oracle_基础八(Undo)

Auto Undo Mgmt: Concepts

1.Undo data is managed using an UNDO tablespace.

2.You allocate one UNDO tablespace per instance with enough space for the workload of the instance.

3.The Oracle server automatically maintains undo data within the UNDO tablespace

Auto undo Mgmt: config

1.configure two parameters in the initailization file:

   a.UNDO_MANAGEMENT  //推荐设置为auto

   b.UNDO_TABLESPACE

2.Create at least one UNDO tablespace

AUM: Init Parameters

1.UNDO_MANAGEMENT: Specifies whether the system should use AUTO or MANUAL  mode

 2.UNDO_TABLESPACE:   specifies a particular UNDO TABLEspace to be used

UNDO_MANAEMENT=AUTOUNDO_TABLESPACE=UNDOTBS

CREATE DATABASE db01 ...UNDO_TABLESPACE undo1DATAFILE '/u01/....undodb01.dbf' SIZE 40M AUTOEXTEND ONCREATE UNDO TABLESPACE undo1 DATAFILE  '/u01/.../undodb01.dbf' SIZE 20M

</pre><pre class="sql" name="code">ALTER TABLESPACE undotbs ADD DATAFILE '/u01/oradata/undotbs2.dbf' SIZE 30M AUTOEXTEND ON

AUM: Switching UNDO TS

1.you can switch form using one UNDO tablespace to another.

2.Only one UNDO tablespace can be in assigned to a database at a time

3. More than one UNDO tablespace may exist within an instance , but only one can be active

4. Use the ALTER SYSTEM command for dynamic switching between UNDO tablespaces

ALTER SYSTEM SET UNDO_TABLESPACE=UNDOTBS

AUM: Dropping an UNDO TS

DROP TABLESPACE UNDOTBS2

1.An UNDO tablespace can only be dropped if it is currently not in use by any instance.

2.To drop an active UNDO tablespace:

   a.Switch to new UNDO tablespace

   b. Drop the tablespace after all current transactions are complete.

UNDO_RETENTION :This parameter controls the amount of undo data to retain for consistent read.

v$transaction  其中　USED_UBLK 说明该事务使用的undo 数据块

可以根据这个表来算每秒中需要多少undo空间，　然后综合UNDO_RETENTION就是需要的undo表空间

select max(undoblks/((end_time-begin_time)*24*3600)) from v$undostat;   一般用这种

select sum(undoblks) /sum((end_time-begin_time)*24*3600) from v$undostat

show parameter undo_retention ;

show parameter db_block_size

AUM:Sizing an UNDO TS

Determing a size for theUNDO tablespace requires three pieces of information:

1.(UR) UNDO_RETENTION in seconds

2.(UPS) Number of undo data blocks generated per second

3.(DBS) Overhead varies based on extent and file size (db_block_size)

AUM: Altering an UNDO TS

1.The ALTER TABLESPACE command can make changes to UNDO tablespaces

2. The following example adds another data file to the UNDO tablespace:

ALTER TABLESPACE undotbs ADD DATAFILE '/u01/oradata/undotbs2.dbf' SIZE 30M AUTOEXTEND ON;

AUM:Undo Quota

1.Long transactions and improperly written transactions can consume valuable resources.

2.With undo quota, users can be grouped and a maximum undo space limit can be assigned to the group

3. UNDO_POOL a Resource Manager directive, defines the amount of space allowed for a resource group.

4.When a group excessds its limit , no new transactions are possible for the group , until undo space is freed by current transactions which are either completing or aborting

Get Undo Segment Info

1.Information about undo segments can be obtained by querying the following views:

    a.DBA_ROLLBACK_SEGS　　//所有的segment

2.Dynamic Performance Views

   V$ROLLNAME //在线的segment信息

   V$ROLLSTAT //在线的segment统计信息

   V$UNDOSTAT

   V$SESSION

   V$TRANSACTION

select segment_name, tablespace_name from dba_rollback_segs;