status
2 from v$rollstat r join v$transaction t on r.usn=t.xidusn
3 join v$session s on t.ses_addr = s.saddr
4 where s.username = 'HR';
STATUS
---------------
PENDING OFFLINE
1 row selected.
Even though the current undo tablespace is UNDO_BATCH, the daytime tablespace
UNDOTBS1 cannot be taken offline or dropped until HR??™s transaction is committed or
rolled back:
SQL> show parameter undo_tablespace
NAME TYPE VALUE
-------------------------- ----------- ----------------------
undo_tablespace string UNDO_BATCH
SQL> alter tablespace undotbs1 offline;
alter tablespace undotbs1 offline
*
ERROR at line 1:
ORA-30042: Cannot offline the undo tablespace
The error message ORA-30042 applies if you try to offline an undo tablespace that is in use??”
either it is the current undo tablespace or it still has pending transactions. Note that if we switch
back to the daytime tablespace before HR commits or rolls back the original transaction, the status
of HR??™s rollback segment reverts back to ONLINE:
SQL> alter system set undo_tablespace=undotbs1;
System altered.
SQL> select r.status
2 from v$rollstat r join v$transaction t on r.usn=t.xidusn
3 join v$session s on t.ses_addr = s.saddr
4 where s.username = 'HR';
220 Oracle Database 11g DBA Handbook
STATUS
---------------
ONLINE
1 row selected.
Sizing and Monitoring the Undo Tablespace
There are three types of undo data in the undo tablespace: active or unexpired, expired, and
unused.
Pages:
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378