Administration
Bigfile tablespaces
User Rating: / 1
- Details
-
Parent Category: Articles
-
Created on Wednesday, 17 March 2010 22:45
-
Last Updated on Monday, 12 March 2012 13:54
-
Published on Wednesday, 17 March 2010 22:45
-
Written by Guy Lambregts
-
Hits: 4904
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
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.