BIG FILE Creation

BIG FILE Creation :

A bigfile tablespace (BFT) is a tablespace containing a single file that can have a very large size.

Bigfile Tablespace Overview:

The traditional tablespace is referred to as a smallfile tablespace (SFT). A smallfile tablespace contains multiple, relatively small files. The bigfile tablespace has the following characteristics:

• An Oracle database can contain both bigfile and smallfile tablespaces.
  
• System default is to create the traditional smallfile tablespace.
  
• The SYSTEM and SYSAUX tablespaces are always created using the system default type.
   
• Bigfile tablespaces are supported only for locally managed tablespaces with automatic segment-space management.

There are two exceptions when bigfile tablespace segments are manually managed:

• Locally managed undo tablespace
  
• Temporary tablespace

Bigfile tablespaces are intended to be used with Automated Storage Management (ASM) (see Chapter 1) or other logical volume managers that support RAID.
However, you can also use The bigfile tablespace without ASM.

Bigfile Tablespace Benefits:

Bigfile tablespace has the following benefits:

• The bigfile tablespace simplifies large database tablespace management by reducing the number of datafiles needed.
  
  
• The bigfile tablespace simplifies datafile management with Oracle-managed files and Automated Storage Management (ASM) by eliminating the need for adding new datafiles and dealing with multiple files.
  
  
• The bigfile tablespace allows you to create a bigfile tablespace of up to eight exabytes (eight million terabytes) in size, and significantly increase the storage capacity of an Oracle database.
  
  
• The bigfile tablespace follows the concept that a tablespace and a datafile are logically equivalent.

The maximum amount of data for a 32K block size database is eight exabytes (8,388,608 Terabytes) in Oracle 10g.


The maximum amount of data for a 32K block size database is eight exabytes (8,388,608 Terabytes) in Oracle 10g.

BLOCK SIZE	MAXIMUM DATA FILE SIZE	MAXIMUM DATABASE SIZE
32 K	131,072 GB	8,589,934,592 GB
16 K	65,536 GB	4,294,967,296 GB
8 K	32,768 GB	2,147,483,648 GB
4 K	16,384 GB	1,073,741,824 GB
2 K	8,192 GB	536,870,912 GB

 

Creating Big file tablespace :

sql>>CREATE BIGFILE TABLESPACE user_tbs
     DATAFILE '/disk1/oradata/ORCL/user_tbs01.dbf' SIZE 1024m;

 Resizing the datafile :

sql>>ALTER TABLESPACE user_tbs DATAFILE '/disk1/oradata/ORCL/user_tbs01.dbf' RESIZE  10G;

 Data Dictionary Views Enhancement

->A new column is added to both dba_tablespaces  and v$tablespace views
to indicate whether a particular tablespace is bigfile or smallfile

SQL> select name, bigfile  from v$tablespace;

SQL> select tablespace_name,bigfile from   dba_tablespaces;

Here is an example on how to use the dbms_rowid package to retrieve rowid information:

SYS>>select dbms_rowid.rowid_relative_fno(rowid, 'BIGFILE') 
       bigfile_rowid,
       dbms_rowid.rowid_relative_fno(rowid, 'SMALLFILE')
       smallfile_rowid,
       first_name, last_name
       FROM   hr.employees where  rownum < 3;

Bigfile Tablespace Rowid Format:

BIGFILE_ROWID SMALLFILE_ROWID FIRST_NAME           LAST_NAME
------------- --------------- -------------------- ----------
         1024               4 Mike                 Ault
         1024               4 Madhu              Tumma

Data Dictionary Views Enhancement

A new column is added to both dba_tablespaces  and v$tablespace views to indicate whether a particular tablespace is bigfile or smallfile:

SQL> select name, bigfile from v$tablespace;

 NAME                           BIGFILE
------------------------------ -------
SYSTEM                         NO
UNDOTBS01                NO
SYSAUX                          NO
TEMP                             NO
EXAMPLE                     NO
USERS                            NO
BIG_TBS                        YES

SQL> select tablespace_name,bigfile from   dba_tablespaces;

TABLESPACE_NAME                BIGFILE
------------------------------ ---------
SYSTEM                                   SMALLFILE
UNDOTBS01                          SMALLFILE
SYSAUX                                   SMALLFILE
TEMP                                      SMALLFILE|
EXAMPLE                               SMALLFILE
USERS                                      SMALLFILE
BIG_TBS01                              BIGFILE

Example 1: Create a database with default bigfile tablespace.

CREATE DATABASE GRID
SET DEFAULT BIGFILE TABLESPACE
DATAFILE ?/u02/oradata/grid/system01.dbf? SIZE 500 M,
SYSAUX DATA FILE ?/u02/oradata/grid/sysaux01.dbf? SIZE 500 M
DEFAULT TEMPORARY TABLESPACE tbs01
TEMPFILE ?/u02/oradata/grid/temp01.dbf? SIZE 1024 M
UNDO TABLESPACE undo01
DATAFILE ?/u02/oradata/grid/undo01.dbf? SIZE 1024 M;

Example 2: Moving data between smallfile and bigfile tablespaces.

ALTER TABLE employee MOVE TABLESPACE bigfile_tbs;

Example 3: Create a bigfile tablespace and change its size. 

CREATE BIGFILE TABLESPACE user_tbs
DATAFILE ?/u02/oradata/grid/user_tbs01.dbf? SIZE 1024 M;
ALTER TABLESPACE user_tbs RESIZE 10G;

In the previous release of Oracle server, K and M were used to specify storage size. Notice in this DDL statement, a user can specify size in gigabytes and terabytes using G and T respectively.

Example 4: Use DBVERIFY utility with bigfile. With small file 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 BFTs by starting multiple instances of DBVERIFY on parts of the single big file.

$dbv FILE=bigfile01.dbf  START=1 END=10000
$dbv FILE=bigfile01.dbf  START=10001

Note: START = Start Block; END = End Block