《Managing Undo Data》由会员分享，可在线阅读，更多相关《Managing Undo Data（64页珍藏版）》请在金锄头文库上搜索。
1、Managing Undo Data,Objectives,After completing this lesson, you should be able to do the following:Describe the purpose of undo dataImplement Automatic Undo ManagementCreate and configure undo segmentsObtain undo segment information from the data dictionary,Managing Undo Data Overview,Two methods fo
2、r managing undo data existsAutomatic Undo ManagementManual Undo ManagementThis lesson discusses Automatic Undo ManagementThe term undo replaces what was known in previous versions of Oracle as rollback,Undo Segment,Update transaction,Old image,New image,Undo segment,Table,Undo Segments: Purpose,Tran
3、saction rollback,Transaction recovery,Undo segment,Read consistency,Read Consistency,Image at start of statement,New image,Table,SELECT * FROM table,Types of Undo Segments,SYSTEM: Used for objects in the SYSTEM tablespaceNon-SYSTEM: Used for objects in other tablespaces:Auto Mode: Requires an UNDO t
4、ablespaceManual Mode:Private: Acquired by a single instancePublic: Acquired by any instanceDeferred: Used when tablespaces are taken offline immediate, temporary, or for recovery,Automatic Undo Management:Concepts,Undo data is managed using an UNDO tablespaceYou allocate one UNDO tablespace per inst
5、ance with enough space for the workload of the instance.The Oracle server automatically maintains undo data within the UNDO tablespace.,Automatic Undo Management:Configuration,Configure two parameters in the initialization file:UNDO_MANAGEMENTUNDO_TABLESPACECreate at least one UNDO tablespace.,Autom
6、atic Undo Management:Initialization Parameters,UNDO_MANAGEMENT: Specifies whether the system should use AUTO or MANUAL mode. UNDO_TABLESPACE: Specifies a particular UNDO tablespace to be used.,UNDO_MANAGEMENT=AUTOUNDO_TABLESPACE=UNDOTBS,Automatic Undo Management:UNDO Tablespace,You may create the UN
7、DO tablespace with the database by adding a clause in the CREATE DATABASE commandOr create it later by using the CREATE UNDO TABLESPACE command,CREATE DATABASE db01. . .UNDO TABLESPACE undo1 DATAFILE undo1db01.dbfSIZE 20M AUTOEXTEND ON,CREATE UNDO TABLESPACE undo1DATAFILE undo1db01.dbf SIZE 20M;,Aut
8、omatic Undo Management:Altering an UNDO Tablespace,The ALTER TABLESPACE command can make changes to UNDO tablespaces.The following example adds another data file to the UNDO tablespace:,ALTER TABLESPACE undotbsADD DATAFILE undotbs2.dbf SIZE 30MAUTOEXTEND ON;,Automatic Undo Management:Switching UNDO
9、Tablespaces,You may switch from using one UNDO tablespace to anotherOnly one UNDO tablespace can be in assigned to an instance at a timeMore than one UNDO tablespace may exist within an instance, but only one can be activeUse the ALTER SYSTEM command for dynamic switching between UNDO tablespaces,AL
10、TER SYSTEM SET UNDO_TABLESPACE=UNDOTBS2;,Automatic Undo Management:Dropping an UNDO Tablespace,The DROP TABLESPACE command drops an UNDO tablespace.An UNDO tablespace can only be dropped if it is currently not in use by any instance.To drop an active UNDO tablespace:Switch to a new UNDO tablespaceDr
11、op the tablespace after all current transactions are complete,DROP TABLESPACE UNDOTBS2;,Automatic Undo Management:Other Parameters,UNDO_SUPPRESS_ERRORSSet to TRUE, this parameter suppresses errors while attempting to execute manual operations in AUTO mode.UNDO_RETENTIONControls the amount of undo da
12、ta to retain for consistent read,Undo Data Statistics,SELECT end_time,begin_time,undoblksFROM v$undostat;END_TIME BEGIN_TIME UNDO- - -22-JAN-01 13:44:18 22-JAN-01 13:43:04 1922-JAN-01 13:43:04 22-JAN-01 13:33:04 147422-JAN-01 13:33:04 22-JAN-01 13:23:04 134722-JAN-01 13:23:04 22-JAN-01 13:13:04 1628
13、22-JAN-01 13:13:04 22-JAN-01 13:03:04 224922-JAN-01 13:03:04 22-JAN-01 12:53:04 169822-JAN-01 12:53:04 22-JAN-01 12:43:04 143322-JAN-01 12:43:04 22-JAN-01 12:33:04 153222-JAN-01 12:33:04 22-JAN-01 12:23:04 1075,Automatic Undo Management:Sizing an UNDO Tablespace,Determining a size for the UNDO table
14、space requires three pieces of information(UR) UNDO_RETENTION in seconds(UPS) Number of undo data blocks generated per second(DBS) Overhead varies based on extent and file size (db_block_size),UndoSpace = UR * (UPS * DBS) + (DBS * 24),Automatic Undo ManagementUndo Quota,Long transactions and imprope
15、rly written transactions can consume valuable resourcesWith undo quota users can be grouped and a maximum undo space limit can be assigned to the groupUNDO_POOL, a Resource Manager directive, defines the amount of space allowed for a resource groupWhen a group exceeds its limit no new transactions a
16、re possible, for the group, until undo space is freed by current transactions either completing or aborting,Obtaining Undo Segments Information,Data Dictionary ViewsDBA_ROLLBACK_SEGSDynamic Performance ViewsV$ROLLNAMEV$ROLLSTATV$UNDOSTATV$SESSIONV$TRANSACTION,Summary,In this lesson, you should have learned how to:Configure Automatic Undo ManagementCreate an Undo TablespaceProperly size an Undo Tablespace,
