Oracle Tablespace Management

Oracle Tablespaces Overview and Management

- Oracle divides a database into one or more logical storage units called tablespaces.

- Each tablespace consists of one or more files called datafiles.

- A datafile physically stores the data objects of the database such as tables and indexes on disk.

- Oracle logically stores data in the tablespaces and physically stores data in datafiles associated with the corresponding tablespaces.

- By using tablespaces, you can perform the following operations:

• Control the database size allocated for the data.
• Grant space quotas to the users.
• Control the availability of data by taking tablespaces online or offline.
• Perform partial database backup or recovery.

-  If the datafiles of a tablespace become full we can add new datafiles to that tablespace at any point in time.

- By default when we create a new oracle database then some default tablespaces will be created. They are
            System tablespace
            Sysaux tablespace
            Undo tablespace
            Temporary tablespace
            Default permanent tablespace/users tablespace


Tablespaces can be manipulated with the following sql commands,

1. CREATE
2. DROP
3. ALTER

Examples for Create Tablespace:

Permanent tablespace:

SQL> create tablespace test datafile '/u01/app/oradata/test01.dbf' SIZE 100M autoextend on;

Create more than one datafile with a single create tablespace command:

SQL> create tablespace test datafile '/u01/app/oradata/test01.dbf' size 100M autoextend on,
'/u01/app/oradata/test02.dbf' SIZE 100M AUTOEXTEND ON,
'/u01/app/oradata/test03.dbf' SIZE 100M AUTOEXTEND ON logging extent management local;

Temporary Tablespace:

SQL> Create temporary tablespace temp01 tempfile '/u01/app/oradata/TEMP01.DBF' size 2048m;

Undo tablespace:

SQL> create undo tablespace UNDOTBS datafile '/u01/app/oradata/undotbs01.dbf' size 2048M;

Big file tablespace (Introducing in Oracle10g):

SQL> Create bigfile tablespace big_tbs datafile '/u01/app/oradata/big_tbs01.dbf' SIZE 100G;

Extend Size of a tablespace:

SQL> alter database datafile '/u01/app/oradata/test01.dbf' resize 1024M;

Adding datafile:

SQL> alter tablespace test add datafile '/u01/app/oradata/test02.dbf' size 1024M;

Tablespaces have two status

1. ONLINE (DEFAULT STATUS OF A TABLESPACE)
2. OFFLINE

If a tablespace is offline, you cannot access data stored in it. On the other hand, if a tablespace is online, its data is available for reading and writing.

Normally, a tablespace is online so that its data is available to users. However, you can take a tablespace offline to make data inaccessible to users when you update and maintain the applications.

In case of some errors such as hardware failures, Oracle automatically takes an online tablespace offline. Any attempt to access data in offline tablespace will result in an error.

Examples for making tablespace to offline and online:

SQL> alter tablespace test offline;
SQL> alter tablespace test online;

An online tablespace has 2 modes

1. READ WRITE (Default mode of a ts)
2. READ ONLY

The read-only tablespaces allow Oracle to avoid performing backup and recovery of large, static parts of a database. Because Oracle doesn’t update the files of a read-only tablespace, you can store the files on the read-only media.

Examples for making tablespace to Read Write and Read only mode:

SQL> alter tablespace test read only;
SQL> alter tablespace test read write;

Drop tablespaces:

SQL>drop tablespace test including contents and datafiles;

Tablespace related data dictionary views:

1. DBA_TABLESPACES
2. DBA_DATA_FILES
3. V$DATAFILES
4. V$TEMPFILE
5. DBA_SEGMENTS
6. DBA_EXTENTS
7. USER_SEGMENTS
8. USER_EXTENTS
9. SM$TS_FREE
10. SM$TS_USED