UNDO表空间占用高解决办法

undo表空间不能释放时,最好的解决办法就是:

1.重新建立一个新的undo表空间.

2.设置数据库的undo表空间为新的undo表空间
3.删除旧的undo表空间及其内容
步骤：
SQL>create undo tablespace undotbs3 datafile '/dev/rora_data_02' size 11518m extent management local;
SQL>alter system set undo_tablespace='UNDOTBS3' scope=both sid='JLZDH1';
SQL>drop tablespace undotbs1 including contents;
ORA-30013 : undo tablespace undotbs1 is currently in use

SQL> show parameter undo
 
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 450
undo_tablespace string UNDOTBS3
SQL> select tablespace_name,status,sum(bytes/1024/1024) from dba_undo_extents group by tablespace_name,status;

TABLESPACE_NAME STATUS SUM(BYTES/1024/1024)
------------------------------ --------- --------------------
UNDOTBS3 UNEXPIRED 6.25
UNDOTBS2 EXPIRED 291.25
UNDOTBS1 EXPIRED 7152.1875
UNDOTBS3 EXPIRED 13.0625

SQL> drop tablespace undotbs1 including contents;

Tablespace dropped.

SQL> select tablespace_name,status,sum(bytes/1024/1024) from dba_undo_extents group by tablespace_name,status;

TABLESPACE_NAME STATUS SUM(BYTES/1024/1024)
------------------------------ --------- --------------------
UNDOTBS3 UNEXPIRED 2.3125
UNDOTBS2 EXPIRED 291.25
UNDOTBS3 EXPIRED 17

--ORACLE的SID查看：

select name form V$database;

select instance_name from  V$instance;

--附报错处理方法：
1 create undo tablespace undotBS2 datafile 'D:oracleoradataPS30710UNDOTBS2.DBF' size 100m;
alter system set undo_tablespace=undotBS2;
drop tablespace undotbs1 including contents;(进行这部操作的时候会报下面的错)：
ORA-01548: 已找到活动回退段'_SYSSMU1$'，终止删除表空间
2 修改文件init.ora.162007221035，如下：
undo_management=manual
undo_retention=10800
undo_tablespace=undotBS2
_CORRUPTED_ROLLBACK_SEGMENTS =(_SYSSMU1$,_SYSSMU2$,_SYSSMU3$,_SYSSMU3$,_SYSSMU4$,_SYSSMU5$,_SYSSMU6$,_SYSSMU7$,_SYSSMU8$,_SYSSMU9$,_SYSSMU10$)
3 启动服务
startup pfile='D:oracleadminPS30710pfileinit.ora.421200715439'
4 删除表空间
drop tablespace undotbs1 including contents;
create undo tablespace undotBS1 datafile 'D:oracleoradataPS30710UNDOTBS01.DBF' size 200m;
drop tablespace undotBS2 including contents;
5 修改init.ora.162007221035，如下：
undo_management=auto
undo_retention=10800
undo_tablespace=undotBS1
_CORRUPTED_ROLLBACK_SEGMENTS =(_SYSSMU1$,_SYSSMU2$,_SYSSMU3$,_SYSSMU3$,_SYSSMU4$,_SYSSMU5$,_SYSSMU6$,_SYSSMU7$,_SYSSMU8$,_SYSSMU9$,_SYSSMU10$)
6 关闭服务，并且用下面的命令重新启动服务
startup pfile='D:oracleadminPS30710pfileinit.ora.421200715439'
7 拷贝spfile文件，原先的spfile文件做好备份
create spfile='D:oracleora92databaseSPFILEPS30710.ORA' FROM pfile='D:oracleadminPS30710pfileinit.ora.421200715439';
8 关闭服务器，重新启动服务器，即可。
select segment_name,status,tablespace_name from dba_rollback_segs;
SEGMENT_NAME STATUS TABLESPACE_NAME
------------------------------ ---------------- ------------------------------
SYSTEM ONLINE SYSTEM
_SYSSMU1$ ONLINE UNDOTBS1
_SYSSMU2$ ONLINE UNDOTBS1
_SYSSMU3$ ONLINE UNDOTBS1
_SYSSMU4$ ONLINE UNDOTBS1
_SYSSMU5$ ONLINE UNDOTBS1
_SYSSMU6$ ONLINE UNDOTBS1
_SYSSMU7$ ONLINE UNDOTBS1
_SYSSMU8$ ONLINE UNDOTBS1
_SYSSMU9$ ONLINE UNDOTBS1
_SYSSMU10$ ONLINE UNDOTBS1
已选择11行。

--附：init.ora.路径：

D:\app\Administrator\admin\qingge612\pfile

undo_tablespace=UNDOTBS1--修改为要替换的TABLESPACES名称