Release 8.0
A58397-01
 Library |
 Product |
 Contents |
 Index |
| Oracle8 Administrator's Guide Release 8.0 A58397-01 |
|
This chapter describes the various aspects of tablespace management, and includes the following topics:
This chapter contains several references to Oracle Enterprise Manager. For more information about performing specific tasks using Enterprise Manager/GUI or Server Manager/LineMode, see the Oracle Enterprise Manager Administrator's Guide and Oracle Enterprise Manager User's Guide.
Before working with tablespaces of an Oracle database, consider the guidelines in the following sections:
Using multiple tablespaces allows you more flexibility in performing database operations. For example, when a database has multiple tablespaces, you can perform the following tasks:
Some operating systems set a limit on the number of files that can be simultaneously open; these limits can affect the number of tablespaces that can be simultaneously online. To avoid exceeding your operating system's limit, plan your tablespaces efficiently. Create only enough tablespaces to fill your needs, and create these tablespaces with as few files as possible. If you need to increase the size of a tablespace, add one or two large datafiles, or create datafiles with the autoextend option set on, rather than many small datafiles.
Review your data in light of these advantages and decide how many tablespaces you will need for your database design.
When you create a new tablespace, you can specify default storage parameters for objects that will be created in the tablespace. Storage parameters specified when an object is created override the default storage parameters of the tablespace containing the object. However, if you do not specify storage parameters when creating an object, the object's segment automatically uses the default storage parameters for the tablespace.
Set the default storage parameters for a tablespace to account for the size of a typical object that the tablespace will contain (you estimate this size). You can specify different storage parameters for an unusual or exceptional object when creating that object.
|
Note: If you do not specify the default storage parameters for a new tablespace, the default storage parameters of Oracle become the tablespace's default storage parameters. |
See Also: For information about estimating the sizes of objects, see Chapters 9 through 16.
Grant users who will be creating tables, clusters, snapshots, indexes, and other objects the privilege to create the object and a quota (space allowance or limit) in the tablespace intended to hold the object's segment. The security administrator is responsible for granting the required privileges to create objects to database users and for assigning tablespace quotas, as necessary, to database users.
See Also: To learn more about assigning tablespace quotas to database users, see "Assigning Tablespace Quotas".
The steps for creating tablespaces vary by operating system. On most operating systems you indicate the size and fully specified filenames when creating a new tablespace or altering a tablespace by adding datafiles. In each situation Oracle automatically allocates and formats the datafiles as specified. However, on some operating systems, you must create the datafiles before installation.
The first tablespace in any database is always the SYSTEM tablespace. Therefore, the first datafiles of any database are automatically allocated for the SYSTEM tablespace during database creation.
You might create a new tablespace for any of the following reasons:
To increase the total size of the database you can alternatively add a datafile to an existing tablespace, rather than adding a new tablespace.
|
Note: No data can be inserted into any tablespace until the current instance has acquired at least two rollback segments (including the SYSTEM rollback segment). |
To create a new tablespace, use either the Create Tablespace property sheet of Enterprise Manager/GUI, or the SQL command CREATE TABLESPACE. You must have the CREATE TABLESPACE system privilege to create a tablespace.
As an example, let's create the tablespace RB_SEGS (to hold rollback segments for the database), with the following characteristics:
The following statement creates the tablespace RB_SEGS:
CREATE TABLESPACE rb_segs DATAFILE 'datafilers_1' SIZE 50M DEFAULT STORAGE ( INITIAL 50K NEXT 50K MINEXTENTS 2 MAXEXTENTS 50 PCTINCREASE 0) OFFLINE;
If you do not fully specify filenames when creating tablespaces, the corresponding datafiles are created in the current directory of the database server.
See Also: See your operating system-specific Oracle documentation for information about initially creating a tablespace.
For more information about adding a datafile, see "Creating and Adding Datafiles to a Tablespace".
For more information about the CREATE TABLESPACE statement, see the Oracle8 Reference.
If you wish to improve the concurrency of multiple sort operations, reduce their overhead, or avoid Oracle space management operations altogether, you can create temporary tablespaces.
Within a temporary tablespace, all sort operations for a given instance and tablespace share a single sort segment. Sort segments exist in every instance that performs sort operations within a given tablespace. You cannot store permanent objects in a temporary tablespace. You can view the allocation and deallocation of space in a temporary tablespace sort segment via the V$SORT_SEGMENT table.
To identify a tablespace as temporary during tablespace creation, issue the following statement:
CREATE TABLESPACE tablespace TEMPORARY;
To identify a tablespace as temporary in an existing tablespace, issue the following statement:
ALTER TABLESPACE tablespace TEMPORARY;
See Also: For more information about the CREATE TABLESPACE and ALTER TABLESPACE commands, see the Oracle8 SQL Reference.
For more information about V$SORT_SEGMENT, see the Oracle8 Reference.
For more information about Oracle space management, see Oracle8 Concepts.
This section describes aspects of managing tablespace allocation, and includes the following topics:
You can change the default storage parameters of a tablespace to change the default specifications for future objects created in the tablespace. To change the default storage parameters for objects subsequently created in the tablespace, use either the Alter Tablespace property sheet of Enterprise Manager/GUI, or the SQL command ALTER TABLESPACE. Also, to alter the default storage parameters of a tablespace, you must have the ALTER TABLESPACE system privilege.
ALTER TABLESPACE users DEFAULT STORAGE ( INITIAL 50K NEXT 50K MINEXTENTS 2 MAXEXTENTS 20 PCTINCREASE 50);
New values for the default storage parameters of a tablespace affect only future extents allocated for the segments within the tablespace.
Space for tablespace segments is managed using extents, which are comprised of a specific number of contiguous data blocks. The free extent closest in size to the required extent is used when allocating new extents to a tablespace segment. Thus, a larger free extent can be fragmented, or smaller contiguous free extents can be coalesced into one larger free extent (see Figure 8-1). However, continuous allocation and deallocation of free space fragments your tablespace and makes allocation of larger extents more difficult. By default, SMON (system monitor) processes incrementally coalesce the free extents of tablespaces in the background. If desired, you can disable SMON coalescing.
If you find that fragmentation of space is high (contiguous space on your disk appears as non-contiguous), you can coalesce your free space in a single space transaction. After every eight coalesces the space transaction commits and other transactions can allocate or deallocate space. You must have ALTER TABLESPACE privileges to coalesce tablespaces. You can coalesce all available free space extents in a tablespace into larger contiguous extents on a per tablespace basis by using the following command:
ALTER TABLESPACE tablespace COALESCE;
You can also use this command to supplement SMON and extent allocation coalescing, thereby improving space allocation performance in severely fragmented tablespaces. Issuing this command does not effect the performance of other users accessing the same tablespace. Like other options of the ALTER TABLESPACE command, the COALESCE option is exclusive; when specified, it should be the only option.
To display statistics about coalesceable extents for tablespaces, you can view the DBA_FREE_SPACE_COALESCED view. You can query this view to determine if you need to coalesce space in a particular tablespace.
See Also: For information about the contents of DBA_FREE_SPACE_COALESCED, see the Oracle8 Reference.
You can bring an offline tablespace online to make the schema objects within the tablespace available to database users. Alternatively, you can take an online tablespace offline while the database is open, so that this portion of the database is temporarily unavailable for general use but the rest is open and available. This section includes the following topics:
You can bring any tablespace in an Oracle database online whenever the database is open. The only exception is that the SYSTEM tablespace must always be online because the data dictionary must always be available to Oracle. A tablespace is normally online so that the data contained within it is available to database users.
To bring an offline tablespace online while the database is open, use either the Place Online menu item of Enterprise Manager/GUI, or the SQL command ALTER TABLESPACE. You must have the MANAGE TABLESPACE system privilege to bring a tablespace online.
The following statement brings the USERS tablespace online:
ALTER TABLESPACE users ONLINE;
You may wish to take a tablespace offline for any of the following reasons:
To take an online tablespace offline while the database is open, use either the Take Offline menu item of Enterprise Manager/GUI, or the SQL command ALTER TABLESPACE. You must have the MANAGE TABLESPACE system privilege to take a tablespace offline.
You can specify any of the following priorities when taking a tablespace offline:
Take a tablespace offline temporarily only when you cannot take it offline normally; in this case, only the files taken offline because of errors need to be recovered before the tablespace can be brought online. Take a tablespace offline immediately only after trying both the normal and temporary options.
The following example takes the USERS tablespace offline normally:
ALTER TABLESPACE users OFFLINE NORMAL;
See Also: Before taking an online tablespace offline, verify that the tablespace contains no active rollback segments. For more information see "Taking Rollback Segments Offline".
This section describes issues related to making tablespaces read-only, and includes the following topics:
Making a tablespace read-only prevents further write operations on the datafiles in the tablespace. After making the tablespace read-only, you should back it up.
Use the SQL command ALTER TABLESPACE to change a tablespace to read-only. You must have the ALTER TABLESPACE system privilege to make a tablespace read-only. The following statement makes the FLIGHTS tablespace read-only:
ALTER TABLESPACE flights READ ONLY
After a tablespace is read-only, you can copy its files to read-only media. You must then rename the datafiles in the control file to point to the new location by using the SQL command ALTER DATABASE RENAME.
A read-only tablespace is neither online nor offline. Issuing the ALTER TABLESPACE command with the ONLINE or OFFLINE option does not change the read-only state of the tablespace; rather, it causes all of the datafiles in the tablespace to be brought online or offline.
Before you can make a tablespace read-only, the following conditions must be met. It may be easiest to meet these restrictions by performing this function in restricted mode, so that only users with the RESTRICTED SESSION system privilege can be logged on.
This is necessary to ensure that there is no undo information that needs to be applied to the tablespace.
For this reason, the SYSTEM tablespace can never be made read-only, since it contains the SYSTEM rollback segment. Additionally, because the rollback segments of a read-only tablespace are not accessible, it is recommended that you drop the rollback segments before you make a tablespace read-only.
For better performance while accessing data in a read-only tablespace, you might want to issue a query that accesses all of the blocks of the tables in the tablespace just before making it read-only. A simple query, such as SELECT COUNT (*), executed against each table will ensure that the data blocks in the tablespace can be subsequently accessed most efficiently. This eliminates the need for Oracle to check the status of the transactions that most recently modified the blocks.
See Also: For more information about read-only tablespaces, see Oracle8 Concepts.
Whenever you create a tablespace, it is both readable and writeable. To change a read-only tablespace back to a read-write tablespace, use the SQL command ALTER TABLESPACE. You must have the ALTER TABLESPACE system privilege to change a read-only tablespace to a read-write tablespace. The following command makes the FLIGHTS tablespace writeable:
ALTER TABLESPACE flights READ WRITE;
Making a read-only tablespace writeable updates the control file for the datafiles, so that you can use the read-only version of the datafiles as a starting point for recovery.
To issue this command, all of the datafiles in the tablespace must be online. Use the DATAFILE ONLINE option of the ALTER DATABASE command to bring a datafile online. The V$DATAFILE view lists the current status of a datafile.
To create a read-only tablespace on a WORM (Write Once Read Many) device when you have read-only files that do not require updating:
You can drop a tablespace and its contents (the segments contained in the tablespace) from the database if the tablespace and its contents are no longer required. Any tablespace in an Oracle database, except the SYSTEM tablespace, can be dropped. You must have the DROP TABLESPACE system privilege to drop a tablespace.
When you drop a tablespace, only the file pointers in the control files of the associated database are dropped. The datafiles that constituted the dropped tablespace continue to exist. To free previously used disk space, delete the datafiles of the dropped tablespace using the appropriate commands of your operating system after completing this procedure.
You cannot drop a tablespace that contains any active segments. For example, if a table in the tablespace is currently being used or the tablespace contains an active rollback segment, you cannot drop the tablespace. For simplicity, take the tablespace offline before dropping it.
After a tablespace is dropped, the tablespace's entry remains in the data dictionary (see the DBA_TABLESPACES view), but the tablespace's status is changed to INVALID.
To drop a tablespace, use either the Drop tablespace menu item of Enterprise Manager/GUI, or the SQL command DROP TABLESPACE. The following statement drops the USERS tablespace, including the segments in the tablespace:
DROP TABLESPACE users INCLUDING CONTENTS;
If the tablespace is empty (does not contain any tables, views, or other structures), you do not need to check the Including Contained Objects checkbox. If the tablespace contains any tables with primary or unique keys referenced by foreign keys of tables in other tablespaces and you want to cascade the drop of the FOREIGN KEY constraints of the child tables, select the Cascade Drop of Integrity Constraints checkbox to drop the tablespace.
Use the CASCADE CONSTRAINTS option to cascade the drop of the FOREIGN KEY constraints in the child tables.
See Also: For more information about taking tablespaces offline, see "Taking Tablespaces Offline".
For more information about the DROP TABLESPACE statement, see the Oracle8 SQL Reference.
The following data dictionary views provide useful information about tablespaces of a database:
The following examples illustrate how to use the views not already illustrated in other chapters of this manual. They assume you are using a database that contains two tablespaces, SYSTEM and USERS. USERS is made up of two files, FILE1 (100MB) and FILE2 (200MB); the tablespace has been taken offline normally.
To list the names and default storage parameters of all tablespaces in a database, use the following query on the DBA_TABLESPACES view:
SELECT tablespace_name "TABLESPACE", initial_extent "INITIAL_EXT", next_extent "NEXT_EXT", min_extents "MIN_EXT", max_extents "MAX_EXT", pct_increase FROM sys.dba_tablespaces; TABLESPACE INITIAL_EXT NEXT_EXT MIN_EXT MAX_EXT PCT_INCREASE ---------- ----------- -------- ------- ------- ------------ SYSTEM 10240000 10240000 1 99 50 USERS 10240000 10240000 1 99 50
To list the names, sizes, and associated tablespaces of a database, enter the following query on the DBA_DATA_FILES view:
SELECT file_name, bytes, tablespace_name FROM sys.dba_data_files; FILE_NAME BYTES TABLESPACE_NAME ------------ ---------- ------------------- filename1 10240000 SYSTEM filename2 10240000 USERS filename3 20480000 USERS
To see the amount of space available in the free extents of each tablespace in the database, enter the following query:
SELECT tablespace_name, file_id, COUNT(*) "PIECES", MAX(blocks) "MAXIMUM", MIN(blocks) "MINIMUM", AVG(blocks) "AVERAGE", SUM(blocks) "TOTAL" FROM sys.dba_free_space WHERE tablespace_name = 'SYSTEM' GROUP BY tablespace_name, file_id; TABLESPACE FILE_ID PIECES MAXIMUM MINIMUM AVERAGE SUM ---------- ------- ------ ------- ------- ------- ------ SYSTEM 1 2 2928 115 1521.5 3043
SUM shows the amount of free space in each tablespace, PIECES shows the amount of fragmentation in the datafiles of the tablespace, and MAXIMUM shows the largest contiguous area of space. This query is useful when you are going to create a new object or you know that a segment is about to extend, and you want to make sure that there is enough space in the containing tablespace.
