Oracle 10g Free Training - Defining and Creating Tablespaces:This tutorial provides detailed steps with introduction to tablespaces, Datafiles and Creating Tablespaces using three ways via SQL Plus, iSQL Plus and Enterprise Manager and also demonstrates the differences in creating the tablespaces using these 3 methods.

Defining Tablespaces

Each Oracle database is divided into one or more logical units called tablespaces. A tablespace is a logical grouping of related data.

Each database has at least one Tablespace called SYSTEM Tablespace. As part of the process of creating the database, Oracle automatically creates the SYSTEM tablespace. Although a small database can fit within the SYSTEM tablespace, creating a separate tablespace for user data is recommended.

A database administrator can use Tablespaces to do the following:

1. Control disk space allocation for database data.
2. Assign specific space quotas for database users.
3. Perform partial database backup or recovery operations.
4. Allocate data storage across devices to improve performance.

Defining Data Files

Data is stored in the Oracle database in form of files called as datafiles. Each Tablespace is a collection of one or more Datafiles. Each data file consists of ‘Datablocks’, ‘Extents’ and ‘Segments’.

Some systems put a limit on the number of datafiles that can be used. So, efficient definition of tablespaces and datafiles is important.

Creating Tablespaces

There are three ways to create a tablespace in Oracle10g: Using SQL Plus, Using iSQL Plus, Using Enterprise Manager.

Creating Tablespaces using SQL Plus

1. Logon to 10g SQL*PLUS using your administrator user id and password.
2. Type the following command on the SQL prompt (figure 1):
   CREATE SMALLFILE TABLESPACE "TESTTBLSPCE" DATAFILE 'C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\testdtfile' SIZE 100M LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;

Figure -1 Creating Tablespaces using SQL Plus

Note: Oracle10g provides a facility to alert the DBA about filling up of table space, based on the values of tablespace thresholds. This will alert the DBA when the tablespace fills up to ‘Warning’ and ‘Critical’ levels. To activate these alerts, set the thresholds using the following code.

Creating Tablespaces using iSQL Plus

1. To connect to iSQL*PLUS, open the Internet Explorer and enter the following URL.
http://:5560/isqlplus

2. Use the administrator user id and password to login to iSQL*PLUS.

3. On the ‘Workspace’ tab (figure 2), enter the following command:
CREATE SMALLFILE TABLESPACE "TESTTBLSPAC" DATAFILE 'C:\ORACLE\PRODUCT\10.1.0 \ORADATA\ORCL\testdatfile' SIZE 100M LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO

4. Click ‘Execute’.

5. The iSQL* Plus window displays ‘Tablespace Created’.

Figure -2 Creating Tablespaces using iSQL Plus

Note: The tablespace thresholds can be set in iSQL Plus using the following code:

BEGIN DBMS_SERVER_ALERT.SET_THRESHOLD(9000,NULL,NULL,NULL,NULL,1,1,NULL,5,'TESTTBLSPC');
END;