Another 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.
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:
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
- 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
TABLESPACE statement. Oracle creates a locally managed
tablespace with automatic segment-spec management. You need not specify
in the statement. If you specify
AUTO in this statement the database returns
an error. The remaining syntax of the statement is the same as for the
CREATE TABLESPACE statement.
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
at database creation, but you want to create a traditional (smallfile)
tablespace, then specify a
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
Finding out a Bigfile Tablespace
following views, you can identify if the database has any bigfile tablespaces. The
following views contain a
BIGFILE column that identifies a tablespace as a
You can also identify a bigfile tablespace by the relative file number of its single datafile.
Specifying the Default Tablespace Type
SET DEFAULT ... TABLESPACE clause of
DATABASE statement determines the
default type of tablespace for this database in subsequent
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
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
TABLESPACE statement has been extended
to allow you to perform operations on tablespaces, rather than the underlying
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;
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
When your database and its user community are expanding faster than the capabilities of the servers that host them, don't despair. Instead, borrow some strategies like Bigfile type tablespace. The performance of database opens, checkpoints, and DBWR processes should improve if data is stored in bigfile tablespaces instead of traditional tablespaces. However, increasing the datafile size might increase the time to restore a corrupted file or create a new datafile.