* There can be more than one UNDO tablespaces in 9i. * But only one can be active at a time. UNDO_MANAGEMENT: static AUTO - ORACLE manage rollback segments automatically for you. You can not create rollback segment or assign rollback segments to sessions. MANUAL - This is how it works prior to 9i. UNDO_TABLESPACE: dynamic Which UNDO tbs to use at startup. If none is available, rollback segment in the system tablespace is used. (From ORACLE DOC) The only clauses you can specify for an undo tablespace are the DATAFILE clause and the extent_management_clause to specify local extent management. All undo tablespaces are created permanent, read/write, and in logging mode. (From OEM) When creating an UNDO tbs, the only thing you have control of is the DATAFILE clause. UNDO_SUPPRESS_ERRORS: dynamic A temporary method for you to move to automatic undo managemnet. Supresses errors when "set transaction use rollback segment" is encountered. The statement is meaningless, but you get no errors and programs continue to run. In the following example, the rollback segment 'RSBIG' does not even exist. UNDO_RETENTION: dynamic Specifies (in seconds) the amount of committed undo information to retain in the database. -- UNDO_RETENTION and the infamous ORA-01555 ORACLE tries to honor this retention period when there is enough space in the UNDO tablespace to go around. If retention period has expired or there is not enough space in the UNDO tbs, and a transaction needs undo space, the UNDO segment will be overwritten. Long runnning quries that need access to the segment will fail with ORA-01555. So, how is this different from pre-9i release? In 8i you create more, larger rollback segments. In 9i, you set higher UNDO_RETENTION, all the while making sure you have enough space in the UNDO tbs. Flash-back query is the other subject that UNDO_RETENTION has direct impact on. You set UNDO_RETENTION depends on how far back you want the version of you data to be available. SQL> show parameters undo NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ undo_management string AUTO undo_retention integer 10800 undo_suppress_errors boolean FALSE undo_tablespace string UNDOTBS1 SQL> SQL> exec DBMS_TRANSACTION.USE_ROLLBACK_SEGMENT('RSBIG'); BEGIN DBMS_TRANSACTION.USE_ROLLBACK_SEGMENT('RSBIG'); END; * ERROR at line 1: ORA-30019: Illegal rollback Segment operation in Automatic Undo mode ORA-06512: at "SYS.DBMS_TRANSACTION", line 65 ORA-06512: at line 1 SQL> alter session set undo_suppress_errors=true; Session altered. SQL> exec DBMS_TRANSACTION.USE_ROLLBACK_SEGMENT('RSBIG'); PL/SQL procedure successfully completed. SQL> select SEGMENT_NAME,EXTENT_ID,STATUS from DBA_UNDO_EXTENTS; SEGMENT_NAME EXTENT_ID STATUS ------------------------------ ---------- --------- _SYSSMU10$ 0 EXPIRED _SYSSMU10$ 1 EXPIRED _SYSSMU10$ 2 UNEXPIRED _SYSSMU10$ 3 EXPIRED _SYSSMU9$ 0 UNEXPIRED _SYSSMU9$ 1 UNEXPIRED _SYSSMU9$ 2 EXPIRED _SYSSMU9$ 3 EXPIRED _SYSSMU8$ 0 EXPIRED _SYSSMU8$ 1 EXPIRED _SYSSMU8$ 2 UNEXPIRED _SYSSMU8$ 3 EXPIRED _SYSSMU7$ 0 UNEXPIRED _SYSSMU7$ 1 UNEXPIRED _SYSSMU7$ 2 EXPIRED _SYSSMU7$ 3 UNEXPIRED _SYSSMU6$ 0 UNEXPIRED _SYSSMU6$ 1 UNEXPIRED _SYSSMU6$ 2 EXPIRED _SYSSMU6$ 3 UNEXPIRED _SYSSMU5$ 0 UNEXPIRED _SYSSMU5$ 1 UNEXPIRED _SYSSMU5$ 2 EXPIRED _SYSSMU5$ 3 UNEXPIRED _SYSSMU4$ 0 EXPIRED _SYSSMU4$ 1 EXPIRED _SYSSMU4$ 2 UNEXPIRED _SYSSMU4$ 3 EXPIRED _SYSSMU3$ 0 UNEXPIRED _SYSSMU3$ 1 UNEXPIRED _SYSSMU3$ 2 EXPIRED _SYSSMU3$ 3 UNEXPIRED _SYSSMU2$ 0 EXPIRED _SYSSMU2$ 1 EXPIRED _SYSSMU2$ 2 UNEXPIRED _SYSSMU2$ 3 EXPIRED _SYSSMU1$ 0 EXPIRED _SYSSMU1$ 1 EXPIRED _SYSSMU1$ 2 UNEXPIRED _SYSSMU1$ 3 EXPIRED 40 rows selected.