• Define the components of database storage structures.
• Describe how to create different types of tablespaces.
• Understand how to manage extents and segment space in tablespaces. Describe how to modify and drop tablespaces.
• Define the views available to obtain tablespace information.
Focused Explanation
Database Storage Structure
An Oracle database is divided into tablespaces, where each tablespace represents a group of logically related segments. A segment is a database entity, such as a table or index, which requires storage space. For example, a company’s database can contain various tablespaces, such as ACCOUNTS, and SALES. The ACCOUNTS tablespace can be used to group all segments of the Accounts department, and the SALES tablespace can be used to group all segments of the Sales department. The information of a tablespace is stored in one or more datafiles. A datafile contains extents, which represent the storage space of tablespaces.
Note: An extent is composed of data blocks, which represent a fixed number of bytes of disk space.
Creating and Managing Tablespaces
When you create a database, Oracle creates the following three default tablespaces:
• SYSTEM: Contains data dictionary tables and system rollback segments.
• SYSAUX: Contains schema objects, such as spatial data options, XMLDB or Intermedia.
• TEMP: Helps to perform sort operations and stores transient data that persists only for the duration of a session.
To create additional tablespaces, you can use the CREATE DATABASE or CREATE TABLESPACE statement. You can specify the following options with these statements:
• The type of tablespace
• The location of datafiles of the tablespace
• Extent management options for the tablespace
• Segment space management options for the tablespace
• Whether or not a tablespace is a temporary tablespace
• Whether or not a tablespace is an undo tablespace Specifying the Type of the Tablespace
Oracle 10g provides two options to specify the type of a tablespace. A tablespace can be a bigfile tablespace or a smallfile tablespace.
A bigfile tablespace contains a single, but very large, datafile, which can consist of a maximum of 232 data blocks. For example, you can use bigfile tablespaces for creating tablespaces in a database that
contains thousands of read/write datafiles and takes a long time for updating these datafiles. The syntax for creating a bigfile tablespace is:
CREATE BIGFILE TABLESPACE tablespace_name DATAFILE location SIZE sizeoption;
In this syntax, tablespace_name refers to the name of the tablespace, location refers to the location of the datafile, and sizeoption specifies the datafile size.
A smallfile tablespace contains more than one datafile. Each datafile can consist of up to 222 data blocks.
The SYSTEM and SYSAUX tablespaces are created as smallfile tablespaces. The syntax for creating a smallfile tablespace is:
CREATE SMALLFILE TABLESPACE tablespace_name DATAFILE location SIZE sizeoption;
In this syntax, tablespace_name refers to the name of the tablespace, location refers to the location of the datafile, and sizeoption specifies the datafile size.
Note: If you do not specify an option for the type of tablespace with the CREATE TABLESPACE statement, then a smallfile tablespace is created.
Specifying the Location of Datafiles
You mustspecify the location of datafiles either implicitly using Oracle-Managed Files (OMF) or explicitly when creating a tablespace. The following is an example of explicitly specifying the location of datafiles when creating a tablespace:
CREATE SMALLFILE TABLESPACE rec2005mar
DATAFILE ‘/ORADATA/ORCL1/rec2005mar.dbf’ SIZE 25G;
Note: OMF is a feature of an Oracle database that helps you manage datafiles. It allows Oracle to manage these files for you, and reduces the amount of administrative effort required.
If you are using OMF, you do not need to specify the location of the datafiles. To enable OMF, you must set the DB_CREATE_FILE_DEST initialization parameter to the directory in which you want to store your datafiles and temporary files.
The following is an example of setting this parameter:
ALTER SYSTEM SET
DB_CREATE_FILE_DEST='/u01/oradata/orcl10' SCOPE=BOTH;
After the OMF feature is enabled, you do not need to include the DATAFILE clause in the CREATE TABLESPACE statement. The following is an example of how to create a tablespace when the OMF feature is enabled:
CREATE TABLESPACE sales_data;
This statement creates the sales_data tablespace. The datafiles of this tablespace are stored at the location specified by the DB_CREATE_FILE_DEST parameter.
Managing Extents
Oracle provides two methods that you can use to manage extents in a tablespace. These are:
• Dictionary extent management
• Local extent management
Using the dictionary extent management method, the extent management information of a tablespace is stored in data dictionary tables. This extent management technique keeps a record of used and free extents in the data dictionary tables UET$ and FET$, respectively. With this method, whenever an extent is allocated or is free, the information is updated in corresponding data dictionary tables. A tablespace that uses dictionary extent management is called a dictionary-managed tablespace. The following is an example of creating a dictionary-managed tablespace:
CREATE TABLESPACE sales_data
DATAFILE '/ORADATA/ORCL1/salesdata2005.dbf' SIZE 100M EXTENT MANAGEMENT DICTIONARY;
This statement creates a dictionary-managed tablespace sales_data, with datafiles of 100 MB stored at the specified location.
Using the local extent management method, the extent management information of a tablespace is stored in the same tablespace in each datafile. This extent management information is stored using bitmaps.
Each bit of these bitmaps corresponds to a database block or a group of database blocks. Whenever an extent in a tablespace is allocated or is free, the status of bits corresponding to the data blocks of the extent is changed. A tablespace that uses local extent management is called a locally-managed tablespace. The local extent management method provides two options to specify how extents are allocated to locally-managed tablespaces:
• UNIFORM – Ensures that all extents allocated or deallocated from tablespaces are of a specified size. When you use this option, the default size of an extent is 1 MB. The UNIFORM option does not apply for undo tablespaces and is the default option for temporary tablespaces.
• AUTOALLOCATE – Specifies varying size of extents for each segment. When you use this option, Oracle manages the size of extents itself. For example, when you use the AUTOALLOCATE option on the Windows platform with data blocks of size 8 KB, each segment allocates the first 16 extents of size 64 KB, the next 63 extents of size 1 MB, and then allocates the next 120 extents of size 8 MB and so on. This process allows small segments to remain small and large segments to expand without increasing too many extents.
The following is an example to create a locally-managed tablespace named sales2005data with the AUTOALLOCATE option enabled:
CREATE TABLESPACE sales2005data
DATAFILE '/ORADATA/ORCL1/ sales2005data.dbf' size 25G EXTENT MANAGEMENT LOCAL AUTOALLOCATE;
Note: You can change extent management of a tablespace from dictionary-managed to locally-managed, and vice versa, using the DBMS_SPACE_ADMIN package. However, you cannot use this package to change the extent management of the SYSTEM tablespace or temporary tablespaces from locally-managed to dictionary-locally-managed.
Specifying Segment Space Management
Segment space management is applicable only to locally-managed tablespaces. You can specify the MANUAL or AUTO option with the CREATE TABLESPACE statement to manage segments of a locally-managed tablespace.
The MANUAL segment management option identifies available data blocks using free block lists. It uses two parameters, the PCT_FREE and PCT_USED parameters. When you use the MANUAL segment
management option, the Oracle database calculates the space available in a data block after every insert, delete, and update operation. If the space available in the data block is less than the value of the
PCT_FREE parameter, the data block is marked as used and is removed from the free data block list. If the space available is less than the value of the PCT_USED parameter, the database block is marked as free and placed in free data block list. By default, a locally-managed tablespace uses the MANUAL segment storage option. To explicitly specify the MANUAL segment option, you can use the SEGMENT SPACE MANAGEMENT clause with the CREATE TABLESPACE statement. The following example shows how to create a locally-managed tablespace with the MANUAL segment space management option:
CREATE TABLESPACE sales2005data
DATAFILE '/ORADATA/ORCL1/ sales2005data.dbf' SIZE 25G EXTENT MANAGEMENT LOCAL AUTOALLOCATE
SEGMENT SPACE MANAGEMENT MANUAL;
The AUTO space management option uses bitmaps to maintain the record of free data blocks. This space management option does not use the PCT_FREE and PCT_USED parameters. You cannot use the AUTO space management option for the SYSTEM tablespace or temporary tablespaces.
The following example shows how to create a locally-managed tablespace named sales2005data with the AUTO segment space management option:
CREATE TABLESPACE sales2005data
DATAFILE '/ORADATA/ORCL1/ sales2005data.dbf' SIZE 25G EXTENT MANAGEMENT LOCAL AUTOALLOCATE
SEGMENT SPACE MANAGEMENT AUTO;
Specifying a Tablespace as a Temporary Tablespace
A temporary tablespace represents a logical group of temporary database segments. These temporary database segments are commonly created during sorting operations, such as when a query with an ORDER BY or GROUP BY clause is executed or when an index is created using the CREATE INDEX
statement. A temporary tablespace uses tempfiles instead of datafiles for storing information of temporary segments. When temporary segments are created, the Oracle database allocates storage space for tempfiles. You can create temporary tablespaces in two ways:
• Using the DEFAULT TEMPORARY TABLESPACE clause with CREATE DATABASE statement
• Using the CREATE TEMPORARY TABLESPACE statement after creating the database The following is an example of how to create a temporary tablespace named temp_data:
CREATE TEMPORARY TABLESPACE temp_data
TEMPFILE 'D:\ORACLE\ORADATA\ORCL\temp_data01.dbf' SIZE 2G;
Specifying a Tablespace as an Undo Tablespace
An undo tablespace represents a logical group of undo segments. An undo segment is a database entity that stores information to roll back a database transaction or other uncommitted changes to the data. To create an undo tablespace, you can either set the value of the UNDO_MANAGEMENT initialization
parameter to AUTO or use the CREATE UNDO TABLESPACE statement. The following is an example to create an undo tablespace named undotbs by using the CREATE UNDO TABLESPACE statement:
CREATE UNDO TABLESPACE undotbs
DATAFILE '/ORADATA/ORCL1/sales2005data.dbf' SIZE 2G;
Dropping a Tablespace
You use the DROP TABLESPACE statement to drop a tablespace. You can use the optional INCLUDING CONTENTS clause to remove the segments of the tablespace recursively. The following is an example to drop a tablespace:
DROP TABLESPACE sales2005data INCLUDING CONTENTS;
The datafiles of a tablespace are not removed when a tablespace is dropped. To drop a tablespace along with its datafiles, use the following statement syntax:
DROP TABLESPACE tablespace_name INCLUDING CONTENTS AND DATAFILES;
Modifying a Tablespace
You can modify a tablespace using the ALTER TABLESPACE statement. The operations that you can perform using this statement are:
• Renaming a tablespace – Use the ALTER TABLESPACE tablespace_name RENAME TO new_tablespace_name statement to rename a tablespace. In this statement:
o tablespace_name – Refers to the name of the tablespace.
o new_tablespace_name – Refers to the new name for the tablespace.
• Adding a datafile to a tablespace – Use the ALTER TABLESPACE tablespace_name ADD DATAFILE 'datafile_nameandlocation' SIZE datafile_size statement to add a datafile in a tablespace. In this statement:
o tablespace_name – Refers to the name of the tablespace to which the datafile will be added.
o datafile_nameandlocation – Refers to the name and location of the datafile.
o datafile_size – Refers to the size of the datafile.
• Taking a tablespace offline or online – Use the ALTER TABLESPACE tablespace_name OFFLINE statement to take a tablespace offline and the ALTER TABLESPACE
tablespace_name ONLINE statement to make the tablespace online. The tablespace_name refers to the name of the tablespace.
Note: You cannot make a backup of a tablespace when it is online.
• Making a tablespace read-only – Use the ALTER TABLESPACE tablespace_name READ ONLY statement to make a tablespace read-only. To change to a read-only tablespace so that it can be written to, use the ALTER TABLESPACE tablespace_name READ WRITE statement.
• Putting a tablespace in backup mode – Use the ALTER TABLESPACE tablespace_name BEGIN BACKUP statement to put a tablespace in backup mode. Use the ALTER TABLESPACE tablespace_name END BACKUP statement to take a tablespace out of backup mode.
Obtaining Tablespace Information
Oracle provides several views that provide information about tablespaces. These are:
• DBA_TABLESPACES – Contains one row for each tablespace in the database. This view contains information about the tablespace block size, status of the tablespace, contents of the tablespace, the type of extent management, the segment space management, and whether the tablespace is a bigfile tablespace or a smallfile tablespace.
• DBA_DATA_FILES – Contains information about datafiles of each tablespace.
• DBA_TEMP_FILES – Contains information about tempfiles of tablespaces.
• V$TABLESPACE – Contains the same information that the DBA_TABLESPACE view provides and additional information, such as whether or not the tablespace participates in database flash backup operations.