3.2. Managing Tablespaces

Tablespaces physically group schema objects for administration convenience. They bridge physical structures, such as datafiles or extents, and logical structures, such as tables and indexes. Tablespaces can store zero or more segments. Segments are schema objects that require storage outside the data dictionary. Tables and indexes are examples of segments. Constraints and sequences are examples of schema objects that do not store data outside the data dictionary and are therefore not segments.

Figure 3.1. Segment, extents, and data blocks

You can place the tables and indexes associated with an application into a set of tablespaces in order to manage that data more easily. You can take a tablespace offline and recover it (potentially to a different point in time), separate from the rest of the database. You can also move it to another database and configure it as read-only so that you do not have to make additional backups of static data.

Another common use for tablespaces is performance related. You can physically separate tables from indexes onto separate disk drives without using a logical volume manager. By placing tables into a tablespace that sits on datafiles residing on one set of disk drives and placing the indexes for those tables into another tablespace that sits on datafiles residing on different disk drives, you can separate table and index segments, thereby reducing any disk contention that might otherwise occur on heavily accessed tables. The following example creates two tablespaces—one for tables and one for indexes:

CREATE TABLESPACE HR_DATA DATAFILE 'D:oracleoradataora10HR_DATA01.DBF'
      SIZE 2G;

CREATE TABLESPACE HR_INDX DATAFILE 'F:oracleoradataora10HR_INDX01.DBF'
      SIZE 2G;

In the following sections, you will learn how to create and manage tablespaces in your database.

3.2.1. Identifying Default Tablespaces

The SYSTEM tablespace is used for the data dictionary and should not be used to store schema objects other than those that the database places there. The SYSAUX tablespace is new to Oracle10g and stores schema objects associated with Oracle-provided features, such as the spatial data option, XMLDB (eXtensible Markup Language DataBase), or Intermedia.

The SYSTEM and SYSAUX tablespaces are always created when the database is created. (SYSAUX is created when you upgrade an older database to Oracle 10g.) One or more temporary tablespaces are usually created in a database as well as an undo tablespace and several application tablespaces. Because SYSTEM and SYSAUX are the only tablespaces always created with the database, they are the default tablespaces. You should not, however, continue to use them as the default tablespace for your users or applications. In the following sections, you will learn how to create additional tablespaces and enable their use as better defaults.

3.2.2. Creating and Maintaining Tablespaces

You create tablespaces using either the CREATE DATABASE or the CREATE TABLESPACE statement. You must make several choices when creating a tablespace: whether to make the tablespace bigfile or smallfile, whether to manage extents locally or with the dictionary, and whether to manage segment space automatically or manually. Additionally, there are specialized tablespaces for temporary segments and undo segments.

3.2.2.1. Creating Bigfile and Smallfile Tablespaces

New to Oracle10g is the bigfile tablespace. Bigfile tablespaces are built on a single datafile (or temp file), which can be as many as 2³² data blocks in size. So, a bigfile tablespace that uses 8KB data blocks can be as much as 32TB in size.

Bigfile tablespaces are intended for very large databases. When a very large database has thousands of read/write datafiles, operations that must update the datafile headers, such as checkpoints, can take a relatively long time. If you reduce the number of datafiles, these operations can complete faster.

To create a bigfile tablespace, use the keyword BIGFILE in the CREATE statement, like this:

CREATE BIGFILE TABLESPACE hist2004apr
DATAFILE '/ORADATA/PROD/HIST2004APR.DBF' SIZE 25G;

Smallfile tablespace is the new name for the old Oracle tablespace datafile option. With a smallfile tablespace, you can have multiple datafiles for a tablespace. Each datafile can be as many as 2²² data blocks in size. So datafiles in a smallfile tablespace that uses 8KB data blocks are limited to 32GB. The smallfile tablespace can have as many as 1,022 datafiles, limiting the 8KB data block tablespace to slightly less than 32TB—about the same as a bigfile tablespace. The SYSTEM and SYSAUX tablespaces are always created as smallfile tablespaces.

To create a smallfile tablespace, either omit the keyword BIGFILE or explicitly use the keyword SMALLFILE, like this:

CREATE TABLESPACE hist2004apr
DATAFILE '/ORADATA/PROD/HIST2004APR.DBF' SIZE 25G;

3.2.2.2. Working with Oracle Managed File Tablespaces

Oracle Managed Files (OMF) can ease the administration of files used by an Oracle10g database. Using OMF, you specify operations in terms of tablespaces and not operating system files. You don't explicitly name datafiles or temp files; the database does this for you.

To enable OMF, set the initialization parameter DB_CREATE_FILE_DEST to the directory where you want the database to create and manage your data and temp files, like this:

ALTER SYSTEM SET
  db_create_file_dest = 'D:oracleoradataora10OMF'
  SCOPE=BOTH;

When creating a tablespace using OMF, you simply omit the filename:

CREATE TABLESPACE hr_data;

Oracle creates a tablespace using a unique datafile name, such as O1_MF_HR_DATA_0DC3Z9WL_.DBF. This datafile will have auto extend enabled and be 100MB unless you specify a different size. By default, the tablespace is a smallfile tablespace, but you can specify a bigfile tablespace by including the keyword BIGFILE, like this:

CREATE BIGFILE TABLESPACE hr_data;

If you work in an environment in which many databases are managed under both Windows and Unix, you can use OMF to eliminate operating system–specific filenames from your administrative scripts.

3.2.2.3. Choosing Extent Management

You can use tablespaces with either local extent management or the older technique of dictionary extent management. With dictionary extent management, the database tracks free and used extents in the data dictionary, changing the FET$ and UET$ tables with recursive SQL. With local extent management, the database tracks extents through the use of bitmaps, eliminating the recursive SQL. Local extent management is the default if not specified and is generally the preferred technique.

With locally managed tablespaces, you have two options for how extents are allocated: UNIFORM or AUTOALLOCATE. The UNIFORM option tells the database to allocate and deallocate extents in the tablespace with the same unvarying size that you can specify or let extents default to 1MB. UNIFORM is the default for temporary tablespaces and cannot be specified for undo tablespaces. To create consistent 100MB extents, use the clause EXTENT MANAGEMENT LOCAL UNIFORM SIZE 100M in the CREATE TABLESPACE statement. Here is an example:

CREATE TABLESPACE hist2004apr
DATAFILE '/ORADATA/PROD/HIST2004APR.DBF' SIZE 25G
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 100M;

AUTOALLOCATE, on the other hand, tells the database to vary the size of extents for each segment. For example, on Windows and Linux with 8KB data blocks, each segment starts out with 64KB extents for the first 16 extents, and then extents increase in size to 1MB for the next 63 extents. The size then increases to 8MB for the next 120 extents, then 64MB, and so on as the segment grows. This algorithm allows small segments to remain small and large segments to grow without gaining too many extents. AUTOALLOCATE is best used for a general-purpose mixture of small and large tables. Here is an example of creating a tablespace using AUTOALLOCATE:

CREATE TABLESPACE hist2004apr
DATAFILE '/ORADATA/PROD/HIST2004APR.DBF' SIZE 25G
EXTENT MANAGEMENT LOCAL AUTOALLOCATE;

You can convert a tablespace from dictionary extent management to local extent management and back with the Oracle-supplied PL/SQL package DBMS_SPACE_ADMIN. The SYSTEM tablespace and any temporary tablespaces, however, cannot be converted from local to the older style dictionary management.

3.2.2.4. Choosing Segment Space Management

For tablespaces that have local extent management, you can use either manual or automatic segment space management. Manual segment space management exists for backward compatibility and uses free block lists to identify the data blocks available for inserts together with the parameters PCT_FREE and PCT_USED, which control when a block is made available for inserts.

After each INSERT or UPDATE, the database compares the remaining free space in that data block with the segment's PCT_FREE setting. If the data block has less than PCT_FREE free space (meaning that it is almost full), it is taken off the free block list and is no longer available for inserts. The remaining free space is reserved for update operations that may increase the size of rows in that data block. After each UPDATE or DELETE, the database compares the used space in that data block with that segment's PCT_USED setting. If the data block has less than PCT_USED used space, the data block is deemed empty enough for inserts and is placed on the free block list.

To specify manual segment space management, use the SEGMENT SPACE MANAGEMENT MANUAL clause of the CREATE TABLESPACE statement, or simply omit the SEGMENT SPACE MANAGEMENT clause. Although Oracle strongly recommends AUTOMATIC segment space management for permanent, locally managed tablespaces, the default behavior of Oracle 10g is MANUAL. Here is a statement that creates a tablespace with manual segment space management:

CREATE TABLESPACE hist2004apr
DATAFILE '/ORADATA/PROD/HIST2004APR.DBF' SIZE 25G
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
SEGMENT SPACE MANAGEMENT MANUAL;

When automatic segment space management is specified, bitmaps are used instead of free lists to identify which data blocks are available for inserts. The parameters PCT_FREE and PCT_USED are ignored for segments in tablespaces with automatic segment space management. Automatic segment space management is available only on tablespaces configured for local extent management; it is not available for temporary or system tablespaces. Automatic segment space management performs better and reduces your maintenance tasks, making it the preferred technique.

To specify automatic segment space management, use the SEGMENT SPACE MANAGEMENT AUTO clause of the CREATE TABLESPACE statement, like this:

CREATE TABLESPACE hist2004apr
DATAFILE '/ORADATA/PROD/HIST2004APR.DBF' SIZE 25G
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
SEGMENT SPACE MANAGEMENT AUTO;

3.2.2.5. Creating Temporary Tablespaces

A temporary tablespace is used for temporary segments, which are created, managed, and dropped by the database as needed. These temporary segments are most commonly generated during sorting operations such as ORDER BY, GROUP BY, and CREATE INDEX. They are also generated during other operations such as hash joins or inserts into temporary tables.

You create a temporary tablespace at database creation time with the DEFAULT TEMPORARY TABLESPACE clause of the CREATE DATABASE statement or after the database is created with the CREATE TEMPORARY TABLESPACE statement, like this:

CREATE TEMPORARY TABLESPACE temp
TEMPFILE 'C:ORACLEORADATAORA10TEMP01.DBF' SIZE 2G
;

Notice that the keyword TEMPFILE is used instead of DATAFILE. Temp files are only available with temporary tablespaces, never need to be backed up, and do not log data changes in the redo logs.

Temporary tablespaces are created using temp files instead of datafiles. Temp files are allocated slightly differently than datafiles. Although datafiles are completely allocated and initialized at creation time, temp files are not always guaranteed to allocate the disk space specified. This means that on some Unix systems a temp file will not actually allocate disk space until a sorting operation requires it. Although this delayed allocation approach allows rapid file creation, it can cause problems down the road if you have not reserved the space that may be needed at runtime.

-- first create it as a permanent tablespace
-- to force the disk space to be allocated
CREATE TABLESPACE temp
DATAFILE '/ORADATA/PROD/TEMP01.DBF' SIZE 2G;

-- dropping the tablespace does not remove
-- the file from the file system
DROP TABLESPACE temp;

-- the keyword REUSE is needed to use the existing
-- file created in the previous steps
CREATE TEMPORARY TABLESPACE temp
TEMPFILE '/ORADATA/PROD/TEMP01.DBF' SIZE 2G REUSE

3.2.2.6. Creating Undo Tablespaces

An undo tablespace stores undo segments, which are used by the database for several purposes, including the following:

• Rolling back a transaction explicitly with a ROLLBACK statement

• Rolling back a transaction implicitly (for example, through recovery of a failed transaction)

• Reconstructing a read-consistent image of data

• Recovering from logical corruptions

To create an undo tablespace at database creation time, set the initialization parameter UNDO_MANAGEMENT=AUTO and include an UNDO TABLESPACE clause in your CREATE DATABASE statement, like this:

CREATE DATABASE TEST
  CONTROLFILE REUSE
  LOGFILE
       GROUP 1 'C:ORADATATESTREDO01.LOG'  SIZE 10M,
       GROUP 2 'C:ORADATATESTREDO02.LOG'  SIZE 10M,
      GROUP 3 'C:ORADATATESTREDO03.LOG'  SIZE 10M
  DATAFILE  'C:ORADATATESTSYSTEM01.DBF' SIZE 500M
    AUTOEXTEND ON NEXT 50M MAXSIZE UNLIMITED

EXTENT MANAGEMENT LOCAL
  SYSAUX DATAFILE 'C:ORADATATESTSYSAUX01.DBF' SIZE 250M
    AUTOEXTEND ON NEXT 50M MAXSIZE UNLIMITED
     EXTENT MANAGEMENT LOCAL
  DEFAULT TEMPORARY TABLESPACE temp
     TEMPFILE 'C:ORADATATESTTEMP01.DBF' SIZE 100M
     AUTOEXTEND ON NEXT 100M MAXSIZE 8000M
  UNDO TABLESPACE undo
     DATAFILE 'C:ORADATATESTundo01.DBF' SIZE 500M
    AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED
    EXTENT MANAGEMENT LOCAL
  CHARACTER SET WE8MSWIN1252
  NATIONAL CHARACTER SET AL16UTF16
  USER SYS     IDENTIFIED BY SOUPERSEEKRET
  USER SYSTEM IDENTIFIED BY MYSEEKRET
;

You can create an undo tablespace after database creation with the CREATE UNDO TABLESPACE statement, like this:

CREATE UNDO TABLESPACE undo
DATAFILE '/ORADATA/PROD/UNDO01.DBF' SIZE 2G;

3.2.2.7. Removing a Tablespace

To remove a tablespace from the database, use the DROP TABLESPACE statement. The optional clause INCLUDING CONTENTS recursively removes any segments (tables, indexes, and so on) in the tablespace, like this:

DROP TABLESPACE dba_sandbox INCLUDING CONTENTS;

Dropping a tablespace does not automatically remove the datafiles from the file system. Use the additional clause INCLUDING CONTENTS AND DATAFILES to remove the underlying datafiles as well as the stored objects, like this:

DROP TABLESPACE hr_data INCLUDING CONTENTS AND DATAFILES;

3.2.2.8. Modifying Tablespaces

Use an ALTER TABLESPACE statement to modify the attributes of a tablespace. Some of the actions that you can perform on tablespaces include renaming the tablespace, adding a datafile to a smallfile tablespace, taking a tablespace offline or online, making a tablespace read-only or read/write, or changing the user-managed backup state. To rename a tablespace, use an ALTER TABLESPACE statement with a RENAME clause, like this:

ALTER TABLESPACE fin RENAME TO payables;

The following sections detail additional modifications that you may perform on your tablespaces.

3.2.2.8.1. Adding a Datafile to a Tablespace

Smallfile tablespaces can have multiple datafiles and can thus be spread over multiple file systems without engaging a logical volume manager. To add a datafile to a smallfile tablespace, use an ADD DATAFILE clause with the ALTER TABLESPACE statement. For example, the following statement adds a 2GB datafile on in the /u02 file system to the receivables tablespace:

ALTER TABLESPACE receivables ADD DATAFILE
  '/u02/oradata/ORA10/receivables01.dbf'
  SIZE 2G;

3.2.2.8.2. Taking a Tablespace Offline or Online

You need to take a tablespace offline to perform some maintenance operations, such as recovering the tablespace or moving the datafiles to a new location. Use the OFFLINE clause with an ALTER TABLESPACE statement to take a tablespace offline. For example, to take the receivables tablespace offline to move a datafile from the H drive to the G drive, follow these steps.

3.2.2.8.3. Making a Tablespace Read-Only

If a tablespace contains static data, it can be marked read-only. Tablespaces that contain historic or reference data are typical candidates for read-only. When a tablespace is read-only, it does not have to be backed up with the nightly or weekly database backups. One backup after being marked read-only is all that is needed for future recoveries. Tables in a read-only tablespace can only be selected from; their rows cannot be inserted, updated, or deleted.

Use a READ ONLY clause with an ALTER TABLESPACE statement to mark a tablespace readonly. For example, to mark the SALES2003 tablespace read-only, execute the following:

ALTER TABLESPACE sales2003 READ ONLY;

If you need to make changes to a table in a read-only tablespace, make it read writable again with the key words READ WRITE, like this:

ALTER TABLESPACE sales2003 READ WRITE;

3.2.2.8.4. Putting a Tablespace in Backup Mode

If you perform non-RMAN (non–Recovery Manager) online backups, sometimes called usermanaged backups, you need to put a tablespace in backup mode before you begin to copy the datafiles using an operating system program. While the tablespace is in backup mode, the database continues to write data to the datafiles (checkpoints occur), but the occurrence of these checkpoints is not recorded in the header blocks of the datafiles. This omission tells the database that recovery may be needed if the database instance gets terminated abruptly.

While a tablespace is in backup mode, some additional information is written to the redo logs to assist with recovery, if needed.

Some companies perform backups by splitting a third mirror, mounting these mirrored file systems onto another server, and then copying them to tape. To safely split the mirror, alter all your tablespaces into backup mode, make the split, and then alter all the tablespaces out of backup mode. Put them into backup mode like this:

ALTER TABLESPACE system BEGIN BACKUP;

Use the keywords END BACKUP to take a tablespace out of backup mode, like this:

ALTER TABLESPACE system END BACKUP;

If you forget to take a tablespace out of backup mode, the next time you bounce your database, it will see that the checkpoint number in the control file is later than the one in the datafile headers and report that media recovery is required.

3.2.3. Obtaining Tablespace Information

You can get information on tablespaces using the Enterprise Manager (EM) Database Control from several data dictionary views, such as the following:

• DBA_TABLESPACES

• DBA_DATA_FILES

• DBA_TEMP_FILES

• V$TABLESPACE

The DBA_TABLESPACES view has one row for each tablespace in the database and includes the following information:

• The tablespace block size

• The tablespace status: online, offline, or read-only

• The contents of the tablespace: undo, temporary, or permanent

• Whether it uses dictionary or locally managed extents

• Whether the segment space management is automatic or manual

• Whether it is a bigfile or smallfile tablespace

To get a listing of all the tablespaces in the database, their status, contents, extent management policy, and segment management policy, run the following query:

SELECT tablespace_name, status,contents
      ,extent_management extents
      ,segment_space_management free_space
FROM dba_tablespaces

TABLESPACE_NAME    STATUS    CONTENTS  EXTENTS   FREE_SPACE
------------------ --------- --------- --------- ----------
SYSTEM             ONLINE    PERMANENT LOCAL      MANUAL
UNDOTBS1           ONLINE    UNDO      LOCAL      MANUAL
SYSAUX             ONLINE    PERMANENT LOCAL      AUTO
TEMP               ONLINE    TEMPORARY LOCAL      MANUAL
USERS              ONLINE    PERMANENT LOCAL      AUTO
EXAMPLE            ONLINE    PERMANENT LOCAL      AUTO
DATA               ONLINE    PERMANENT LOCAL      AUTO
INDX               ONLINE    PERMANENT LOCAL      AUTO

The V$TABLESPACE view also has one row per tablespace, but it includes some information other than DBA_TABLESPACES, such as whether the tablespace participates in database flashback operations:

SELECT name, bigfile, flashback_on
FROM v$tablespace;

NAME       BIGFILE  FLASHBACK_ON
---------- -------- ------------
SYSTEM     NO        YES
UNDOTBS1   NO        YES
SYSAUX     NO        YES
USERS      NO        YES

TEMP       NO        YES
EXAMPLE    NO        YES
DATA       NO        YES
INDX       NO        YES

The DBA_DATA_FILES and DBA_TEMP_FILES views contain information on datafiles and temp files, respectively. This information includes the tablespace name, filename, file size, and autoextend settings.

SELECT tablespace_name, file_name, bytes/1024 kbytes
FROM dba_data_files
UNION ALL
SELECT tablespace_name, file_name, bytes/1024 kbytes
FROM dba_temp_files;

TABLESPACE FILE_NAME                                  KBYTES
---------- ---------------------------------------- -------
USERS      C:ORACLEORADATAORA10USERS01.DBF        102400
SYSAUX     C:ORACLEORADATAORA10SYSAUX01.DBF       256000
UNDOTBS1   C:ORACLEORADATAORA10UNDOTBS01.DBF       51200
SYSTEM     C:ORACLEORADATAORA10SYSTEM01.DBF       460800
EXAMPLE    C:ORACLEORADATAORA10EXAMPLE01.DBF      153600
INDX       C:ORACLEORADATAORA10INDX01.DBF         102400
TEMP       C:ORACLEORADATAORA10TEMP01.DBF          51200

In addition to the data dictionary, tablespace information can be obtained from several sources. Some of these sources are the DDL and the Enterprise Manager.

3.2.3.1. Generating DDL for a Tablespace

Another way to quickly identify the attributes of a tablespace is to ask the database to generate DDL to re-create the tablespace. The CREATE TABLESPACE statement that results contains the attributes for the tablespace. Use the PL/SQL package DBMS_METADATA to generate DDL for your database objects. For example, to generate the DDL for the USERS tablespace, execute this:

SELECT DBMS_METADATA.GET_DDL('TABLESPACE','USERS')
FROM dual;

The output from this statement is a CREATE TABLESPACE statement that contains all the attributes for the USERS tablespace:

CREATE TABLESPACE "USERS" DATAFILE

'C:ORACLEORADATAORA10USERS01.DBF' SIZE 5242880
AUTOEXTEND ON NEXT 1310720 MAXSIZE 32767M
LOGGING ONLINE PERMANENT BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE
MANAGEMENT AUTO;

3.2.3.2. Obtaining Tablespace Information with the Enterprise Manager

Instead of querying the data dictionary views with a command-line tool such as SQL*Plus or iSQL*Plus, you can use the interactive GUI tool EM Database Control to monitor and manage database structures, including tablespaces. The EM Database Control is an alternative to a command-line interface.

To use the Database Control, follow these steps:

You use the screens and options in EM Database Control to manipulate and change your tablespaces with many of the same options that the command-line interface supports. For example, to increase the size of the datafile in the USER3_DATA tablespace, click the edit button next to the datafile. The EM Database Control displays the tablespace edit screen, as shown in Figure 3.5.

Edit the File Size field, increasing it to 300 MB. The change will be applied when you click Continue.

Figure 3.3. The Enterprise Manager Tablespaces screen

Figure 3.4. The Enterprise Manager tablespace editor

Figure 3.5. Editing the datafile size