DBA

Tablespace :-

Logical

database ---- > tablespace --- > segment ---- > extent ---- > Oracle block

* An Oracle Database can be logically grouped into smaller logical areas of space Known as Tablespace

* Tablespace Consists of one or more datafiles.

* It consists of zero or more segments


Creating Tablespace

sql>create tablespace azar datafile '\oracle\oradata\azar01.dbf' size 20m;

Check your tablespace view dba_tablespaces

sql>select * from dba_tablespaces;

sql>alter tablespace azar read only;

Your tablespace in read only mode you can drop your tablespace contents.

sql>select tablespace_name,status from dba_tablespaces;

sql>alter tablespace azar read write;

sql>select tablespace_name,status from dba_tablespaces;

sql>alter tablespace azar offline;

sql>alter tablespace azar online;

Increase your size tablespace in three Ways :-

sql>alter tablespace azar datafile '\oracle\oradata\azar01.dbf' size 20m autoextend on;

sql>alter tablespace azar add datafile '\oracle\oradata\azar02.dbf' size 10m;

sql>alter database datafile '\oracle\oradata\azar01.dbf' resize 30m;

View:-

dba_data_files

dba_free_space

Moving tablespace from one location to another location

• Offline your tablespace
• Cut your datafile and paste datafile in target place
• sql>alter tablespace azar rename datafile '\oracle\oradata\azar01.dbf' to 'any path' ;
• Online your tablespace

Create temporary tablespace :-

Using for only sort operation

sql> Create temporary tablespace azardba tempfile '\oracle\oradata\temp02.dbf' size 2m;

If you want change your temporary tablespace to default tablespace

sql>alter database default temporay tablespace azardba;

View:-

dba_temp_files

v$tempfile

v$temp_space_header