UNDO表空间不能drop，ORA-30013错误解决

最新推荐文章于 2024-04-01 23:45:04 发布

cixian0549

最新推荐文章于 2024-04-01 23:45:04 发布

阅读量1.1k

点赞数

文章标签：数据库

由于大量undo数据导致磁盘空间爆满，然后新建一个undo表空间undotbs1。将undo表空间切换过去后，drop掉原来的undo表空间undotbs2，出现ORA-30013错误。

切换后
SYS@prod>show parameter undo

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS1

drop掉undotbs2
SYS@prod>drop tablespace undotbs2 including contents and datafiles;
drop tablespace undotbs2 including contents and datafiles
*
ERROR at line 1:
ORA-30013: undo tablespace 'UNDOTBS2' is currently in use

[oracle@rh131:dbs]$oerr ora 30013
30013, 00000, "undo tablespace '%s' is currently in use"
// *Cause: the specified undo tablespace is currently used by another
// instance.
// *Action: Wait for the undo tablespace to become available or
// change to another name and reissue the statement.

解决办法
1、查找在回滚段中undotbs2的段名
SYS@prod>select SEGMENT_NAME,TABLESPACE_NAME,STATUS from dba_rollback_segs;

SEGMENT_NAME TABLESPACE_NAME STATUS
------------------------------ ------------------------------ ----------------
SYSTEM SYSTEM ONLINE
_SYSSMU1_2751737385$ UNDOTBS2 OFFLINE
_SYSSMU2_1068948819$ UNDOTBS2 OFFLINE
_SYSSMU3_793454965$ UNDOTBS2 OFFLINE
_SYSSMU4_29595104$ UNDOTBS2 OFFLINE
_SYSSMU5_4099828275$ UNDOTBS2 OFFLINE
_SYSSMU6_2031435162$ UNDOTBS2 OFFLINE
_SYSSMU7_1367400636$ UNDOTBS2 ONLINE
_SYSSMU8_1966551224$ UNDOTBS2 OFFLINE
_SYSSMU11_4094294264$ UNDOTBS1 ONLINE
_SYSSMU12_2971983910$ UNDOTBS1 ONLINE
_SYSSMU13_3591807852$ UNDOTBS1 ONLINE
_SYSSMU14_3096608065$ UNDOTBS1 ONLINE
_SYSSMU15_1766318059$ UNDOTBS1 ONLINE
_SYSSMU16_3715188403$ UNDOTBS1 ONLINE
_SYSSMU17_838587784$ UNDOTBS1 ONLINE
_SYSSMU18_3742004836$ UNDOTBS1 ONLINE
_SYSSMU19_1414966368$ UNDOTBS2 OFFLINE
_SYSSMU20_1185806686$ UNDOTBS2 OFFLINE
_SYSSMU21_1325029745$ UNDOTBS1 ONLINE
_SYSSMU22_3675209904$ UNDOTBS1 ONLINE

21 rows selected.

2、修改pfile文件，修改以下内容
*.undo_tablespace='UNDOTBS2'
_CORRUPTED_ROLLBACK_SEGMENTS=
(_SYSSMU1_2751737385$,_SYSSMU2_1068948819$,_SYSSMU3_793454965$,_SYSSMU4_29595104$,_SYSSMU5_4099828275$,_SYSSMU6_2031435162$,_SYSSMU7_1367400636$,_SYSSMU8_1966551224$,_SYSSMU19_1414966368$,_SYSSMU20_1185806686$)

3、以pfile启动实例
SYS@prod>startup force pfile='$ORACLE_HOME/dbs/initprod.ora';
ORACLE instance started.

Total System Global Area 835104768 bytes
Fixed Size 2232960 bytes
Variable Size 469765504 bytes
Database Buffers 360710144 bytes
Redo Buffers 2396160 bytes
Database mounted.
Database opened.

4、切换undo表空间，并drop掉undotbs2表空间
SYS@prod>show parameter undo

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS2

SYS@prod>alter system set undo_tablespace=undotbs1;

System altered.

SYS@prod>drop tablespace undotbs2 including contents and datafiles;

Tablespace dropped.

至此，undotbs2表空间成功drop掉，磁盘空间也被释放了不少。
最后以spfile文件（spfile文件记录的undo表空间是切换后的）启动实例，或者修改pfile文件会之前状态启动实例即可。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/30136543/viewspace-1563961/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/30136543/viewspace-1563961/