/ * Undo Table Space * /
By using undo technology, you can provide the following features for the Oracle database: * Use the ROLLBACK statement to undo transaction * Database recovery * Provide data read-consistency
Oracle strongly recommends that DBA applies automatic revocation management in the Oracle 9i database, any database users cannot create objects in the revocation table space, and only save the revocation record in the revocation table space.
SQL> Show Parameter Undo
Name Type Value --------------------------------- ------------------------------ UNDO_MANAGEMENT STRING AutOUndo_retention integer 10800undo_suppress_errors boolean falseundo_tablespace string undotbs1
UNDO_MANAGEMENT - Auto Automatic Undo Management Mode (Undo Table Space Method) - Manual Handmade Undo Management Mode (Reflex Betting Mode) UNDO_RETENTION - Undo Record Save the longest time
Create and manage the revocation table space 1. Establish cancellation table space when creating a database, Create Database Database DB01 ... Undo TableSpace undotbs01 DataFile 'i: /oacle/oradata/undotbs0101.dbf' ....
2. Create a revocation table space after the database is established, Creotbs02 DataFile 'i: /oracle/oradata/undotbs0201.dbf' AutoExtend ON; (It is recommended to set the data file growth in the tablespace to automatic growth mode)
Modifying the revocation tablespace users can only perform the following operations * Add new data file * Rename data file * Change data file size * Switch data file to online or offline status
Alter TableSpace Undotbs01 Add DataFile 'i: /oracle/oradata/undotbs0102.dbf' Autoextend On Next 1M MaxSize Unlimited
Alter Database DataFile 'i: /oracle/oradata/undotbs0101.dbf' Resize 500M;
Delete the revocation table space (only if the cancellation table space is not used in use, if the revocation table space contains the revocation record of the pending transaction, the table space cannot be removed) Drop TableSpace UnTotbs01; Drop TableSpace UnTotbs01 include Contents and DataFiles;
Switching revocation table space (undo_tablespace is a dynamic initialization parameter) ALTER system set undo_tableSpace = undotbs02;
If there is the following case, the above switching operation will generate an error: * The specified undo tablespace does not exist * The specified table space is not an undo tablespace * The specified table space is being used by other instances.
After successful switching of the table space, any new starting transaction will save the revocation record in the new undo tablespace, but there may be currently not submitted revocation records in the old revocation table space.
UNDO_RETENTION parameter: Specify the retention time of revocation record, in seconds, is a dynamic parameter such as: set the retention time to 30 minutes UNDO_RETENTION = 1800 ALTER SYSTEM SET undo_retention = 600; Note: Time to revoke records in the revocation table space Not necessarily greater than the undo_retrion parameter specified time, if the revocation table space has been written, the new transaction will overwrite the revocation record that has been submitted, even if these undo records have not expired. Therefore, if you are undo_retention The parameter sets a large value, then ensure that the revocation table space has sufficient storage space.
When Oracle does not use the revocation (rollback) record to ensure the reading consistency of the data, "Snapshot is too old" error will occur. This error typically occurs when performing a query for a long run time, due to the related query The revocation record has been overwritten, so Oracle does not provide the correct read consistency view for the query.
Suppose there is such a situation: User Scott updates to the EMP table, but he has not submitted the update transaction, and the data before the update in the EMP table is saved in the revocation record of the Scott transaction. At this time, if there is another user Jake Start accessing the EMP table, Oracle will utilize these undo records to provide read-consistency views in the EMP table. Assume that Jake's query will last for a long time, Oracle will continue to use these undo records. In this process, the Scott user can submit his transaction. After the transaction is submitted, the revocation record of the Scott transaction in the revocation table space (or return segment) is marked as invalid, which may be revoked by other transactions. Override, or is released when the Oracle reduction is returned. At this time, if the Jake user's query is not over, it may generate a "snapshot too old" error.
Workaround: * In manual cancellation management mode, DBA must specify a larger return segment (with larger Mineltents and Optimal parameter values). * In automatic revocation management Next, DBA only needs to specify a larger undo_retrion parameter value and guarantees that the revocation tablespace has sufficient storage space.
Undo Table Space Data Dictionary V $ undostat contains statistics for all revocation table spaces for monitoring and adjusting the revocation table space, and DBA can estimate this view to estimate the size V $ rollstat of the revocation table space in automatic undo management mode. This view can be taken to query information about each revocation segment of the revocation table space V $ Transaction information about the revocation segments used by each business.
/ * Management Return Section * / If you build multiple back to the database, Oracle will assign average transaction to each return segment, which can significantly improve systemically rebound processing performance.
Create a return section CREATE ROLLBACK Segment RBS_02 TableSpace RBSTBSStorage (Initial 100K Next 100k Optimal 5M Pctincrease 0 Mineltents 20 MaxExtents 100); (Pctincrease Store Parameters To 0, set the initial and next parameters to the same value, so that all The area should have the same size, the value of the Optimal parameter cannot be less than INITIAL NEXT * (MINEXTENTS-1), and if not set, Oracle will use null as the default value, set a larger value for the MINEXTENTS parameter, don't Set the maxExtents parameter to unlimited to avoid excessive storage spaces allocated due to an abnormal transaction.
New Retreat Section If you want to use it as a private retreat section, you can add its name to the initialization parameter rollback_segments rollback_segments = (RBS_01, RBS_02) Change the storage parameters of the return segment Alter Rollback Segment RBS_02Storage (MaxExtents 120);
Alter Rollback Segment RBS_02Storage (Optimal 8M);
Reduced Refill If the Optimal Storage Parameters are specified by setting the Optimal storage parameter, Oracle will return to the return segment in the retraction section exceeding the optimal size, and the DBA can also be handmade and retreat. The reduction operation of the segment only performs the optimal size of the return segment RBS_02 when there is no active transaction, 5malter rollback segment rbs_02 shrink to 5m
Display Assignment Reflection SET Transaction Use Rollback Segment Large_BS01 (SET Transaction Statement must be the first execution statement of the transaction)
Delete a refund (must be taken off first, if the return segment is in any nonfline state, you can't perform the delete operation, you must also delete it in the rollback_segment parameter) Drop Rollback Segment RBS_02
Refill Section Data Dictionary: DBA_ROLLBACK_SEGS All Rebate Section Information V $ ROLLNAME All Online Refill Section Name
One of the important works of the management and return segment is to set a reasonable Optimal parameter value, and the DBA can first query the V $ ROLLSTAT view to get the Aveactive field value of the refund, and then set the value of Optimal to average the aveactive value. .