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