SQL> Select Tablespace_Name,Contents From dba_tablespaces;
Output :
Types of tablespace in Oracle:
- System Tablespace
- Sysaux Tablespace
- undo Tablespace
- Temporary Tablespace
- User/Normal Tablespace
- Example Tablespace
1. System Tablespace:
The system tablespace is create by default. The system tablespace is a permanent tablespace. The system tablespace owner is sys and within all data dictionary. The all data dictionary tables is store in the datafiles. The system tablespace is always online when the database is open.
2. Sysaux Tablespace:
The sysaux tablespace is a permanent tablespace. The sysaux tablespace is a new feature of the Oracle 10g. The sysaux tablespace is a system tablespace and auxiliary tablespace. The sysaux tablespace is a optional database components.
3. Undo Tablespace:
The undo tablespace is show always from undotbs1 name in the database. The undo tablespace is contains the undo data after any updating, deleting of database object. The undo tablespace is used for rolling back transaction.
Create Undo Tablespace:
SQL> Create undo tablespace undo01 datafile
2 ‘C:ORACLEPRODUCT10.2.0ORADATAORCLundo01.dbf’
3 size 100M;
4. Temporary Tablespace:
The temporary tablespace show always from temp name in the database. The temporary tablespace is store in tempfile. All temporary operation are perform in temporary tables. The temporary tablespace is used for the shorting temporary tables.
For example:
SQL> Select * from employee order by salary desc;
Create Temporary Tablespace:
SQL> Create temporary tablespace temp3 tempfile
2 ‘C:ORACLEPRODUCT10.2.0ORADATAORCLTEMP03.dbf’
3 size 10M;
5. User/Normal Tablespace:
The user and normal tablespace is a permanent tablespace. All user tablespace information store/show in the user tablespace.
Create User Tablespace:
SQL> Create user tablespace user02 userfile
2 ‘C:ORACLEPRODUCT10.2.0ORADATAORCLuser02.dbf’
3 size 10M;
6. Example Tablespace:
We will create database through the Database Configuration Assistant when we will select sample schemas option. This sample schemas is create a example tablespace. The sample schemas provide a comman platform for examples.
********************************************************************************************************************* Note : Please not do make backups before using these queries and also confirm them yourself or by aother means as well.
*********************************************************************************************************************