Automatic Undo Management
Traditionally transaction undo information was stored in Rollback Segments until a commit or rollback statement was issued, at which point it was purged. In Oracle 9i this method of manual undo management is still available in addition to a new automatic method which frees DBAs from routine undo management tasks and tuning. In addition it allows the DBA to specify how long undo information should be retained after commit, preventing "snapshot too old" errors on long running queries in addition to supporting Oracle flashback query.
It is not possible to use both methods in a single instance simultaneous, but the method can be switched, a procedure which requires the instance to be bounced.
Undo Tablespace Creation
Automatic undo management requires a locally managed undo tablespace to store undo segments in. Undo tablespaces can be created
during database creation or using the
CREATE UNDO TABLESPACE statement.
-- As part of database creation CREATE DATABASE rbdb1 CONTROLFILE REUSE ... UNDO TABLESPACE undotbs_01 DATAFILE 'C:\Oracle\Ordata\TSH1\undo0101.dbf' SIZE 100M REUSE AUTOEXTEND ON; -- Using the create undo tablespace statement CREATE UNDO TABLESPACE undotbs_02 DATAFILE 'C:\Oracle\Ordata\TSH1\undo0201.dbf' SIZE 100M REUSE AUTOEXTEND ON;
If undo_management is set to auto and no undo_tablespace is defined Oracle will create one during the database creation.
Enabling Automatic Undo Management
Since the default undo management mode is
MANUAL, the instance must be told to use
AUTO mode at instance startup. To do this the following initialization parameters can be set.
UNDO_MANAGEMENT = AUTO # Default is MANUAL UNDO_TABLESPACE = undotbs_01 # The name of the undo tablespace. UNDO_RETENTION = 900 # The time undo is retained. # Default is 900 seconds. UNDO_SUPPRESS_ERRORS = TRUE # Suppress errors when MANUAL undo admin # SQL statements are issued.
Several of these parameters can be altered while the instance is up, but the
UNDO_MANAGEMENT parameter is static.
-- Dynamic Parameters. ALTER SYSTEM SET UNDO_TABLESPACE=UNDOTBS_02; ALTER SYSTEM SET UNDO_RETENTION=5; ALTER SYSTEM SET UNDO_SUPPRESS_ERRORS=FALSE; -- Static Parameters. ALTER SYSTEM SET UNDO_MANAGEMENT=AUTO SCOPE=SPFILE;
Maintenance of undo tablespaces is similar to that of regular tablespaces, although some options are not neccessary as Oracle takes over the burden of most management tasks.
-- Add a datafile. ALTER TABLESPACE undotbs_01 ADD DATAFILE 'C:\Oracle\Ordata\TSH1\undo0102.dbf' AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED; -- Resize an undo datafile. ALTER DATABASE DATAFILE 'C:\Oracle\Ordata\TSH1\undo0102.dbf' RESIZE 10M; -- Perform backup operations ALTER TABLESPACE undotbs_01 BEGIN BACKUP; ALTER TABLESPACE undotbs_01 END BACKUP; -- Drop an undo tablespace. DROP TABLESPACE undotbs_01;
In the last example the tablespace will only be dropped if it is not currently being used or contains undo information for a current transaction. It will however drop tablespaces where the undo information has not yet expired, thus affecting long running queries and flashback query.
Undo information can be queried using the following views.
By default all users have unlimited access to undo tablespaces, although this can be limited using the Resource Manager directive UNDO_POOL.
For more information see:
Hope this helps. Regards Tim...