SQL> create undo tablespace undotbs2 datafile
2 'D:\oracle\oradata\test\undotbs02.dbf' SIZE 400m autoextend off;
Tablespace created.
SQL> show parameter undo
NAME TYPE VALUE
------------------------------ ----------- ---------------------
undo_management string AUTO
undo_retention integer 10800
undo_suppress_errors boolean FALSE
undo_tablespace string UNDOTBS1
SQL> alter system set undo_tablespace=UNDOTBS2 scope=both;
System altered.
SQL> show parameter undo
NAME TYPE VALUE
------------------------------ ----------- ---------------------
undo_management string AUTO
undo_retention integer 10800
undo_suppress_errors boolean FALSE
undo_tablespace string UNDOTBS2
SQL> select segment_name ,tablespace_name ,segment_id from dba_rollback_segs;
SEGMENT_NAME TABLESPACE_NAME SEGMENT_ID
------------------------------ ------------------------------ ----------
SYSTEM SYSTEM 0
_SYSSMU1$ UNDOTBS1 1
_SYSSMU2$ UNDOTBS1 2
_SYSSMU3$ UNDOTBS1 3
_SYSSMU4$ UNDOTBS1 4
_SYSSMU5$ UNDOTBS1 5
_SYSSMU6$ UNDOTBS1 6
_SYSSMU7$ UNDOTBS1 7
_SYSSMU8$ UNDOTBS1 8
_SYSSMU9$ UNDOTBS1 9
_SYSSMU10$ UNDOTBS1 10
SEGMENT_NAME TABLESPACE_NAME SEGMENT_ID
------------------------------ ------------------------------ ----------
_SYSSMU11$ UNDOTBS2 11
_SYSSMU12$ UNDOTBS2 12
_SYSSMU13$ UNDOTBS2 13
_SYSSMU14$ UNDOTBS2 14
_SYSSMU15$ UNDOTBS2 15
_SYSSMU16$ UNDOTBS2 16
_SYSSMU17$ UNDOTBS2 17
_SYSSMU18$ UNDOTBS2 18
_SYSSMU19$ UNDOTBS2 19
_SYSSMU20$ UNDOTBS2 20
21 rows selected.
SQL> select usn,status,xacts from v$rollstat;
USN STATUS XACTS
---------- --------------- ----------
0 ONLINE 0
10 PENDING OFFLINE 1 -- XACTS ： Number of active transactions
11 ONLINE 0
12 ONLINE 0
13 ONLINE 0
14 ONLINE 0
15 ONLINE 0
16 ONLINE 0
17 ONLINE 0
18 ONLINE 0
19 ONLINE 1
USN STATUS XACTS
---------- --------------- ----------
20 ONLINE 0
12 rows selected.
发现原UNDOTBS1的回滚段处于PENDING OFFLINE状态，并有一个事物存在
如果要drop UNDOTBS1，需要等到事务完成后才行。
SQL> select usn,status,xacts from v$rollstat;
USN STATUS XACTS
---------- --------------- ----------
0 ONLINE 0
10 PENDING OFFLINE 0 -- 0表示事务结束
11 ONLINE 0
12 ONLINE 0
13 ONLINE 0
14 ONLINE 0
15 ONLINE 0
16 ONLINE 0
17 ONLINE 0
18 ONLINE 0
19 ONLINE 1
USN STATUS XACTS
---------- --------------- ----------
20 ONLINE 0
12 rows selected.
SQL> select usn,status,xacts from v$rollstat;
USN STATUS XACTS
---------- --------------- ----------
0 ONLINE 0
11 ONLINE 0
12 ONLINE 0
13 ONLINE 0
14 ONLINE 0
15 ONLINE 0
16 ONLINE 0
17 ONLINE 0
18 ONLINE 0
19 ONLINE 1
20 ONLINE 0
11 rows selected.
SQL> drop tablespace undotbs1 including contents and datafiles;
Tablespace dropped.
SQL>