This section includes the following topics:
A small database might need only the SYSTEM tablespace; however, it is recommended that you create at least one additional tablespace to store user data separate from data dictionary information. This allows you more flexibility in various database administration operations and can reduce contention among dictionary objects and schema objects for the same datafiles.
Note: The SYSTEM tablespace must always be kept online. See "Online and Offline Tablespaces" ![[*]](jump.gif)
.
All data stored on behalf of stored PL/SQL program units (procedures, functions, packages and triggers) resides in the SYSTEM tablespace. If you create many of these PL/SQL objects, the database administrator needs to plan for the space in the SYSTEM tablespace that these objects use. For more information about these objects and the space that they require, see Chapter 14, "Procedures and Packages", and Chapter 15, "Database Triggers".
Figure 4 - 2. Enlarging a Database by Adding a Datafile to a Tablespace
Alternatively, a database administrator can create a new tablespace (defined by an additional datafile) to increase the size of a database. Figure 4 - 3 illustrates this.
Figure 4 - 3. Enlarging a Database by Adding a New Tablespace
The size of a tablespace is the size of the datafile(s) that constitute the tablespace, and the size of a database is the collective size of the tablespaces that constitute the database.
The third option is to change a datafile's size or allow datafiles in existing tablespaces to grow dynamically as more space is needed. You accomplish this by altering existing files or by adding files with dynamic extension properties. Figure 4 - 4 illustrates this.
Figure 4 - 4. Enlarging a Database by Dynamically Sizing Datafiles
For more information about increasing the amount of space in your database, see the Oracle7 Server Administrator's Guide.
Note: 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. However, the database administrator might take a tablespace offline for any of the following reasons:
You cannot take a tablespace offline if it contains any rollback segments that are in use.
You can bring a tablespace online only in the database in which it was created because the necessary data dictionary information is maintained in the SYSTEM tablespace of that database. An offline tablespace cannot be read or edited by any utility other than Oracle. Thus, tablespaces cannot be transferred from database to database (transfer of Oracle data can be achieved with tools described in Oracle7 Server Utilities).
Oracle automatically changes a tablespace from online to offline when certain errors are encountered (for example, when the database writer process, DBWR, fails in several attempts to write to a datafile of the tablespace). Users trying to access tables in the tablespace with the problem receive an error. If the problem that causes this disk I/O to fail is media failure, the tablespace must be recovered after you correct the hardware problem.
Note: Because you can only bring a tablespace online in the database in which it was created, read-only tablespaces are not meant to satisfy archiving or data publishing requirements.
Whenever you create a new tablespace, it is always created as read-write. The READ ONLY option of the ALTER TABLESPACE command allows you to change the tablespace to read-only, making all of its associated datafiles read-only as well. You can then use the READ WRITE option to make a read-only tablespace writeable again.
Read-only tablespaces cannot be modified. Therefore, they do not need repeated backup. Also, should you need to recover your database, you do not need to recover any read-only tablespaces, because they could not have been modified.
Offline datafiles cannot be accessed. Bringing a datafile in a read-only tablespace online makes the file readable. The file cannot be written to unless its associated tablespace is returned to the read-write state. The files of a read-only tablespace can independently be taken online or offline using the DATAFILE option of the ALTER DATABASE command.
To update a read-only tablespace, you must first make the tablespace writeable. After updating the tablespace, you can then reset it to be read-only.
A temporary tablespace is a tablespace that can only be used for sort segments. No permanent objects can reside in a temporary tablespace. Sort segments are used when a segment is shared by multiple sort operations. One sort segment exists in every instance that performs a sort operation in a given tablespace.
Temporary tablespaces provide performance improvements when you have multiple sorts that are too large to fit into memory. The sort segment of a given temporary tablespace is created at the time of the first sort operation. The sort segment grows by allocating extents until the segment size is equal to or greater than the total storage demands of all of the active sorts running on that instance.
You create temporary tablespaces using the following SQL syntax:
CREATE TABLESPACE tablespace TEMPORARY
You can also alter a tablespace from PERMANENT to TEMPORARY or vice versa using the following syntax:
ALTER TABLESPACE tablespace TEMPORARY
For more information on the CREATE TABLESPACE and ALTER TABLESPACE Commands, see Chapter 4 of Oracle7 Server SQL Reference.