 |
|
Oracle Tips by Burleson |
Addition of Rollback
Segments
Another database structure is the ROLLBACK
segment. Under pre-9i versions and in 9i with manually managed undo,
ROLLBACK segments can be placed in any tablespace, but it is
suggested that they be placed in a tablespace that contains only
other rollback segments. This makes administration easier. Rollback
segments can be PUBLIC, which means that for a multi-instance
database, any instance can use the rollback segment, or PRIVATE,
which means only the instance that has the rollback segments named
in the ROLLBACK SEGMENTS clause of its INIT.ORA file can use the
rollback segment.
Under Oracle9i, an UNDO tablespace can be
created (is created by default) and should be used to allow Oracle
to internally manage UNDO (read rollback) operations. The UNDO
tablespace is usually created with the UNDO TABLESPACE clause of the
CREATE DATABASE command or can be added after database creation with
the CREATE UNDO TABLESPACE command and addition of the required
initialization parameters.
Managing the UNDO Tablespace
There are several initialization parameters
that control the UNDO tablespace feature of Oracle9i. They are:
UNDO_MANAGEMENT. Can be set to AUTO or
MANUAL. In AUTO, the database manages undo operations; under MANUAL,
the DBA manages undo operations. The default value is MANUAL, and
any UNDO_TABLESPACE specification is ignored. If set to MANUAL,
management of rollback segments is identical to pre-9i rollback
segment management.
UNDO_TABLESPACE. A dynamic parameter that
specifies which tablespace to use as an UNDO tablespace.
UNDO_RETENTION. A dynamic parameter that
specifies the length of time to retain undo information for use in
flashback queries; the default is 900 seconds.
UNDO_SUPPRESS_ERRORS. Either TRUE or FALSE.
Used to suppress error messages if manual undo management SQL
commands are entered in auto undo management mode.
Creation of the UNDO Tablespace
As stated above, the UNDO tablespace can be
created either when the database is created, using the UNDO
TABLESPACE clause of the CREATE DATABASE command, or it an be added
later, using the CREATE UNDO TABLESPACE command and the appropriate
initialization parameters set for it. An example UNDO tablespace
creation command is shown here:
CREATE UNDO TABLESPACE undo_tbs1 DATAFILE
'/oracle3/oradata/galinux1/data/undo_tbs2.dbf' SIZE 200M AUTOEXTEND
ON;
Oracle determines the other attributes of
the undo tablespace automatically; you only need to specify the
datafile clause and size information. The Oracle system will
automatically assign an UNDO tablespace with locally managed,
auto-allocated extents; the maximum size will be set at
approximately one and a half times the specified size. You can
specify the autoextend parameters and the extent management
parameters in the CREATE UNDO command, but options such as LOGGING
will cause an error.
Altering an UNDO Tablespace
An UNDO tablespace can only be altered for
the following items:
* Adding a datafile.
* Renaming a datafile.
* Bringing a datafile on- or offline.
* Beginning or ending backup.
* Resizing a datafile.
Dropping an UNDO Tablespace
The UNDO tablespace(s) are dropped using the
DROP TABLESPACE command. The UNDO tablespace to be dropped cannot
have any currently active transactions.
See Code Depot
www.oracle-script.com |
