Any user DML that attempts to make changes
in non-SYSTEM tablespaces will, in addition, receive the error message ???ORA-01552: cannot use
system rollback segment for non-system tablespace ???USERS,??™??? and the statement fails.
UNDO_RETENTION
UNDO_RETENTION specifies a minimum amount of time that undo information is retained for
queries. In automatic undo mode, UNDO_RETENTION defaults to 900 seconds. This value is
valid only if there is enough space in the undo tablespace to support read-consistent queries; if
active transactions require additional undo space, an unexpired undo may be used to satisfy the
active transactions and may cause ???ORA-01555: Snapshot Too Old??? errors.
The column TUNED_UNDORETENTION of the dynamic performance view V$UNDOSTAT
gives the tuned undo retention time for each time period; the status of the undo tablespace usage
is updated in V$UNDOSTAT every ten minutes:
SQL> show parameter undo_retention
NAME TYPE VALUE
------------------------------------ ----------- ---------------
undo_retention integer 900
SQL> select to_char(begin_time,'yyyy-mm-dd hh24:mi'),
2 undoblks, txncount, tuned_undoretention
3 from v$undostat where rownum = 1;
TO_CHAR(BEGIN_TI UNDOBLKS TXNCOUNT TUNED_UNDORETENTION
---------------- ---------- ---------- -------------------
2007-08-05 16:07 9 89 900
1 row selected.
SQL>
Because the transaction load is very light during the most recent time period, and the instance
has just recently started up, the tuned undo retention value is the same as the minimum specified
in the UNDO_RETENTION initialization parameter: 900 seconds (15 minutes).
Pages:
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375