Tablespace Management Posted by MOHAMMED ABDUL HAMEED on 6:00 AM

Creating tablespace :

Sql> create tablespace hyd datafile '/u01/home/prod/hyd.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 hyd add datafile  ‘/u02/demo/data/hyd02.dbf’ size 100m autoextend on;

Deleting a datafile from a tablespace

Sql> alter tablespace hyd drop datafile  ‘/u02/demo/data/hyd02.dbf’;

Droping a tablespace

Sql> drop tablespace hyd; --> it will drop tablespace logically at database level.

Sql> drop tablespace hyd including contents and datafiles;
-->  it will drop tablespace logically(database level) and physically(o/s level)

Reusing orphan datafile

Sql> create tablespace hyd datafile ‘/u01/demo/data/hyd01.dbf’ reuse;

Resize a Datafile

Sql> alter database datafile  ‘/u01/demo/data/hyd.dbf’              resize 50m;

Making  a tablespace as read only

Sql> alter tablespace hyd read only;

Sql> select tablespace_name,status from dba_tablespaces;

Sql> alter tablespace hyd read write;

Making a tablespace offline.

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

Sql> alter tablespace hyd online;

Renaming of tablespace

Sql> alter tablespace hyd rename to hyd1;