Table Space Management

Oracle Database stores data logically in tablespaces and physically in datafiles associated with the corresponding tablespace.

Tablespace:

An oracle tablespace is a logical structure which stores the physical files.

We can't create a tablespace without datafile into it.

Types of Tablespaces: 

1) System Tablespaces

2) Sysaux Tablespaces

3) Undo Tablespaces

4) Temp tablespaces etc.

1) System Tablespace: 

     Every Oracle database contains a tablespace named SYSTEM, which Oracle Database creates automatically when the database is created. The SYSTEM tablespace is always online when the database is open.

      

     The SYSTEM tablespace always contains the data dictionary tables for the entire database.

     All data stored on behalf of stored PL/SQL program units (that is, procedures, functions, packages, and triggers) resides in the SYSTEM tablespace

2) Sysaux Tablespace:

     The SYSAUX tablespace is an auxiliary tablespace to the SYSTEM tablespace. Many database components use the SYSAUX tablespace as their default location to store data. Therefore, the SYSAUX tablespace is always created during database creation or database upgrade.

     The SYSAUX tablespace provides a centralized location for database metadata that does not reside in the SYSTEM tablespace.

     During normal database operation, Oracle Database does not allow the SYSAUX tablespace to be dropped or renamed.

     Transportable tablespaces for SYSAUX is not supported.

3) Undo tablespace:

           Undo tablespaces are special tablespaces used solely for storing undo information.

      

           Undo tablespaces are used only when the database is in automatic undo management mode (the default). A database can contain more than one undo tablespace, but only one can be in use at any time. Undo data is managed within an undo tablespace using undo segments that are automatically created and maintained by the database.

           When the first DML operation is run within a transaction, the transaction is bound (assigned) to an undo segment (and therefore to a transaction table) in the current undo tablespace.

4) Temp tablespace:

          Manage space for sort operations more efficiently by designating one or more temporary tablespaces exclusively for sorts.

         All operations that use sorts, including joins, index builds, ordering, computing aggregates (GROUP BY), and collecting optimizer statistics, benefit from temporary tablespaces.

             

Actions:

1) Creating the tablespace

2) Deleting the tablespace (or) drop the tablespace;

3) Altering the tablespace

4) Renaming of the tablespace:

Using the RENAME TO clause of the ALTER TABLESPACE, you can rename a permanent or temporary tablespace. For example, the following statement renames the users tablespace:

Eg:

ALTER TABLESPACE users RENAME TO usersts;

When you rename a tablespace the database updates all references to the tablespace name in the data dictionary, control file, and (online) datafile headers. The database does not change the tablespace ID so if this tablespace were, for example, the default tablespace for a user, then the renamed tablespace would show as the default tablespace for the user in the DBA_USERS view.

The following affect the operation of this statement:

1)  The COMPATIBLE parameter must be set to 10.0.0 or higher.

2)  If the tablespace being renamed is the SYSTEM tablespace or the SYSAUX tablespace, then it will not be renamed and an error is raised.

3)  If any datafile in the tablespace is offline, or if the tablespace is offline, then the tablespace is not renamed and an error is raised.

4)  If the tablespace is read only, then datafile headers are not updated. This should not be regarded as corruption; instead, it causes a message to be written to the alert log indicating that datafile headers have not been renamed. The data dictionary and control file are updated.

5)  If the tablespace is the default temporary tablespace, then the corresponding entry in the database properties table is updated and the DATABASE_PROPERTIES view shows the new name.

6)  If the tablespace is an undo tablespace and if the following conditions are met, then the tablespace name is changed to the new tablespace name in the server parameter file (SPFILE).

The server parameter file was used to start up the database.

The tablespace name is specified as the UNDO_TABLESPACE for any instance.

If a traditional initialization parameter file (PFILE) is being used then a message is written to the alert log stating that the initialization parameter file must be manually changed.

      

1) creating the tablespaces:

create tablespace <tablespacename> datafile <datafilename> size <size> ;

Here value '<>' indicates you required name;

Eg: I am creating chandra tablespace;

Create tablespace chandra datafile '/oracle/orasoft/oradata/chandra.dbf ' size 100m;

Creating the tablespace with required size of datafile block sizes:

1) First you have to mention the static parameter db_nk_block_size=nk in spfile

Where n is (2,4,8,16,....)

Then

Create tablespace <tablespacename> datafile <datafilename> size <size>

Blocksize <size>;

Eg:

Create tablespace chandra1 datafile '/oracle/orasoft/oradata/chandra1.dbf' size 100m

Blocksize 4k

For creation of tablespaces undo,temporary tablespaces we may mention the key words undo and 

2) Altering the tablespaces:

Adding Datafile in a Tablespace

Use ALTER TABLESPACE to add datafile in tablespace like

ALTER TABLESPACE <tablespace_name> ADD DATAFILE <location_of_datafile>;

Eg :

ALTER TABLESPACE my_tablespace ADD DATAFILE  ’/u01/oracle/oradata/data02.dbf’;

To add temporary file in TEMP table space

ALTER TABLESPACE <tablespace_name> ADD TEMPFILE ‘<location_of_tempfile>’ SIZE <size>;

You can modify datafile using ALTER DATABASE command like

ALTER DATABASE DATAFILE <location_of_datafile> AUTOEXTEND ON|OFF NEXT <size> MAXSIZE <size>;

Eg:

ALTER DATABASE DATAFILE ‘/u01/oracle/oradata/data02.dbf’ AUTOEXTEND ON NEXT 30M MAXSIZE 1200M;

3) Rename Tablespace:

ALTER TABLESPACE users RENAME TO usersts;

4) Droping the tablespace:

If the tablespace didn't have any contents then (i.e empty file)

Drop tablespace <tablespace name>

If you drop the tablespace only like above syntax you may have the datafile in os level so we can reuse that.

By using reuse keyword.

If the tablespace have any contents then (i.e, non empty datafiles)

Drop tablespace <tablespace name> including contents;

                    (or)

Drop tablespace <tablespace name> including contents

And datafiles;