|
DBAs create tablespaces for the ease of control and maintenance over the information and structures within the Oracle database. There are two types of tablespaces in an Oracle database: SYSTEM and everything else. The SYSTEM tablespace is created when you create the database. Its purpose is to hold the data dictionary tables, views, and stored program units. It also holds the SYSTEM undo segment. User data should never be stored in the SYSTEM tablespace although as yet, there is nothing to stop you from doing it. The non-SYSTEM tablespaces are created by the DBA to enable more control and flexibility in database administration. Non-SYSTEM tablespaces allow you to separate UNDO information, temporary information, application data, and application indexes. You can use tablespaces to separate data by backup requirements, by the characteristic of static over dynamic data, and as a means to control the amount of space allocated to user's objects. We have established that the best way to maintain your database is to create additional tablespaces after the database has been created. The next few sections address the ways that we can create these additional tablespaces. Undo TablespaceUndo tablespaces are Oracle 9i's preferred manner of storing undo segments. Undo tablespaces cannot contain anything other than undo segments, and Oracle alone has direct control over them. When creating an undo tablespace, you can specify only the DATAFILE clause and the EXTENT MANAGEMENT clause.
The CREATE TABLESPACE statement for undo tablespaces follows: CREATE UNDO TABLESPACE mydb1_undo1 DATAFILE '/mydatabases/mydb1/mydb1_undo1.dbf' SIZE 100M; Temporary TablespacesOracle uses temporary tablespaces for sort operations. Like undo tablespaces, temporary tablespaces cannot contain any other type of data (undo tablespaces can't contain anything but undo data; temporary tablespaces cannot contain any permanent objects). Oracle highly recommends that you create temporary tablespaces as locally managed. Sort segments (also known as temporary segments) are used when a segment is shared by multiple sort operations. Temporary tablespaces provide performance improvements when you have multiple sorts going on at one time that are too big to fit easily into memory. The sort segment in the temporary tablespace is created at the time of the first large sort operation in the instance. This segment expands by allocating extents until the segment is equal to or greater than the total storage demands of all the active sorts running in the given instance. The following is an example of the CREATE TABLESPACE statement for temporary tablespaces: CREATE TEMPORARY TABLESPACE mydb1_temp TEMPFILE '/mydatabases/mydb1/mydb1_temp01.dbf' SIZE 1000M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 10M; Temporary tablespaces have several unique features that differ from normal tablespaces. These unique features are as follows:
Default Temporary TablespacesDefault temporary tablespaces allow you, as DBA, to specify a database wide default temporary tablespace. Having a default temporary tablespace eliminates the chances that an errant sort will use the SYSTEM tablespace for storing sort data. You can create the default temporary tablespace either with the CREATE DATABASE command or with the ALTER DATABASE command. When you create it with the CREATE DATABASE command, the default temporary tablespace is always locally managed. If the database has a default temporary tablespace, all users created without the TEMPORARY TABLESPACE clause will have the defined default as their temporary tablespace. Without having a default temporary tablespace, those users would be created having the SYSTEM tablespace assigned as their default temporary tablespace. To set a tablespace as the default temporary tablespace after the database has been created, you can run the following command: ALTER DATABASE DEFAULT TEMPORARY TABLESPACE mydb_temp1; After the tablespace has been made the default temporary tablespace, users who do not have an explicitly assigned temporary tablespace will have that as theirs. The DEFAULT TEMPORARY TABLESPACE can be reassigned by use of the ALTER DATABASE command. After the ALTER DATABASE command has been successfully run, all users who have the DEFAULT TEMPORARY TABLESPACE assigned to them will have the new default assigned to them.
But great as it is to be able to use default temporary tablespaces, there are restrictions on them. Those restrictions are as follows:
|
|