oracle 11g rac undo表空间切换(五)
2 _SYSSMU13_3714158714$ ONLINE
UNDOTBS2 _SYSSMU12_2799301969$ ONLINE
UNDOTBS2 _SYSSMU11_2193728667$ ONLINE
UNDOTBS3 _SYSSMU32_1846650689$ OFFLINE
TABLESPACE_NAME SEGMENT_NAME STATUS
------------------------------ ------------------------------ ----------------
UNDOTBS3 _SYSSMU28_3009640607$ OFFLINE
UNDOTBS3 _SYSSMU27_2601671938$ OFFLINE
UNDOTBS3 _SYSSMU26_1750999348$ OFFLINE
UNDOTBS3 _SYSSMU25_1760351227$ OFFLINE
UNDOTBS3 _SYSSMU24_4215245524$ OFFLINE
UNDOTBS3 _SYSSMU23_2874026201$ OFFLINE
UNDOTBS3 _SYSSMU22_2783794725$ OFFLINE
UNDOTBS3 _SYSSMU21_2564029858$ OFFLINE
UNDOTBS3 _SYSSMU10_548360867$ OFFLINE
31 rows selected.
删除undotbs3及datafiles
SQL> drop tablespace undotbs3 including contents and datafiles;
Tablespace dropped.
查询当前undo 使用情况
SQL> SELECT seg.tablespace_name "Tablespace Name", ts.bytes/1024/1024 "TS Size(MB)",
2 ue.status "UNDO Status", count(*) "Used Extents",
3 round(sum(ue.bytes)/1024/1024, 2) "Used Size(MB)",
4 round(sum(ue.bytes)/ts.bytes*100, 2) "Used Rate(%)"
5 FROM dba_segments seg, DBA_UNDO_EXTENTS ue,
6 (SELECT tablespace_name, sum(bytes) bytes
7 FROM dba_data_files GROUP BY tablespace_name) ts
8 WHERE ue.segment_NAME=seg.segment_NAME and seg.tablespace_name=ts.tablespace_name
9 GROUP BY seg.tablespace_name, ts.bytes, ue.status
10 ORDER BY seg.tablespace_name;
Tablespace Name TS Size(MB) UNDO Stat Used Extents Used Size(MB)
------------------------------ ----------- --------- ------------ -------------
Used Rate(%)
------------
UNDOTBS1 5000 EXPIRED 10 .63
.01
UNDOTBS1 5000 UNEXPIRED 10 .63
.01
UNDOTBS2 5000 EXPIRED 7 .44
.01
Tablespace Name TS Size(MB) UNDO Stat Used Extents Used Size(MB)
------------------------------ ----------- --------- ------------ -------------
Used Rate(%)
------------
UNDOTBS2 5000 UNEXPIRED 14 .88
.02