To change undo
management from rollback segments to automatic undo management (changing the value of
UNDO_MANAGEMENT from MANUAL to AUTO), the instance must be shut down and restarted
for the change to take effect; you can change the value of UNDO_TABLESPACE while the database
is open. The UNDO_RETENTION parameter specifies, in seconds, the minimum amount of time
that undo information should be retained for Flashback queries. However, with an undersized
undo tablespace and heavy DML usage, some undo information may be overwritten before the
time period specified in UNDO_RETENTION.
New to Oracle 10g is the RETENTION GUARANTEE clause of the CREATE UNDO TABLESPACE
command. In essence, an undo tablespace with a RETENTION GUARANTEE will not overwrite
unexpired undo information at the expense of failed DML operations when there is not enough free
undo space in the undo tablespace. More details on using this clause can be found in Chapter 7.
The following initialization parameters enable automatic undo management with the undo
tablespace UNDO04 using a retention period of at least 24 hours:
undo_management = auto
undo_tablespace = undo04
undo_retention = 86400
The dynamic performance view V$UNDOSTAT can assist in sizing the undo tablespace
correctly for the transaction load during peak processing periods. The rows in V$UNDOSTAT
are inserted at ten-minute intervals and give a snapshot of the undo tablespace usage:
SQL> select to_char(end_time,'yyyy-mm-dd hh24:mi') end_time,
2 undoblks, ssolderrcnt from v$undostat;
182 Oracle Database 11g DBA Handbook
END_TIME UNDOBLKS SSOLDERRCNT
------------------ -------- -----------
2007-07-23 10:28 522 0
2007-07-23 10:21 1770 0
2007-07-23 10:11 857 0
2007-07-23 10:01 1605 0
2007-07-23 09:51 2864 3
2007-07-23 09:41 783 0
2007-07-23 09:31 1543 0
2007-07-23 09:21 1789 0
2007-07-23 09:11 890 0
2007-07-23 09:01 1491 0
In this example, a peak in undo space usage occurred between 9:41 A.
Pages:
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333