删除undo表空间报ORA-01548
删除undo表空间报ORA-01548
用expdp导出数据报错:
C:\Documents and Settings\TEMP>expdp scott/tiger DUMPFILE=scott.dmp DIRECTORY=du
mp SCHEMAS=scott
Export: Release 11.2.0.1.0 - Production on 星期一 7月 23 21:58:14 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
连接到: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-31626: 作业不存在
ORA-31637: 无法创建作业 SYS_EXPORT_SCHEMA_02 (用户 SCOTT)
ORA-06512: 在 "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: 在 "SYS.KUPV$FT_INT", line 798
ORA-39244: 关系选择期间禁用删除空位图像标题的事件
ORA-06512: 在 "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: 在 "SYS.KUPC$QUE_INT", line 1825
ORA-02320: 无法创建嵌套表列 TREAT("USER_DATA" AS "KUPC$_FILE_LIST")."FILELIST"
的存储表
ORA-00376: 此时无法读取文件 3
ORA-01110: 数据文件 3: 'D:\APP\WWANG\ORADATA\MDSP\UNDOTBS01.DBF'
导出报错如上错误：
由于之前删除undotbs1表空间数据文件undotbs01执行如下指令删除的
alter database datafile 'D:\app\wwang\oradata\mdsp\undotbs01.dbf' offline drop
但是现在undotbs表空间还有活动的回滚段
现在建立undo表空间undotbs2把undo表空间切换到undotbs2上去
SQL> create undo tablespace undotbs2 datafile 'D:\app\wwang\oradata\mdsp\undotbs
21.dbf' size 100M;
表空间已创建。
SQL> alter system set undo_tablespace='undotbs2';
系统已更改。
SQL>
Undo表空间切换过去后删除undotbs1，报错如下
SQL> drop tablespace undotbs1 including contents;
drop tablespace undotbs1 including contents
第 1 行出现错误:
ORA-01548: 已找到活动回退段 '_SYSSMU1_1518548437$', 终止删除表空间
SQL>
再次导出还是报错上面的错误
这时查看
SQL> select SEGMENT_NAME,TABLESPACE_NAME,STATUS from dba_rollback_segs;
SEGMENT_NAME TABLESPACE_NAME STATUS
------------------------------ --------------- -------------------------
SYSTEM SYSTEM ONLINE
_SYSSMU13_967519202$ UNDOTBS1 OFFLINE
_SYSSMU12_1198886156$ UNDOTBS1 OFFLINE
_SYSSMU11_711815884$ UNDOTBS1 OFFLINE
_SYSSMU10_3176102001$ UNDOTBS1 NEEDS RECOVERY
_SYSSMU9_1126410412$ UNDOTBS1 NEEDS RECOVERY
_SYSSMU8_1557854099$ UNDOTBS1 NEEDS RECOVERY
_SYSSMU7_137577888$ UNDOTBS1 NEEDS RECOVERY
_SYSSMU6_1834113595$ UNDOTBS1 NEEDS RECOVERY
_SYSSMU5_1018230376$ UNDOTBS1 NEEDS RECOVERY
_SYSSMU4_2369290268$ UNDOTBS1 NEEDS RECOVERY
SEGMENT_NAME TABLESPACE_NAME STATUS
------------------------------ --------------- -------------------------
_SYSSMU3_991555123$ UNDOTBS1 NEEDS RECOVERY
_SYSSMU2_2082490410$ UNDOTBS1 NEEDS RECOVERY
_SYSSMU1_1518548437$ UNDOTBS1 NEEDS RECOVERY
_SYSSMU16_3838319334$ UNDOTBS2 ONLINE
_SYSSMU15_3971806561$ UNDOTBS2 ONLINE
_SYSSMU14_168326844$ UNDOTBS2 ONLINE
已选择17行。
SQL>
发现undotbs01中的回滚段status为NEEDS RECOVERY 这时如果有rman备份可以利用rman恢复
现在没有备份，如下方法解决：
先利用spfile创建一个pfile文件然后把_CORRUPTED_ROLLBACK_SEGMENTS这个隐含参数加到里面
SQL> create pfile from spfile;
文件已创建。