04 | 03 | 2021
Latest Articles
Popular Articles


Bigfile tablespaces

User Rating:  / 0

Bigfile Tablespaces


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

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;



SQL> select rowid from T1_SMALL;



SQL> commit;

Commit complete.