Tablespace Management in Oracle

1) Tablespace belongs to the logical database. The tablespace will be identified by a name and is accessible only when the database is up and running.

2) A tablespace is a collection of one or more datafiles.

3) One oracle database may have several tablespaces.

4) The tablespaces will contain the data of the tables , the table data is kept within the datafiles of the tablespace.

5) Whenever we insert data into a table that data is transferred to an underlying tablespace which will inturn write that data to the underlying datafiles that are attached to that tablespace.

6) One datafile can belong only to one tablespace at a time.

7) More than one table can store their data in the same tablespace. One table can exist only in one tablespace at a time unless it is a partitioned table.

8) If the datafiles of a tablespace become full we can add new datafiles to that tablespace at any point in time.one tables data can span across many datafiles of the same tablespace.

9) By default when we create a new oracle database then some default tablespaces will be created. They are
  a) System tablespace
  b) Sysaux tablespace
  c) Undo tablespace
  d) Temporary tablespace
  e) Default permanent tablespace/users tablespace

10) The size of a tablespace is the sum total of the size of all the datafiles under that tablespace.

11) The smallest unit of data storage in an oracle database is a datablock.

12) The oracle datablock is a logical division of a datafile.

13) The size of an oracle datablock will be in multiples of the o/s block size.

14) The oracle datablock size can vary upto 2 to the power 'n' kb where 'n' ranges from 1 to 5.

15) The default size of a datablock in oracle 10g is 8kb and incase of 9i it is 2kb.

16) One datafile will have all blocks of the same size.

17) All datafiles belonging to one tablespace must have data blocks of the same size.

18) One oracle database can have tablespaces of different block sizes.

Tablespaces can be manipulated with the following sql commands,

1. CREATE
2. DROP
3. ALTER

Examples for Create Tablespace:

Permanent tablespace:

SQL> create tablespace sample datafile '/u01/oradata/sample01.dbf' SIZE 100M autoextend on;

Create more than one datafile with a single create tablespace command:

SQL> create tablespace sample datafile '/u01/oradata/sample01.dbf' size 100M autoextend on,
'/u01/oradata/sample02.dbf' SIZE 100M AUTOEXTEND ON,
'/u01/oradata/sample03.dbf' SIZE 100M AUTOEXTEND ON logging extent management local;

Temporary Tablespace:

SQL> Create temporary tablespace temp01 tempfile '/u01/oradata/TEMP01.DBF' size 2048m;

Undo tablespace:

SQL> create undo tablespace UNDOTBS datafile '/u01/oradata/undotbs01.dbf' size 2048M;

Big file tablespace (Introducing in Oracle10g):

SQL> Create bigfile tablespace bigtbs datafile '/u01/oradata/bigtbs01.dbf' SIZE 100G;

Extend Size of a tablespace:

SQL> alter database datafile '/u01/oradata/sample01.dbf' resize 1024M;

Adding datafile:

SQL> alter tablespace add datafile '/u01/oradata/sample02.dbf' size 1024M;

Tablespaces have two status

1. ONLINE (DEFAULT STATUS OF A TABLESPACE)
2. OFFLINE

Examples for making tablespace to offline and online:

SQL> alter tablespace sample offline;
SQL> alter tablespace sample online;

An online tablespace has 2 modes

1. READ WRITE (Default mode of a ts)
2. READ ONLY

Examples for making tablespace to Read Write and Read only mode:

SQL> alter tablespace sample read only;
SQL> alter tablespace sample read write;

Tablespace can have two logging modes

1. LOGGING (Tablespace is logging its transactions to a logfile). (default)
2. NOLOGGING (Tablespace is not logging its transactions to a logfile).

Drop tablespaces:

SQL>drop tablespace sample including contents and datafiles;

Tablespace related data dictionary views:

1. DBA_TABLESPACES
2. DBA_DATA_FILES
3. V$DATAFILES
4. V$TEMPFILE
5. DBA_SEGMENTS
6. DBA_EXTENTS
7. USER_SEGMENTS
8. USER_EXTENTS
9. SM$TS_FREE
10. SM$TS_USED