Big tablespace in 11g.

Why Use Oracle Bigfile Tablespaces?

Oracle 10g (and continuing into 11g) introduced a new locally managed tablespace type for extreme-size databases: Bigfile tablespaces allow for the creation of tablespaces with one file where the size of that datafile fully incorporates the power of 64-bit systems. When implemented with Oracle Managed Files or Automatic Storage Management (ASM), bigfile tablespaces can greatly simplify the management of your storage system. Additionally, because you should have fewer datafiles, performance of database management operations such as checkpoints should improve, but be aware that recovery operation times are likely to increase in the event of datafile corruption.

Now you be may asking, “Then what is the benefit of bigfile tablespaces?” A bigfile tablespace with a typical 8K block can contain a single 32-terabyte datafile. If you’re using a 32K block, it can contain a 128-terabyte datafile. This is achieved by changing the way ROWIDs are managed within the tablespace. In a traditional tablespace, three positions in the ROWID are used to identify the relative file number of the row. Because you only have one datafile in bigfile tablespaces, these three positions are instead used to lengthen the data block number for the row, thereby allowing for a much larger number of ROWIDs from traditional smallfile tablespaces.


Introduction:

Oracle 10g feature that is interesting is a new type of tablespace called a Bigfile Tablespace. When you read the 10g New Features, you will find out that a DBA can create a terabyte-sized datafile using the Bigfile option. Oracle uses the term "Smallfile" to designate the tablespaces traditionally used for long time. A Bigfile Tablespace is a tablespace containing a single very large data file. A single Bigfile tablespace file, either data or temp file, can be up to 128 terabytes for a 32K block tablespace and 32 terabytes for an 8K block tablespace. Bigfile tablespace contains only one file, whereas a traditional tablespace (smallfile type) can contain up to 1,022 files. Let's take a look at the Bigfile tablespaces feature in this article and learn about some of the benefits that they offer versus smallfile type tablespaces.

Bigfile tablespace Benefits:

  • A DBA can use bigfile tablespaces to create extremely large databases and minimize the number of datafiles a database must manage, which has the advantage of reducing your system global area (SGA) memory requirements with a lower value of DB_Files Init. Parameter and also lower Controlfile space requirements or size.
  • Bigfile tablespace simplifies database management with the ALTER TABLESPACE command to allow the operations at TABLESPACE level, which will help to modify the size and auto extend functionality for all of the datafiles in one shot, rather than doing at datafile level for each file.For example:
    SQL > ALTER TABLESPACE BIGTBS RESIZE 100G;
SQL> ALTER TABLESPACE BIGTBS s AUTOEXTEND ON NEXT 10G;
  • Bigfile Tablespace can be used with:
    • ASM (Automatic Storage Management)
    • a logical volume manager supporting striping/RAID
    • Dynamically extensible logical volumes
    • Oracle Managed Files (OMF)

Bigfile tablespace Limitations:

  • Bigfile data tablespaces must be created as locally managed, with automatic segment space management. These are the default specifications. Oracle will return an error if either EXTENT MANAGEMENT DICTIONARY or SEGMENT SPACE MANAGEMENT MANUAL is specified. But there are two exceptions when bigfile tablespace segments are manually managed:
    • Locally managed undo tablespace
    • Temporary tablespace
  • Bigfile tablespaces should be striped so that parallel operations are not adversely affected. Oracle expects bigfile tablespace to be used with Automatic Storage Management (ASM) or other logical volume managers that support striping or RAID.
  • Bigfile tablespaces should not be used on platforms with filesize restrictions, which would limit tablespace capacity.
  • Avoid using bigfile tablespaces if there could possibly be no free space available on a disk group, and the only way to extend a tablespace is to add a new datafile on a different disk group.

Creating a Bigfile Tablespace...versus smallfile...

To create a bigfile tablespace, you have to specify the BIGFILE keyword of the CREATE TABLESPACE statement. Oracle creates a locally managed tablespace with automatic segment-spec management. You need not specify EXTENTMANAGEMENT LOCAL and SEGMENT SPACE MANAGEMENT AUTO in the statement. If you specify EXTENT MANAGEMENTLOCAL and SEGMENT SPACE MANAGEMENT AUTO in this statement the database returns an error. The remaining syntax of the statement is the same as for the CREATE TABLESPACE statement.
For example:
SQL > CREATE BIGFILE TABLESPACE bigtbs
    DATAFILE '/u01/oradata/bigtbs_data_01.dbf' SIZE 50G
Note that the above size attribute is larger than the traditional ordinary smallfile tablespace size. This is due to a new addressing scheme Oracle uses internally. Oracle ROWID, addressing a database object stored in a traditional SMALLFILE tablespace, divides the 12 bytes thusly: 3 bytes for the Relative File#, 6 bytes for the Block# and 3 bytes for the object. The same rowid addressing an object stored in a new BIGFILE tablespace uses the 9 bytes to store the Block# within the unique file, as there is no reason to use the 3 bytes for the Relative File# since there is only one file in that tablespace. Thus the new addressing scheme permits up to 4Gblocks in a single data file and the maximum file size can reach 8 TB for a blocksize of 2K and 128 TB for a blocksize of 32K
If the default tablespace type is set to BIGFILE at database creation, but you want to create a traditional (smallfile) tablespace, then specify a CREATE SMALLFILE TABLESPACE statement to override the default tablespace type for the tablespace that you are creating.

Adding a file to Bigfile tablespace...

 SQL> ALTER TABLESPACE bigtbs
           ADD DATAFILE '/u01/oradata/bigtbs_data_02.dbf' size 50G;
      ALTER TABLESPACE bigtbs
      *
      ERROR at line 1:
      ORA-32771: cannot add file to bigfile tablespace

Creating a Bigfile Temporary Tablespace

Just as for regular tablespaces, you can create single-file (bigfile) temporary tablespaces. Use the CREATE BIGFILE TEMPORARY TABLESPACE statement to create a single-tempfile tablespace.

Finding out a Bigfile Tablespace

From the following views, you can identify if the database has any bigfile tablespaces. The following views contain aBIGFILE column that identifies a tablespace as a bigfile tablespace:
  • DBA_TABLESPACES
  • USER_TABLESPACES
  • V$TABLESPACE
You can also identify a bigfile tablespace by the relative file number of its single datafile.

Specifying the Default Tablespace Type

The SET DEFAULT ... TABLESPACE clause of the CREATE DATABASE statement determines the default type of tablespace for this database in subsequent CREATE TABLESPACE statements. Specify either SET DEFAULT BIGFILE TABLESPACE or SET DEFAULT SMALLFILE TABLESPACE. If you omit this clause, the default is a smallfile tablespace, which is the traditional type of Oracle Database tablespace. A smallfile tablespace can contain up to 1022 files with up to 4M blocks each.
The use of bigfile tablespaces further enhances the Oracle-managed files feature, because bigfile tablespaces make datafiles completely transparent for users. SQL syntax for the ALTER TABLESPACE statement has been extended to allow you to perform operations on tablespaces, rather than the underlying datafiles.
The CREATE DATABASE statement shown can be modified as follows to specify that the default type of tablespace is a bigfile tablespace:
SQL> CREATE DATABASE mydb
      USER SYS IDENTIFIED BY password
      USER SYSTEM IDENTIFIED BY password
      SET DEFAULT BIGFILE TABLESPACE
      UNDO TABLESPACE undotbs
      DEFAULT TEMPORARY TABLESPACE temp;
To dynamically change the default tablespace type after database creation, use the SET DEFAULT TABLESPACE clause of the ALTER DATABASE statement:
ALTER DATABASE SET DEFAULT BIGFILE TABLESPACE;
You can change the TYPE of tablespace used when creating new tablespaces:
 SQL> select * from database_properties 
          Where property_name='DEFAULT_TBS_TYPE';
     PROPERTY_NAME        PROPERTY_VALUE  DESCRIPTION
     -------------------- --------------- ------------------------
     DEFAULT_TBS_TYPE     SMALLFILE       Default tablespace type
   
     SQL> ALTER DATABASE SET DEFAULT bigfile TABLESPACE;
     Database altered.
     SQL> CREATE TABLESPACE bigtbs 
          DATAFILE '/us01/oradata/bigtbs_02.dbf' size 1M;
     Tablespace created.
     SQL> select TABLESPACE_NAME, BIGFILE from DBA_TABLESPACES;
     TABLESPACE_NAME                BIGFILE
     ------------------------------ ---------
     ...
     USERS                          SMALLFILE
     BIGTBS                        BIGFILE
Even the default tablespace for the database is either Bigfile or smallfile tablespace; both Bigfile tablespace and smallfile tables can co-exist in the database.
SQL> select TABLESPACE_NAME, BIGFILE from DBA_TABLESPACES;
      TABLESPACE_NAME                BIGFILE
      ------------------------------ ---------
      SYSTEM                         SMALLFILE
      UNDOTBS1                       SMALLFILE
      SYSAUX                         SMALLFILE
      TEMP                           SMALLFILE
      USERS                          SMALLFILE
      BIGTBS                         BIGFILE

Working with Bigfile Tablespace:

To illustrate this, first let us create a bigfile tablespace called bigtbs.
CREATE BIGFILE TABLESPACE bigtbs
DATAFILE ‘/u01/oradata/big_tbs_data_01.dbf’ SIZE 1024 M;
  • If the accounts table is in traditional smallfile tablespace,.it can be moved to bigfile tablespace.
    SQL > ALTER TABLE accounts MOVE TABLESPACE bigtbs;
  • Bigfile tablespace can be resized by issuing alter tablespace.
    SQL> ALTER TABLESPACE bigtbs RESIZE 20G;
  • With a bigfile tablespace, you can use the AUTOEXTEND clause outside of the ADD DATAFILE clause. For example:
    SQL> ALTER TABLESPACE bigtbs AUTOEXTEND ON NEXT 20G;
In the earlier releases of Oracle, K and M were used to specify storage size. Please note in the above statement, you can specify size in gigabytes and terabytes using G and T respectively.
  • Using the DBVERIFY utility: With smallfile tablespace, you can run multiple instances of DBVERIFY, in parallel on multiple datafiles, to speed up integrity checking for a tablespace. You can achieve integrity checking parallelism with Bigfile tablespaces by starting multiple instances of DBVERIFY on parts of the single big file by specifying the start block and end block.
                            $dbv FILE=bigfile01.dbf START=1 END=10000
                        $dbv FILE=bigfile01.dbf START=10001
****************************************************************************

Bigfile Tablespace

A bigfile tablespace (BFT) is a tablespace containing a single file that can have a very large size.

Bigfile Tablespace Overview

The traditional tablespace is referred to as a smallfile tablespace (SFT). A smallfile tablespace contains multiple, relatively small files. The bigfile tablespace has the following characteristics:
  • An Oracle database can contain both bigfile and smallfile tablespaces. 
  • System default is to create the traditional smallfile tablespace. 
  • The SYSTEM and SYSAUX tablespaces are always created using the system default type.
     
  • Bigfile tablespaces are supported only for locally managed tablespaces with automatic segment-space management.
There are two exceptions when bigfile tablespace segments are manually managed:
  • Locally managed undo tablespace 
  • Temporary tablespace
Bigfile tablespaces are intended to be used with Automated Storage Management (ASM) (see Chapter 1) or other logical volume managers that support RAID.
However, you can also use The bigfile tablespace without ASM.

Bigfile Tablespace Benefits

Bigfile tablespace has the following benefits:
  • The bigfile tablespace simplifies large database tablespace management by reducing the number of datafiles needed.
  • The bigfile tablespace simplifies datafile management with Oracle-managed files and Automated Storage Management (ASM) by eliminating the need for adding new datafiles and dealing with multiple files.
  • The bigfile tablespace allows you to create a bigfile tablespace of up to eight exabytes (eight million terabytes) in size, and significantly increase the storage capacity of an Oracle database.
  • The bigfile tablespace follows the concept that a tablespace and a datafile are logically equivalent.

Maximum Database Size

The bigfile tablespace extended the maximum size of tablespace and database. Let's take a look at the two formulas that calculate the maximum size of data file and database.
The maximum bigfile (data file) size is calculated by:
     Maximum datafile size = db_block_size * maximum number of blocks
The maximum amount of data in an Oracle database is calculated by:
     Maximum database size = maximum datafile size * maximum number of datafile
The maximum number of datafiles in Oracle9i and Oracle 10g Database is 65,536. However, the maximum number of blocks in a data file increase from 4,194,304 (4 million) blocks to 4,294,967,296 (4 billion) blocks.
The maximum amount of data for a 32K block size database is eight petabytes (8,192 Terabytes) in Oracle9i.
BLOCK SIZE MAXIMUM DATA FILE SIZE MAXIMUM DATABASE SIZE
32 K 128 GB 8,388,608 GB
16 K64 GB 4,194,304 GB
 8 K 32 GB 2,097,152 GB
4 K 16 GB1,048,579 GB
2 K 8 GB 524,288 GB

 Table 3.1: Maximum database size in Oracle9i.
The maximum amount of data for a 32K block size database is eight exabytes (8,388,608 Terabytes) in Oracle 10g.
BLOCK SIZE MAXIMUM DATA FILE SIZE MAXIMUM DATABASE SIZE
32 K 131,072 GB 8,589,934,592 GB
16 K 65,536 GB 4,294,967,296 GB
8 K32,768 GB2,147,483,648 GB
4 K16,384 GB1,073,741,824 GB
2 K8,192 GB536,870,912 GB
 
Table 3.2: Maximum database size in Oracle 10g.
As you can see, with the new bigfile tablespace addressing scheme, Oracle 10g can contain astronomical amounts of data within a single database.
With a few exceptions, you need to use an alter database resize datafile command to resize a datafile.
One exception is the 'bigfile tablespace'.
 Oracle will allows you to use the "alter tablespace xxx resize datafile" syntax.
This is because you are not allowed to add a datafile to a bigfile tablespace.  Remember, there is no alter tablespace resize datafile syntax.
Rowids of Rows that belong to BFTs do not contain the relative file number.
  • Smallfile tablespace has a four-pieces format, OOOOOOFFFBBBBBBRRR, in which:
     
  • OOOOOO is the data object number of the segment.
     
  • FFF is the tablespace-relative datafile number of the datafile that contains the row.
     
  • BBBBBB is the data block that contains the row.
     
  • RRR is the slot number identifying the row inside a particular block.
Bigfile Tablespace Rowid Format
In a bigfile tablespace, there is only one file that always has a relative file number of 1024.  A bigfile tablespace has a three-piece format, OOOOOOLLLLLLLLLRRR, in which LLLLLLLLL is used to denote the block number.  The concatenation of FFF and BBBBBB makes the encoded block number.
For BFTs, the only supported way of getting components of extended rowids is to use the DBMS_ROWID package.
Here is an example on how to use the dbms_rowid package to retrieve rowid information:
select dbms_rowid.rowid_relative_fno(rowid, 'BIGFILE') 
       bigfile_rowid,
       dbms_rowid.rowid_relative_fno(rowid, 'SMALLFILE')
       smallfile_rowid,
       first_name, last_name
FROM   hr.employees where  rownum < 3;

Bigfile Tablespace Rowid Format

BIGFILE_ROWID SMALLFILE_ROWID FIRST_NAME           LAST_NAME
------------- --------------- -------------------- ----------
         1024               4 Mike                 Ault
         1024               4 Madhu                Tumma
Data Dictionary Views Enhancement
A new column is added to both dba_tablespaces  and v$tablespace views to indicate whether a particular tablespace is bigfile or smallfile:
SQL> select name, bigfile
     from v$tablespace;
 NAME                           BIGFILE
------------------------------ -------
SYSTEM                         NO
UNDOTBS01                      NO
SYSAUX                         NO
TEMP                           NO
EXAMPLE                        NO
USERS                          NO
BIG_TBS                        YES

SQL> select tablespace_name,bigfile
     from   dba_tablespaces;

TABLESPACE_NAME                BIGFILE
------------------------------ ---------
SYSTEM                         SMALLFILE
UNDOTBS01                      SMALLFILE
SYSAUX                         SMALLFILE
TEMP                           SMALLFILE|
EXAMPLE                        SMALLFILE
USERS                          SMALLFILE
BIG_TBS01                      BIGFILE

Bigfile Tablespace Examples
Example 1: Create a database with default bigfile tablespace.
CREATE DATABASE GRID
SET DEFAULT BIGFILE TABLESPACE
DATAFILE '/u02/oradata/grid/system01.dbf' SIZE 500 M,
SYSAUX DATA FILE '/u02/oradata/grid/sysaux01.dbf' SIZE 500 M
DEFAULT TEMPORARY TABLESPACE tbs01
TEMPFILE '/u02/oradata/grid/temp01.dbf' SIZE 1024 M
UNDO TABLESPACE undo01
DATAFILE '/u02/oradata/grid/undo01.dbf' SIZE 1024 M;
Example 2: Moving data between smallfile and bigfile tablespaces.
ALTER TABLE employee MOVE TABLESPACE bigfile_tbs;
Example 3: Create a bigfile tablespace and change its size.
CREATE BIGFILE TABLESPACE user_tbs
DATAFILE '/u02/oradata/grid/user_tbs01.dbf' SIZE 1024 M;
ALTER TABLESPACE user_tbs RESIZE 10G;
In the previous release of Oracle server, K and M were used to specify storage size. Notice in this DDL statement, a user can specify size in gigabytes and terabytes using G and T respectively.
Example 4: Use DBVERIFY utility with bigfile. With small file tablespace, you can run multiple instances of DBVERIFY in parallel on multiple datafiles to speed up integrity checking for a tablespace.  You can achieve integrity checking parallelism with BFTs by starting multiple instances of DBVERIFY on parts of the single big file.

$dbv FILE=bigfile01.dbf  START=1 END=10000
$dbv FILE=bigfile01.dbf  START=10001

Note: START = Start Block; END = End Block


By Sujeet jha at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

ORA-00059: Maximum Number Of DB_FILES Exceeded in 19C database

When I am adding datafile to my 19C database facing the below error. SQL> alter tablespace DATA  add datafile '/u01/data/data15.dbf...