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.

    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 EXTENT MANAGEMENT LOCAL
and SEGMENT SPACE MANAGEMENT AUTO
in the statement. If you specify EXTENT
MANAGEMENT LOCAL 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 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 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
    

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.

»


See All Articles by Columnist
Sreeram Surapaneni

Sreeram Surapaneni
Sreeram Surapaneni
Sreeram Surapaneni, OCP, has been working as a database administrator for the past 11 years at Research In Motion, Ltd., and resides in Ontario, Canada. He has several years' experience in various flavors of UNIX and Linux and with Oracle.. He has special interest and experience in RAC in the Linux environment with ASM. Prior to joining RIM, he led a small team of developers on Oracle applications. He also has experience in the SAP functional modules as well as BASIS management.

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles