Systems Engineering and RDBMS

BIGFILE tablespace

Posted by decipherinfosys on November 1, 2007

In 10g release, Oracle came up with new type of tablespace called the bigfile tablespace. So far we were dealing with our traditional tablespaces right from the birth of the Oracle database. With the introduction of bigfile tablespace, traditional tablespaces are considered as smallfile tablespaces. While a regular or small tablespace can contain up to 1022 data files with up to 4MB blocks each, bigfile tablespace can contain only one data file which can be in size of the terabytes and it can contain blocks up to 4GB. Depending upon the block size, bigfile tablespace can contain data file of 128 terabytes.

Database can contain combination of bigfile and smallfile tablespaces. Bigfile tablespace can be created only for locally managed tablespaces with automatic segment space management. To create the bigfile tablespace, one has to specify BIGFILE clause with CREATE TABLESPACE command. Alternatively, bigfile tablespace can be created during creation of database it self. Let us create one. In our example, we are creating 1G file just to demonstrate the case. Size can be defined in terabyte also.

SQL> CREATE BIGFILE TABLESPACE TB_BIG
2 DATAFILE ‘C:\oracle\product\10.2.0\oradata\orcl\TBBIG.DBF’ SIZE 1G
3 /

Tablespace created.

While creating tablespace, when we define BIGFILE command there is no need to define locally managed or automatic segement space management clause. Oracle automatically, takes these values in consideration. There is also new column BIGFILE in DBA_TABLESPACE, which indicates whether tablespace is a bigfile tablespace or traditional. We can check it by executing following query.

SQL> SELECT TABLESPACE_NAME, EXTENT_MANAGEMENT, SEGMENT_SPACE_MANAGEMENT, BIGFILE
2 FROM DBA_TABLESPACES;

TABLESPACE_NAME                EXTENT_MAN SEGMEN BIG
 ------------------------------ ---------- ------ ---
 SYSTEM                         LOCAL      MANUAL NO
 UNDOTBS1                       LOCAL      MANUAL NO
 SYSAUX                         LOCAL      AUTO   NO
 TEMP                           LOCAL      MANUAL NO
 USERS                          LOCAL      AUTO   NO
 EXAMPLE                        LOCAL      AUTO   NO
 TB_BIG                         LOCAL      AUTO   YES

Bigfile tablespace can be altered to resize or to auto extend the tablespace. Altering bigfile tablespace to add the data file will result in an error.

SQL> ALTER TABLESPACE TB_BIG
2 ADD DATAFILE ‘C:\oracle\product\10.2.0\oradata\orcl\TBBIG_1.DBF’ SIZE 10M
3 /

ALTER TABLESPACE TB_BIG
*
ERROR at line 1:
ORA-32771: cannot add file to bigfile tablespace

Advantages:

  • Biggest advantage to use bigfile tablespace for very large databases is the reduction in number of datafiles. This will ease the data file management. Reduced number of files will also help in reducing control file size and reduce the amount of SGA space by adjusting related initialization parameters.
  • It also simplifies the tablespace management. Alter tablespace allows operation on tablespace rather than the datafile. We can resize the file by altering tablespace without actually giving the file name. We can just give ‘ALTER TABLESPACE TB_BIG RESIZE 2G;’ to resize the underlying data file to 2G.

Even though it has certain advantages while creating bigfile tablespace, we need to keep certain points in mind. As per Oracle documentation, bigfile tablespace should be used with ASM or other logical volume managers which support striping or RAID and extends dynamically. Also make sure that there are no OS level limitations for the file size.

Sorry, the comment form is closed at this time.

 
%d bloggers like this: