前言
因磁盤空間不足，需要將undo表空間遷移到其它的存儲空間
本文介紹如何優雅的刪除undo表空間，并在新的存儲空間中創建新的undo表空間
詳細操作步驟如下：
1、查看默認undo表空間
SQL>show parameter undo
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS
2、查看undo表空間使用情況
SQL>set linesize 1000
SQL>set pagesize 1000
SQL>col usagep for a10
SQL>col tablespace_name for a20
SQL>select ff.s tablespace_name,ff.b total,(ff.b - fr.b) usage,fr.b free,round((ff.b-fr.b) / ff.b * 100) || '%' usagepfrom (select tablespace_name s, sum(bytes) / 1024 / 1024 bfrom dba_data_filesgroup by tablespace_name) ff,(select tablespace_name s, sum(bytes) / 1024 / 1024 bfrom dba_free_spacegroup by tablespace_name) frwhere ff.s = fr.s order by round ((ff.b - fr.b) / ff.b * 100);
TABLESPACE_NAME TOTAL USAGE FREE USAGEP
-------------------- ---------- ---------- ---------- ----------
UNDOTBS 18200 70.9375 18129.0625 0%
3、查看undo表空間數據文件
SQL>select file_id,file_name,tablespace_name,status,autoextensible,bytes/1024/1024 MB from dba_data_files;FILE_ID FILE_NAME TABLESPACE_NAME STATUS AUTOEXTENSIBLE MB
---------- ------------------------------------------------------------ -------------------- --------- --------------- ----------
34 +DATA/orcl/datafile/undotbs.273.1079005483 UNDOTBS AVAILABLE YES 18200
4、創建新的undo表空間
SQL>create undo tablespace undotbs1 datafile '+DATA' size 100m autoextend on;
Tablespace created.
5、設置默認的undo表空間
SQL>alter system set undo_tablespace=UNDOTBS1 scope=both;
System altered.
6、查看修改后的undo表空間
SQL>show parameter undo
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS1
7、告警日志提示
Undo Tablespace 48 moved to Pending Switch-Out state.
表示正在遷移undo數據到新的undo表空間
當出現**Undo Tablespace 48 successfully switched out.**表示遷移完成，可以刪除舊的undo表空間了。
查看正在使用舊的undo表空間：未選擇表示沒有正在使用，可以刪除舊的undo表空間。
SQL>select * from dba_rollback_segs where tablespace_name='UNDOTBS' and status='ONLINE';
no rows selected
8、刪除舊的undo表空間
SQL>drop tablespace undotbs including contents and datafiles;
Tablespace dropped.
9、undo表空間遷移過程監控
9.1、查看正在使用的undo段
SQL>select segment_name,owner,tablespace_name,status
from dba_rollback_segs
where tablespace_name = 'UNDOTBS' and status = 'ONLINE';
9.2、查看哪個用戶在使用undo段
SQL>select s.username,s.status,s.sid, u.namefrom v$transaction t, v$rollstat r, v$rollname u, v$session swhere s.taddr = t.addrand t.xidusn = r.usnand r.usn = u.usnorder by s.username;
9.3、查看使用舊的undo表空間的狀態是否有online狀態
SQL>select * from dba_rollback_segs where tablespace_name='UNDOTBS' and status='ONLINE';
SEGMENT_NAME OWNER TABLESPACE_NAME SEGMENT_ID FILE_ID BLOCK_ID INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE STATUS
------------------------------ ------ -------------------- ---------- ---------- ---------- -------------- ----------- ----------- ----------- ------------ ------
_SYSSMU129_1627357485$ PUBLIC UNDOTBS 129 34 272 131072 65536 2 32765 ONLINE
9.4、編寫正在占用undo表空間的kill語句
SQL>SELECT 'alter system kill session '''||s.sid|| ','||s.serial#|| ''''||';'FROM v$session s,v$transaction t,v$rollname r,v$rollstat g,dba_rollback_segs h,v$sqlarea iWHERE t.addr = s.taddrAND t.xidusn = r.usnAND r.usn = g.usnand r.name = h.segment_nameAND s.PREV_SQL_ID=i.SQL_ID and h.tablespace_name='UNDOTBS' and s.status='INACTIVE' ;
