Tablespace Management in Oracle Database

In this section we are going to learn how to manage tablespaces in Oracle Database.

Overview:

This article is a comprehensive guide for the management of Tablespaces in Oracle Database.

Using multiple tablespaces allows us more flexibility in performing database operations. When a database has multiple tablespaces, we can:

• Take individual tablespaces offline while others remain online, providing better overall availability.
• Back up individual tablespaces
• Separate user data from data dictionary data to reduce I/O contention.
• Separate data of one application from the data of another.

Ref:Oracle Database Administrator's Guide

Please do keep in mind that this article should not be considered a substitution of the official guide from Oracle (http://www.oracle.com). The below mentioned link can be used to download the official guide for Oracle Database Tablespace Management:

http://download.oracle.com/docs/cd/B28359_01/server.111/b28310/tspaces.htm 

Objective of the Article:

By the time we finish this article, we should be able to understand the following:

(1)Tablespace Creation.

(2)Altering Tablespace Availability.

(3)Making Tablespaces Read-Only.

(4)Renaming Tablespaces.

(5)Deleting Tablespaces.

For the screenshots . Please follow the link:::  http://www.sensehaze.com/

Tablespace Creation:

For tablespace creaton type the below mentioned command:
SQL> create tablespace myts datafile 'location_data_file\myts.dbf' SIZE 100M extent management local ;

Here i have used:
tablespace name --- myts
datafile name --- myts.dbf
datafile size --- 100M
extent management --- local.

Here i have used basic options for tablespace creation, as per our requirement we can use various options.

 For the screenshots . Please follow the link:::  http://www.sensehaze.com/

Altering Tablespace Availability:

For knowing information of tablespaces and their status, query the table "DBA_DATA_FILES". As mentioned below:
SQL> select tablespace_name , online_status from dba_data_files;

Taking a tablespace offline:

SQL> alter tablespace myts offline normal;

Taking a tablespace online:

SQL> alter tablespace myts online normal;

 For the screenshots . Please follow the link:::  http://www.sensehaze.com/

Making Tablespaces Read-Only:

Making a tablespace Read-Only:

SQL> alter tablespace myts read only;

Making a tablespace Read-Write:

SQL> alter tablespace myts read write;

Renaming Tablespaces:

For renaming a tablespace, fire the below command:

SQL> alter tablespace myts rename to mytsnew;

Deleting Tablespaces:

For deleting a tablespace, fire the below command:

drop tablespace myts including contents and datafiles;

For the screenshots . Please follow the link:::  http://www.sensehaze.com/