Tablespace

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

System : Tablespace-contains data dictionary of database

Sysaux : Contains database statistics

Undo : Conatins Pre Image data

Temporary : Temporary oprations are performed in this hz if pga is not enough

Users : Default tablespace for all DB users / Application schemas.



Creating tablespace :

SQL> create tablespace tbs datafile '/u01/slm/prod/tbs01.dbf' size 50m
autoextend on;


Tablespace created.


Check:

SQL>select FILE_NAME from dba_data_files;


Creating tablespace with maxsize:

SQL> create tablespace tech datafile '/u01/slm/prod/tech02.dbf' size 20m
autoextend on maxsize 100m default storage(next 10m);

Tablespace created.

To check datafile and tablespace information:

Datafile:

select FILE_NAME, MAXBYTES from dba_data_files;

Tablespace:

select TABLESPACE_NAME from dba_tablespaces;


To adding a datafile into the an existing tablespace :

SQL> alter tablespace tech add datafile '/u01/azhar/prod/tech02.dbf' size 50m
autoextend on;

Tablespace altered.

Check:

SQL> select TABLESPACE_NAME from dba_tablespaces;


Reusing orphan datafile:

SQL> create tablespace dell datafile '/u01/azhar/prod/tech01.dbf' reuse;

Tablespace created.


To resize a Datafile:

SQL> alter database datafile '/u01/azhar/prod/tech01.dbf' resize 50m;
Database altered.


Check:

SQL> select FILE_NAME, BYTES/1024/1024 from dba_data_files;


Making a tablespace as read only and read write:

SQL> alter tablespace tbs read only;

Tablespace altered.

Check:

SQL> select TABLESPACE_NAME,STATUS from dba_tablespaces;

Making a tablespace as offline and online:

Offline:
SQL> alter tablespace tbs offline;

Tablespace altered.

Online:
SQL> alter tablespace dell online;
Tablespace altered.

Check:
SQL> select TABLESPACE_NAME, STATUS from dba_tablespaces;