Tablespace

Every Oracle user is assigned to a tablespace. The Tablespace itself is managed by Oracle and stores all data objects (such as tables, indexes etc) that belong to its users (and schemas). Data files on the other hand are units of physical storage. Data files have the extension '.DBF'. The name and location of a database's datafiles are specified as part of the CREATE DATABASE command. This list may be added to or modified later on using the 'ALTER DATABASE' command. There is no one-to-one mapping between datafiles and table spaces; the data within a tablespace may be entirely contained in one datafile or multiple datafiles. A simple database may contain just one tablespace and one datafile; a complex one may contain many more tablespaces and more than one datafile per tablespace.

You may view all the tablespaces in a database by either expanding the appropriate link on the OEM console or by issuing a command within SQL plus. The data dictionary views USER_TABLESPACES and DBA_TABLESPACES display information about tablespaces; these views may be queried to get tablespace related information. The following displayes the output for these two methods:

Screenshot 4a: Tablespace Through OEM Console

Screenshot 4b: Tablespace Through CLI

Oracle creates four predefined tablespaces in new databases. Additionally, the allocation of a USER tablespace is typically part of the CREATE DATABASE command. The tablespaces can be described as follows:

• The SYSTEM Tablespace is always online when the database is open. This tablespace contains objects that are relevant to database administration. Although this tablespace can accommodate a sizable amount of objects, it is best to use another tablespace to store user data objects. This is the default tablespace that users access when they first log into the server if an alternative is not provided at database creation time.
• The SYSAUX Tablespace is an auxiliary to the SYSTEM tablespace. It contains extensions of the DBA data in the System database.
• The UNDO Tablespace stores snapshots and lists of commands that are used to undo erroneous commands or sets of commands or recover after system failure. Only undo segments automatically created and managed by the Oracle server are part of this tablespace.
• The TEMP Tablespace contains transient data like intermediate aggregation related information, half sorted tables and much more. A temporary tablespace has to be defined at the time of database creation.
• The USER tablespace is the conventional location of user schemas and objects.

A new tablespace may be created by issuing a 'CREATE TABLESPACE' directive. Datafiles may not be created on their own; they are always associated with a logical unit. The 'DATAFILE' portion of the 'CREATE TABLESPACE' command creates a datafile and associates it with the tablespace. The following example shows the creation of a tablespace named 'newsample' and a datafile called 'newsample.dbf'. Querying the associated data dictionary view after the command is issued shows the new tablespace. Log into oracle as SYSTEM and run the tablespace creation command within the screenshot after customizing the path to the datafile. Take a look at the location you specified for the datafile after the command executes. You should see the 'newsample.dbf' file of size 5 megabytes. The file initially contains just null characters; it only gets filled as data is added to the tablespace.

Screenshot 4c: Create Tablespace

The ALTER TABLESPACE command changes the name of a tablespace. A tablespace may be deleted through the DROP TABLESPACE command. You should see 'Tablespace altered' and 'Tablespace Dropped' messages after running the following commands.

ALTER TABLESPACE newsample RENAME TO newsample2;
DROP TABLESPACE newsample2;

Associated datafiles remain on the system even after dropping a tablespace using just the drop tablespace command; they may be removed manually using Operating System commands. However, if you want to delete all associated datafiles after tablespace deletion, run a DROP TABLESPACE command with an INCLUDING clause:

CREATE TABLESPACE newsample2
DATAFILE 'd:\oraclexe\oradata\xe\newsample2.dbf' SIZE 5M;
 
DROP TABLESPACE newsample2 INCLUDING CONTENTS AND DATAFILES;

A user may create a table in a specific tablespace using the 'TABLESPACE' clause of the 'CREATE TABLE' command. The following command creates a sample table in a newly created tablespace.

CREATE TABLESPACE newsample
DATAFILE 'd:\oraclexe\oradata\xe\newsample.dbf' SIZE 5M;
CREATE TABLE sampletbl (somefield NUMBER) TABLESPACE newsample;

Be sure to delete the sample tablespace and table by issuing the following command:

DROP TABLESPACE newsample INCLUDING CONTENTS AND DATAFILES;

Database administrators continuously monitor used and free space on all the tablespaces within the database. A tablespace should never be filled to capacity as this may interfere with the efficiency and functioning of the database. The data dictionary view USER_FREE_SPACE may be used to check space availability in tablespaces in terms of free extents. Each row in this view represents a free extent in the tablespace; a sum of the 'BYTES' column returns the total number of free space.

Screenshot 4d: Monitor Tablespace

 The above command retrieves the sum of bytes within all free extents of the SYSTEM and USER tablespaces through an aggregate query to a data dictionary view. A tablespace that is almost full or requires maintenance (renaming, backup etc.) may be brought offline through the OFFLINE directive used with an ALTER TABLESPACE command. Users cannot use an offline tablespace.

CREATE TABLESPACE newsample
DATAFILE 'd:\oraclexe\oradata\xe\newsample.dbf' SIZE 5M;
ALTER TABLESPACE newsample OFFLINE NORMAL;

The ONLINE clause allows users to add to or modify the tablespace through associated objects once more:

ALTER TABLESPACE newsample ONLINE

A tablespace nearing capacity may be extended by adding a datafile. Normally, 80% usage is the upper bound capacity beyond which a new datafile should be added to the tablespace:

ALTER TABLESPACE newsample ADD DATAFILE
'd:\oraclexe\oradata\xe\newsample2.dbf' SIZE 5M;

If the newsample2.dbf file becomes corrupted or useless, the DBA may choose to drop it. Although datafiles cannot be created separately, corrupted or unnecessary datafiles may be removed from a database through the ALTER DATABASE DATAFILE command. Before running this command, the database should be shutdown through the 'SHUTDOWN' command and started up without opening through the 'STARTUP MOUNT' command. The following screenshot shows a sequence of datafile removal commands run using the start -> Oracle Database 10g Express Edition -> Run SQL Command Line menu item for the Oracle XE 10g installation.

Fig 4e: Remove Datafile