Bigfile Tablespace in 10g

Database tables nowdays holds alot of data. This is of because data is important for companies to make decisions.As you are aware the concept of BI (Business Intelligence), it is becoming famous nowdays. BI needs alot of data to make better decisions.

To address this craving,Oracle has come up with th Bigfile Tablespace concept.

Bigfile tablespaces(BFT) are tablespaces with a single large datafile(this means you can only have 1 datafile for bigfile tablespace). In contrast to normal (smallfile) tablespaces can have several datafiles(max 1022 files), but each is limited in size (the size of the disk partiton).

This concept is introduced in 10g.

Bigfile tablespaces(BFT) must be locally managed with automatic segment-space management.

Temporary tablespaces, the SYSTEM tablespace and locally managed undo tablespaces are exceptions to this rule.

e.g. of creating a bigfile tablespace

CREATE BIGFILE TABLESPACE innotiivebig DATAFILE '/a1/oracle/innotiivebig01.dbf' SIZE 200G;

OR

CREATE BIGFILE TABLESPACE innotiivebig DATAFILE '/a1/oracle/innotiivebig01.dbf' SIZE 200G EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;

The size of the file can be specified in kilobytes (K), megabytes (M), gigabytes (G), or terabytes (T).

DBA_TABLESPACES AND V$TABLESPACE contain a new column called BIGFILE that allows administrators to determine if the tablespace is a Smallfile or Bigfile tablespace.

Before you even consider creating one of these things, make sure that your environment is able to support its expansion

For using BFT,the underlying operating system should support Large File.In other words the file system should have Large File Support(LFS).

Bigfile tablespaces will have a future in many Oracle environments but they must only be implemented with much forethought.