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         :   Conatins Pre Image data
4. Temporary : Temporary oprations are performed in this if pga is not enough
5. Users         :  Default tablespace for all DB users / Application schemas

Creating tablespace :

Sql> create tablespace USER1 datafile '/u01/home/prod1/user.dbf' size 10m autoextend on maxsize 100m default storage (next 10m);

To check tablespace information

Sql> select tablespace_name from dba_tablespaces;

Adding  a datafile to a tablespace

Sql> alter tablespace USER1 add datafile  ‘/u02/user/oratab/prod/uuser2.dbf’ size 100m autoextend on;

Deleting a datafile from a tablespace

Sql> alter tablespace USER1 drop datafile  '/u01/home/prod1/user.dbf';

Droping a tablespace

Sql> drop tablespace USER1;

// it will drop tablespace logically at database level.

Sql> drop tablespace USER1 including contents and datafiles;

// it will drop tablespace logically(database level) and physically(o/s level)

Reusing orphan datafile

Sql> create tablespace USER1 datafile '/u01/home/prod1/user.dbf' reuse;

Resize a Datafile

     Sql> alter database datafile  '/u01/home/prod1/user.dbf' resize 50m;

Making  a tablespace as read only

Sql> alter tablespace user1 read only;

Sql> select tablespace_name,status from dba_tablespaces;

Sql> alter tablespace user1 read write;

Making a tablespace offline

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

Sql> alter tablespace user1 online;

Renaming of tablespace

Sql> alter tablespace user1 rename to employes1;

Steps for Renaming a datafile in tablespace

Steps:-

1.make tablespace offline

Sql> alter tablespace user1 offline;

2. at os level rename the datafile

$cd /u01/app/oracle/data/

$mv user1.dbf  user00.dbf

3. Update the controlfile for this datafile.

Sql> alter database rename file ‘/u01/app/oracle/data/user1.dbf’ to

‘/u01/app/oracle/data/user00.dbf’;

4. Online the tablespace

Sql> alter tablespace user1 online;

Select tablespace_name, file_name from dba_data_files;

Moving table (‘emp’) from one tablespace to another tablespace (users)

Sql> alter table emp move tablespace users;

Moving index from one tablespace to another tablespace

Sql> alter index emp_indx rebuild tablespace user;

To check database size

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

To check free space in database

            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