Tablespace
A Tablespace in Oracle represents a location where the actual data related to tables, indexes will be stored. A database consists of one or more tablespace (tablespace made up of one or more datafiles).
Types of Tablespace
The different types of tablespace are as follows,
1. Permanent tablespace
- It contains the persistent schema objects which are stored in the data files.
2. Undo tablespace
- Oracle database maintains the set of information, data records which are used to perform database roll back, recovery operations etc. These are maintained in the undo tablespace when Oracle database is running in Automatic Undo Management Mode.
3. Temporary tablespace
- Data or information used for the session duration are stored in the Temporary tablespace.
- These objects are stored in Temp files.
Tablespace operations
To create a tablespace first login as "sysdba" account type in Oracle.
To create a table space, use the following statement.
This data file specified in the tablespace will be used to store the data for tables, indexes etc., when you create a table you may or may not specify a table space. If it is not mentioned then the data will be stored in the default table space 'SYSTEM'.
The size specified for this tablespace is just 10 MB. But when more data need to be stored, it automatically extends the size by 5 MB.
To drop a tablespace use the following command.
To view the list of tablespace data in the database use the following query.
To view the data file related information use the following query.