Managing Tablespaces and Datafiles

Tablespaces & Datafiles: Overview

Data for Oracle tables, indexes, etc is stored in data files, but never when an object is defined, the object is associated with a file directly. All the time the Oracle objects are "located" in the tablespaces. The "tablespaces" are logical concepts and each tablespace is in relation with one or more physical file. So, when an object is created in a tablespace, the data will be stored automatically in the file(s) associated with this tablespace. 

Tablespace creation

=> Data tablespace (created for data objects like tables, materialized view, indexes)

 

          CREATE TABLESPACE DATA_1_TBS

          DATAFILE 'C:\oradata\data_1.dbf'

          SIZE 20M AUTOEXTEND ON;

 

       This tablespace named DATA_1_TBS has allocated 20M space on the file data_1.dbf and the size of the file will increase if the tablespace will need more space on the disk.

 

   => Temporary tablespace (keep temporary data for sort, join operations)

       

          CREATE TEMPORARY TABLESPACE temp_1

          TEMPFILE 'c:\temp01.dbf' SIZE 5M AUTOEXTEND ON;

 

       This tablespace named "temp_1" has allocated 5M space on the file temp01.dbf and the size of the file will increase if the tablespace will need more space on the disk. However, in general, the temporary tablespaces are not set to be "autoextend off", but have enough room for the database needs. 

 

  => UNDO tablespace (keep the old values for the transactions which are not commited)

    

         CREATE UNDO TABLESPACE undo1

       DATAFILE 'c:\oradata\undo1.dbf' SIZE 10M AUTOEXTEND ON

       RETENTION GUARANTEE;

 

      If you use the "RETENTION GUARANTEE" clause Oracle guarantees that whatever retention period you have set will be honored.

 

    NOTES:

• EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K;  --> creates a locally managed tablespace in which every extent is 128K

• EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO; --> creates a tablespace with automatic segment-space management (ASSM). 

Add a file to the tablespace

                 ALTER TABLESPACE DATA_1_TBS

             ADD DATAFILE 'c:\oradata\data_file2.dbf' SIZE 30M AUTOEXTEND OFF;

To get more information on the files which are associated with a tablespace the following query could be  used: 

                SELECT TABLESPACE_NAME, FILE_NAME, FILE_ID, AUTOEXTENSIBLE, ONLINE_STATUS

            FROM DBA_DATA_FILES ORDER BY 1;

Remove a file from a tablespace (Resizing a tablespace)

Removing a file from a tablespace cannot be done directly. First, the objects must be moved in another tablespace, the initial tablespace will be dropped and recreated. After that the objects could be moved again in the tablespace which was resized. If the reason you wanted to drop the file is because you mistakenly created the file of the wrong size, then consider using the RESIZE command.

More information about  this subject ( Managing Tablespaces and Datafiles ) you can get from  www.in-oracle.com