tablespace
The primary types of tablespaces in an Oracle database are permanent, undo, and temporary.
Permanent tablespaces contain segments that persist beyond the duration of a session or a transaction.
Although the undo tablespace may have segments that are retained beyond the end of a session
or a transaction, it provides read consistency for select statements that access tables being modified as well as provides undo data for a number of the flashback features of the database. Primarily,however, undo segments store the previous values of columns being updated or deleted, or toprovide an indication that the row did not exist for an insert so that if a users session fails before the user issues a commit or a rollback, the updates, inserts, and deletes will be removed. Undo segments are never directly accessible by a user session, and undo tablespaces may only have undo segments.
As the name implies, temporary tablespaces contain transient data that exists only for the
duration of the session, such as space to complete a sort operation that will not fit in memory.
Bigfile tablespaces can be used for any of these three types of tablespaces, and they simplify
tablespace management by moving the maintenance point from the datafile to the tablespace.
Bigfile tablespaces consist of one and only one datafile. There are a couple of downsides to bigfile
tablespaces
SYSTEM Tablespace User segments should never reside in the SYSTEM tablespace, period.
As of Oracle 10g, you can specify a default permanent tablespace in addition to the ability to specify a default temporary tablespace in Oracle9i.
SYSAUX Tablespace Like the SYSTEM tablespace, the SYSAUX tablespace should not have any
user segments. The contents of the SYSAUX tablespace, broken down by application, can be
reviewed using EM Database Control. You can edit the SYSAUX tablespace by clicking the
Tablespaces link under the Server tab and clicking the SYSAUX link. Figure 3-1 shows a graphical representation of the space usage within SYSAUX.
Undo Multiple undo tablespaces can exist in a database, but only one undo tablespace can be active at any given time. Undo tablespaces are used for rolling back transactions, for providing read consistency for select statements that run concurrently with DML statements on the same table or set of tables, and for supporting a number of Oracle Flashback features, such as Flashback Query.
Temporary More than one temporary tablespace can be online and active in the database,
10g, multiple sessions by the same user would use the same temporary tablespa
one default temporary tablespace could be assigned to a user. To solve this pote
bottleneck, Oracle supports temporary tablespace groups. A temporary tablespa
synonym for a list of temporary tablespaces.
A temporary tablespace group must consist of at least one temporary tablesp
empty. Once a temporary tablespace group has no members, it no longer exists
Permanent tablespaces contain segments that persist beyond the duration of a session or a transaction.
Although the undo tablespace may have segments that are retained beyond the end of a session
or a transaction, it provides read consistency for select statements that access tables being modified as well as provides undo data for a number of the flashback features of the database. Primarily,however, undo segments store the previous values of columns being updated or deleted, or toprovide an indication that the row did not exist for an insert so that if a users session fails before the user issues a commit or a rollback, the updates, inserts, and deletes will be removed. Undo segments are never directly accessible by a user session, and undo tablespaces may only have undo segments.
As the name implies, temporary tablespaces contain transient data that exists only for the
duration of the session, such as space to complete a sort operation that will not fit in memory.
Bigfile tablespaces can be used for any of these three types of tablespaces, and they simplify
tablespace management by moving the maintenance point from the datafile to the tablespace.
Bigfile tablespaces consist of one and only one datafile. There are a couple of downsides to bigfile
tablespaces
SYSTEM Tablespace User segments should never reside in the SYSTEM tablespace, period.
As of Oracle 10g, you can specify a default permanent tablespace in addition to the ability to specify a default temporary tablespace in Oracle9i.
SYSAUX Tablespace Like the SYSTEM tablespace, the SYSAUX tablespace should not have any
user segments. The contents of the SYSAUX tablespace, broken down by application, can be
reviewed using EM Database Control. You can edit the SYSAUX tablespace by clicking the
Tablespaces link under the Server tab and clicking the SYSAUX link. Figure 3-1 shows a graphical representation of the space usage within SYSAUX.
Undo Multiple undo tablespaces can exist in a database, but only one undo tablespace can be active at any given time. Undo tablespaces are used for rolling back transactions, for providing read consistency for select statements that run concurrently with DML statements on the same table or set of tables, and for supporting a number of Oracle Flashback features, such as Flashback Query.
Temporary More than one temporary tablespace can be online and active in the database,
10g, multiple sessions by the same user would use the same temporary tablespa
one default temporary tablespace could be assigned to a user. To solve this pote
bottleneck, Oracle supports temporary tablespace groups. A temporary tablespa
synonym for a list of temporary tablespaces.
A temporary tablespace group must consist of at least one temporary tablesp
empty. Once a temporary tablespace group has no members, it no longer exists