Bigfile tablespaces were introduced in 10G, in a nutshell : a bigfile tablespace compared to smallfile tablespaces
1. can address more space ( sounds logic )
2. only contains 1 datafile
3. limits the amount of datafiles for very big databases ( checkpoint performance and controlfile size )
What is for me important is that the rowid format for rows stored in a bigfile tablespace is different compared to the rowid format for rows stored in a smallfile tablespace.
The rowid format of a smallfile tablespace is
OOOOOO | Data Object Number |
FFF | Relative File number |
BBBBBB | Data Block Number |
RRR | Row Number |
The rowid format of a bigfile tablespace is
OOOOOO | Data Object Number |
LLLLLLLLL | Encoded Block Number |
RRR | Row number |
Since a bigfile tablespace contain only 1 datafile the 3 characters used to define the relative file number are not needed anymore to locate the row. As such these 3 characters can be used for others purposes, more precisely they are used for the block number and because we can address 9 block characters we can address more block, more space with bigfile tablespaces.
Obviously we find more information in the Administration manual
A little gym
SQL> alter system set db_create_file_dest='+DG2' scope=memory;
System altered.
SQL> create bigfile tablespace TS_BIGFILE;
Tablespace created.
SQL> create smallfile tablespace TS_SMALLFILE;
Tablespace created.
SQL> create user coconut identified by coconut default tablespace TS_BIGFILE temporary tablespace TEMP_GROUP1;
User created.
SQL> grant create session,create table to coconut;
Grant succeeded.
SQL> alter user coconut quota unlimited on TS_BIGFILE;
User altered.
SQL> alter user coconut quota unlimited on TS_SMALLFILE;
User altered.
SQL> connect coconut/coconut@myhost1:1521/MY_DB
Connected.
SQL> create table T1_BIG ( col1 number,constraint pk_t1_big primary key (col1) )
tablespace TS_BIGFILE;
Table created.
SQL> create table T1_SMALL ( col1 number,constraint pk_t1_small primary key (col
1) ) tablespace TS_SMALLFILE;
Table created.
SQL> insert into T1_BIG values (1);
1 row created.
SQL> insert into T1_SMALL values (1);
1 row created.
SQL> select rowid from T1_BIG;
ROWID
AAAWMfAAAAAAAAYAAA
SQL> select rowid from T1_SMALL;
ROWID
AAAWMhAAyAAAAAQAAA
SQL> commit;
Commit complete.