Oracle Management undo Tablespace

There are two ways to create the undo Talespace:

(1) Create the undo tablespace when the database is created;

(2) created in a database that already exists.

Database objects cannot be created in the undo tablespace because this tablespace is prepared for the recover of the database.

1. Create the undo tablespace when you create the database

You can create the undo tablespace by specifying the UNDO clause when you create the database, but this clause is not required.

If the system specifies auto mode when creating the database, but does not indicate the name of the undo Tablespace, the system creates a default rollback table space, named Sys_undotbs. This table space is created from the default values defined by Oracle. The initialization size is 10m and can be expanded automatically. Oracle recommends, however, that it is best to use a specified size.

CREATE DATABASE RBDB1

Controlfile Reuse

...

UNDO tablespace undotbs_01 datafile '/u01/oracle/rbdb1/undo0101.dbf ';

Note: If the system creation undo fails at this point, the entire command to create the database fails. Now

The DBA needs to delete the data files that have been created, correct the errors, and recreate the database.

Creating using the Create undo tablespace clause

CREATE UNDO tablespace undotbs_02

DataFile '/u01/oracle/rbdb1/undo0201.dbf ' SIZE 2M reuse

Autoextend on;

2, Undo tablespace related operations

(1) Adding data files

ALTER tablespace undotbs_01

ADD datafile '/u01/oracle/rbdb1/undo0102.dbf ' autoextend on NEXT 1M MAXSIZE Unlimited;

(2) Renaming data files

ALTER tablespace undotbs_01 RENAME datafile '/u01/oracle/rbdb1/undo0102.dbf ' to '/u01/oracle/rbdb1/undo0101.dbf ';

(3) Make the data file online or offline

ALTER tablespace undotbs_01 Online|offline;

(4) Start or end an online backup

ALTER tablespace undotbs_01 begin| End BACKUP;

(5) Delete undo Tablespace

Drop tablespace undotbs_01;

The Drop Undo table space must be in the unused condition. If the Undo table space is in use (for example, a transaction fails, but has not recovered successfully), the drop table Space command will fail. Including contents can be used when the drop table space is available.

(6) Toggle Undo Tablespace

There are two ways to switch the undo table space:

A, use the command dynamic modification;

b, after modifying the initialization parameters, restart the database.

Alter system set UNDO_TABLESPACE=UNDOTBS1;

When the toggle command completes, all transactions are made in the new rollback table space.

The following conditions can cause the switch command to fail:

A, the table space does not exist;

b, the table space is not a rollback segment table space;

C, table space has been used by another instance.

Note: The toggle operation does not wait for the transaction commit of the old undo table space. If the old undo table space has a transaction uncommitted, the old undo Tablespace enters the pending offline state where all transactions can continue, but the undo table space cannot be used by other instances and cannot be deleted until all transactions have been committed. The Undo table space only enters offline mode.

(7) Set undo_retention

The DBA can set the Undo_retention initialization parameter to specify when the undo rollback table Space retains the undo information. When this parameter is set, the system retains the undo information after the specified time to retract the space.

Generally, the system retains the undo information to the specified time before reclaiming space, but if the system

There are a large number of transactions that will also reclaim the expired undo space for use.

(8) Calculation formula of design specification for Undo table space size

Undospace = UR * UPS *db_block_size+ redundancy

UR: Represents the maximum number of seconds to hold in undo, determined by the database parameter Undo_retention value.

UPS: Represents the number of database blocks generated per second in undo.

The dynamic performance view associated with Undo V$undostat contains statistics about undo. Use this view to estimate the current desired undo size for the system.

V$rollstat is the view of the undo mode. Is the undo segments statistic for the undo table space

V$transaction contains information about the undo segments.

Dba_undo_extents contains the commit time for each range in the Undo table space.

See more highlights of this column: http://www.bianceng.cnhttp://www.bianceng.cn/database/Oracle/