Featured Database Articles

Bigfile Type Tablespaces versus Smallfile Type

Introduction:

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.

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 CREATETABLESPACE statement. Oracle creates a locally managed
tablespace with automatic segment-spec management. You need not specify EXTENTMANAGEMENTLOCAL
and SEGMENTSPACEMANAGEMENTAUTO
in the statement. If you specify EXTENTMANAGEMENTLOCAL and SEGMENTSPACEMANAGEMENTAUTO in this statement the database returns
an error. The remaining syntax of the statement is the same as for the CREATE TABLESPACE statement.

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 CREATESMALLFILETABLESPACE statement to override the
default tablespace type for the tablespace that you are creating.

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 a BIGFILE 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 CREATEDATABASE statement determines the
default type of tablespace for this database in subsequent CREATETABLESPACE 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
ALTERTABLESPACE statement has been extended
to allow you to perform operations on tablespaces, rather than the underlying
datafiles.

The CREATEDATABASE statement shown can be modified as follows to
specify that the default type of tablespace is a bigfile tablespace:

Working with Bigfile Tablespace:

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.

Conclusion:

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.