NOTE
Other than special circumstances described in this section, it is
unlikely that you will be switching undo tablespaces for a given
instance. Oracle??™s best practices suggest that you create a single undo
tablespace per instance that is large enough to handle all transaction
loads; in other words, ???set it and forget it.???
About the time the undo tablespace is going to be switched, the user HR is performing some
maintenance operations on the HR.EMPLOYEES table, and she has an active transaction in the
current undo tablespace:
SQL> connect hr/hr@dw;
Connected.
SQL> set transaction name 'Employee Maintenance';
Transaction set.
SQL> update employees set commission_pct = commission_pct * 1.1;
107 rows updated.
SQL>
Checking V$TRANSACTION, you see HR??™s uncommitted transaction:
SQL> select t.status, t.start_time, t.name
2 from v$transaction t join v$session s on t.ses_addr = s.saddr
3 where s.username = 'HR';
Chapter 7: Managing Transactions with Undo Tablespaces 219
STATUS START_TIME NAME
-------------- -------------------- -------------------------
ACTIVE 08/05/07 17:41:50 Employee Maintenance
1 row selected.
You change the undo tablespace as follows:
SQL> alter system set undo_tablespace=undo_batch;
System altered.
HR??™s transaction is still active, and therefore the old undo tablespace still contains the undo
information for HR??™s transaction, leaving the undo segment still available with the following status
until the transaction is committed or rolled back:
SQL> select r.
Pages:
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377