Bigfile Type Tablespaces versus Smallfile Type

一篇介绍bigfile tablespace和smallfile tablesapce的很好的文章，原文见这里。

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.