|Oracle7 Server Administrator's Guide||
Review your data in light of these advantages and decide how many tablespaces you will need for your database design.
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 Oracle7 become the tablespace's default storage parameters.
See Also: For information about estimating the sizes of objects, see Chapters 9 through 16.
See Also: To learn more about assigning tablespace quotas to database users, see page 19 - 11.
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:
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 Server 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:
CREATE TABLESPACE rb_segs
DATAFILE 'datafilers_1' SIZE 50M
DEFAULT STORAGE (
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 "Adding Datafiles to a Tablespace" .
For more information about the CREATE TABLESPACE statement, see the Oracle7 Server SQL Reference.
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_SEGMENTS 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
Note: You can take temporary tablespaces offline. Returning temporary tablespaces online does not affect their temporary status.
See Also: For more information about the CREATE TABLESPACE and ALTER TABLESPACE commands, see the Oracle7 Server SQL Reference.
For more information about V$SORT_SEGMENTS, see the Oracle7 Server Reference.
For more information about Oracle space management, see Oracle7 Server Concepts.
The following example alters the default storage parameters for the tablespace USERS:
ALTER TABLESPACE users
DEFAULT STORAGE (
New values for the default storage parameters of a tablespace affect only future extents allocated for the segments within the tablespace.
Figure 8 - 1. Coalescing Free Space
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.
See Also: For information about the contents of DBA_FREE_SPACE_COALESCED, see the Oracle7 Server Reference.
To bring an offline tablespace online while the database is open, use either the Place Online menu item of Server Manager/GUI, or the SQL command ALTER TABLESPACE. You must have the MANAGE TABLESPACE system privilege to bring a tablespace online.
Note: If a tablespace to be brought online was not taken offline "cleanly" (that is, using the NORMAL option of the ALTER TABLESPACE OFFLINE command), you must first perform media recovery on the tablespace before bringing it online. Otherwise, Oracle7 returns an error and the tablespace remains offline.
The following statement brings the USERS tablespace online:
ALTER TABLESPACE users ONLINE;
You can specify any of the following priorities when taking a tablespace offline:
A tablespace can be taken offline normally if no error conditions exist for any of the datafiles of the tablespace. No datafile in the tablespace can be currently offline as the result of a write error. With normal offline priority, Oracle7 takes a checkpoint for all datafiles of the tablespace as it takes them offline.
A tablespace can be taken offline temporarily, even if there are error conditions for one or more files of the tablespace. With temporary offline priority, Oracle7 takes offline the datafiles that are not already offline, checkpointing them as it does so.
If no files are offline, but you use the temporary option, media recovery is not required to bring the tablespace back online. However, if one or more files of the tablespace are offline because of write errors, and you take the tablespace offline temporarily, the tablespace will require recovery before you can bring it back online.
A tablespace can be taken offline immediately, without Oracle's taking a checkpoint on any of the datafiles. With immediate offline priority, media recovery for the tablespace is required before the tablespace can be brought online. You cannot take a tablespace offline immediately if the database is running in NOARCHIVELOG mode.
Warning: If you must take a tablespace offline, use the normal option (the default) if possible; this guarantees that the tablespace will not require recovery to come back online, even if you reset the redo log sequence (using an ALTER DATABASE OPEN RESETLOGS statement after incomplete media recovery) before bringing the tablespace back online.
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" .
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.
Warning: You cannot rename or resize datafiles belonging to a read-only tablespace.
See Also: For more information about read-only tablespaces, see .
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 Create a Read-Only Tablespace on a WORM Device
Warning: Once a tablespace has been dropped, the tablespace's data is not recoverable. Therefore, make sure that all data contained in a tablespace to be dropped will not be required in the future. Also, immediately before and after dropping a tablespace from a database, back up the database completely. This is strongly recommended so that you can recover the database if you mistakenly drop a tablespace, or if the database experiences a problem in the future after the tablespace has been dropped.
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 Server 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 Oracle7 Server SQL Reference.
SELECT tablespace_name "TABLESPACE",
TABLESPACE INITIAL_EXT NEXT_EXT MIN_EXT MAX_EXT PCT_INCREASE
---------- ----------- -------- ------- ------- ------------
SYSTEM 10240000 10240000 1 99 50
USERS 10240000 10240000 1 99 50
SELECT file_name, bytes, tablespace_name
FILE_NAME BYTES TABLESPACE_NAME
------------ ---------- --------------------
filename1 10240000 SYSTEM
filename2 10240000 USERS
filename3 20480000 USERS
SELECT tablespace_name, file_id,
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.
Copyright © 1996 Oracle Corporation.
All Rights Reserved.