Tablespace Management

By default each database will have five tablespaces in oracle 10g.

1. System : Tablespace-contains data dictionary of database
2. Sysaux : Contains database statistics
3. Undo : Contains Pre Image data
4. Temporary : Temporary operations are performed in Temporary Tablespace if PGA is not enough
5. Users : Default tablespace for all DB users / Application schemas

Creating Tablespace:-

Syntax:
SQL> create tablespace tablespace_name
datafile 'location of datafile/datafile_name.dbf' size 50m;

Example:
SQL> create tablespace test01
datafile '/oradata/oracle/db/test01.dbf' size 50M;

To check Tablespace information:

SQL> select tablespace_name from dba_tablespaces;

Adding the Space to Tablespace (Adding Datafile) :-

Syntax:
SQL> alter tablespace tablespace_name add datafile
'datafile location/datafile_name.dbf ' size 30m;
Example:
SQL> alter tablespace test add datafile
'/oradata/oracle/db/test/test02.dbf ' size 30M;

Deleting a datafile from Tablespace:

Syntax:
SQL> alter tablespace tablespace_name drop datafile
'datafile location/datafile_name.dbf'

Example:
SQL> alter tablespace test drop datafile
‘/oradata/oracle/db/test/test02.dbf’;

Droping a Tablespace:

Sql> drop tablespace test;       (It will drop tablespace and datafile at database level)

Sql> drop tablespace test including contents and datafiles;
(It will drop tablespace logically(database level) and physically(o/s level))

Reusing Orphan datafile:

By using "drop tablespace tablespace_name" command we can drop tablespace at
logical level but physically those datafiles are exist and we can reuse those datafile (Orphan) by
using following command.

Syntax:
SQL> create tablespace tablespace_name datafile
'datafile location/datafile_name.dbf' reuse;

Example:
SQL> create tablespace test datafile
'/oradata/oracle/db/test/test02.dbf' reuse;

Making a Tablespace as read only:

SQL> alter tablespace test read only;

SQL> select tablespace_name,status from dba_tablespaces;   To view the status of tablespace

SQL> alter tablespace test read write;

Making a Tablespace offline:

Sql> alter tablespace test offline;  Users can not access this tablespace in this state.

Sql> alter tablespace test online;

Renaming of Tablespace:

Syntax:
SQL> alter tablespace old_tablespace TO new_tablespace;

Example:
SQL> alter tablespace test rename to test2;

Renaming a Datafile in Tablespace:

Steps:-
1.make Tablespace offline
SQL> alter tablespace test offline;

2. at os level rename the datafile
$cd   /oradata/oracle/db/test/
$mv   test01.dbf    test005.dbf
3. Update the Controlfile for this Datafile.
SQL> alter database rename file ‘/oradata/oracle/db/test/test01.dbf’ to
‘/oradata/oracle/db/test/test005.dbf;

4. Online the Tablespace
Sql> alter tablespace test online;

Select tablespace_name, file_name from dba_data_files;

Relocating a Datafile in Tablespace:

Steps:-
1.make Tablespace offline
SQL> alter tablespace test offline;

2. at os level rename the datafile
$cd    /oradata/oracle/db/test/
$mv   test01.dbf    /u01/test/test01.dbf
3. update the Controlfile for this datafile.
SQL> alter database rename file ‘/oradata/oracle/db/test/test01.dbf’ to
‘/u01/test/test01.dbf’;

4. online the Tablespace
SQL> alter tablespace test online;

SQL>Select tablespace_name,file_name from dba_data_files;

To check Database size:

SQL> Select sum(bytes)/1024/1024 “size in MB” from dba_data_files;

To check free space in Database:

SQL> Select sum(bytes)/1024/1024 from dba_free_space;

Views:-
V$tablespace V$datafile
Dba_tablespaces
User_tablespaces
Dba_data_files Dba_segments            
Dba_extents sm$ts_free
Sm$ts_used sm$ts_avail

Resize Datafiles:-

Syntax:
SQL> alter database datafile 'Location of Datafile/datafile_name.dbf' 

           resize 20M;

Example:
SQL> alter database test02
datafile '/oradata/oracle/db/test/test02.dbf'  resize 20M;

Create Temporary Tablespace:-

Syntax:
SQL> create temporary tablespace tempTB_name
tempfile 'temp file location.tempfile_name.dbf'  size 20M;
Example:
SQL> create temporary tablespace temp_test
tempfile '/oradata/oracle/db/test/temp_test.dbf' size 20M;

(v$sort_segment, v$tempseg_usage for the space info of temp t.s)

Create undo Tablespace:-

Syntax:
SQL> create undo tablespace undoTB_name
datafile 'location of datafile/datafile_name.dbf' size 50m;
Example:
SQL> create undo tablespace undotbs02
datafile '/oradata/oracle/db/test/undotbs02.dbf'  size 50M;

Adding Datafile to Undo Tablespace:

SQL> alter tablespace undotbs02
add datafile '/oradata/oracle/db/test/undotbs03.dbf'  size 50m;

Switching Undo Tablespaces:

SQL> ALTER SYSTEM SET UNDO_TABLESPACE = undotbs02 scope=both;

Views for undo Tablespace:

V$undostat
V$rollstat
V$transaction
dba_undo_extents

View for Sysaux tablespace:
V$sysaux_occupants