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
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
ALTER TABLESPACE TB_BIG
ERROR at line 1:
ORA-32771: cannot add file to bigfile tablespace
- 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.