Oracle 10g Free Training - Managing Tablespaces

This tutorial provides detailed steps about Managing Tablespaces, Altering Tablespace , Creating an ‘Undo’ tablespace and Renaming a Tablespace using SQL Plus, Using iSQL Plus and Enterprise Manager.

Oracle 10g Free Training - Managing Tablespaces

Altering Tablespace Availability

a) Using SQL Plus:

1. Type the following command on the SQL prompt (figure 3):
ALTER TABLESPACE "TESTTBLSPC" OFFLINE NORMAL;

Figure -3 Altering Tablespace Availability using SQL Plus

b) Using iSQL Plus:

1. On the ‘Workspace’ tab (figure 4), enter the following command:
ALTER TABLESPACE "TESTTBLSPC" OFFLINE NORMAL

Figure -4 Altering Tablespace Availability using iSQL Plus

c) Using Enterprise Manager :

Oracle Enterprise Manager can be used to take an online tablespace offline so that a portion of the database is temporarily unavailable for general use. The rest of the database is open and available for users to access data. Conversely, you can bring an offline tablespace online to make the schema objects within the tablespace available to database users.

Availability of a tablespace can be changed using the following steps:

1. In the Enterprise Manager, navigate to ‘Administration > Tablespaces’.

2. Enter the name of the tablespace that you want to alter and click ‘Go’ (figure 15). For more details on offline tablespaces, refer to the section ‘Creating Tablespaces using Enterprise Manager > Status’.

Figure -15 Search Tablespace Window

3. In the ‘Results’ region, select the tablespace name and click ‘Edit’ (figure 15).

4. In the status region, select the ‘Offline’ option (figure 16).

Figure -16 Edit Tablespace Window

5. Click ‘Apply’ to modify the availability.

Creating an ‘Undo’ tablespace

a) Using SQL Plus:

1. Type the following command on the SQL prompt (figure 5):

CREATE SMALLFILE UNDO TABLESPACE "TESTTBLSPCE" DATAFILE 'C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\testfl' SIZE 100M;

Figure -5 Creating Undo Tablespace using SQL Plus

b) Using iSQL Plus:

1. On the ‘Workspace’ tab (figure 6), enter the following command:

CREATE SMALLFILE UNDO TABLESPACE "TESTTBLSP" DATAFILE 'C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\testfl' SIZE 100M

Figure -6 Creating Undo Tablespace using iSQL Plus

c) Using Enterprise Manager :

Undo tablespaces are special tablespaces used solely for storing undo information. You cannot create any other segment types in undo tablespaces. For more details on offline tablespaces, refer to the section ‘Creating Tablespaces using Enterprise Manager > Type’

An undo tablespace can be created by following the steps given in the section ‘Creating Tablespaces using Enterprise Manager’ and selecting the ‘Undo’ option under the ‘Type’ region (figure 12).

Renaming a Tablespace

a) Using SQL Plus:

1. Type the following command on the SQL prompt (figure 7):
ALTER TABLESPACE "TESTTBLSPCE" RENAME TO "TESTTBLSPEC";

Figure -7 Renaming Tablespace using SQL Plus

b) Using iSQL Plus:

1. On the ‘Workspace’ tab (figure 8), enter the following command:
ALTER TABLESPACE "TESTTBLSPC" RENAME TO "TESTTBLSPEC"

Figure -8 Renaming Tablespace using iSQL Plus

c) Using Enterprise Manager :

To rename an existing tablespace,

1. Navigate to ‘Administration > Tablespaces’ in Enterprise Manager.
2. Enter the name of the tablespace that you want to rename and click ‘Go’ (figure 15).
3. In the ‘Results’ region, select the tablespace name and click ‘Edit’ (figure 15).
4. Enter a new name for the tablespace in the ‘Name’ field.
5. Click ‘Apply’ to modify the availability for the selected tablespace.

Note: You can rename only tablespaces from databases with a version number greater than 10g.

Deleting a tablespace

To rename a tablespace:

1. Navigate to ‘Administration > Tablespaces’ in Enterprise Manager.

2. Enter the name of the tablespace that you want to rename and click ‘Go’ (figure 17).

3. In the ‘Results’ region, select the tablespace name and click ‘Delete’.

Figure -17 Tablespaces Window (Delete tablespace)

4. The ‘Delete Tablespace Window’ is displayed (figure 18) with a warning. Click ‘Yes’ button.

Figure -18 Delete Tablespace Window (Warning)

Frequently Asked Questions

1. What is a tablespace?
2. What is a datafile?
3. Which extent management method leads to faster free space allocation?
4. What is the meaning of an offline tablespace?
5. What is a bigfile tablespace?
6. What are tablespace thresholds?
7. What does the auto extend option in datafiles?
8. How do you create an undo tablespace using SQ Plus, iSQL plus and Enterprise Manager?
9. How to rename a tablespace using SQ Plus, iSQL plus and Enterprise Manager?
10. How to delete a tablespace using SQ Plus, iSQL plus and Enterprise Manager?