Oracle Tablespaces

About

Tablespace is created in oracle for allocating space for holding database objects.

There are different types of tablespaces:

•  PERMANENT TABLESPACE: This tablespace contains persistent schema objects.Objects in permanent tablespaces are stored in datafiles. 
• An UNDO tablespace is a type of permanent tablespace used by Oracle Database to manage undo data if you are running your database in automatic undo management mode. Oracle strongly recommends that you use automatic undo management mode rather than using rollback segments for undo.
• A TEMPORARY TABLESPACE contains schema objects only for the duration of a session. Objects in temporary tablespaces are stored in tempfiles.

  

CREATE TABLESPACE

Tablespaces are created with the syntax 'CREATE TABLESPACE'.

Project Tablespace:

In any oracle related project, tablespaces can be created as below:

• <PROJECT>_BIG_IDX:

All the small Indexes, Index Partitions of size greater than 4 MB are grouped here. INDEXPARTITITON comes under this.

• <PROJECT>_SMALL_IDX:

All the small Indexes, Index Partitions of size lesser than 5 MB are grouped here. INDEXPARTITITON comes under this.

• <PROJECT>_BIG_TAB:

All the database objects like LOBINDEX, LOBSEGMENT, TABLE, TABLEPARTITON etc. of size greater than 5 MB are grouped here.

• <PROJECT>_SMALL_TAB:

All the database objects like LOBINDEX, LOBSEGMENT, TABLE, TABLEPARTITON etc. of size lesser than 5 MB are grouped here.

Re-allocating the database objects in proper tablespaces:

There are cases where we need to reallocate database objects like Tables, Index in the proper Tablespaces on weekly or on monthly basis.

These reallocation will be done based on num_rows, avg_row_len and initial_extent of the Table objects. 

Manual Tablespace reallocation scripts:

Table:

ALTER TABLE <TABLE NAME to be moved> MOVE TABLESPACE <destination TABLESPACE NAME>;

Index:

ALTER INDEX <INDEX_NAME> REBUILD TABLESPACE <TABLESPACE_NAME>;

LOBSEGMENT:

In Order to move CLOB column to different tablespace, use the below command. This will move LOGSEGMENT and LOBINDEX to the new tablespace.

ALTER TABLE <Table_Name> MOVE LOB(<CLOB_Column>) STORE AS (TABLESPACE <Tablespace_name>);

LOBINDEX:

You cannot specify a tablespace for the LOBINDEX -- it is automatically created and moved with the LOBSEGMENT.

TABLE PARTITION:

alter table <table_name> move partition <Table_partition_name> tablespace <tablespace_name>;

INDEX PARTITION:

ALTER INDEX <Index_name> REBUILD PARITION <Partition_Name> TABLESPACE  <Tablespace_name>;

Reference: http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_7003.htm and my own project experience.