【翻译自mos文章】在alter/drop表空间时遇到错误ORA-38301,ORA-00604,purge dba_recyclebin 也不行
来源:互联网 发布:买家怎么申请淘宝客 编辑:程序博客网 时间:2024/05/21 17:02
在alter/drop表空间时遇到错误ORA-38301,ORA-00604,purge dba_recyclebin 也不行
适用于:
Oracle Database - Enterprise Edition - Version 10.2.0.1 and later
Information in this document applies to any platform.
症状:
当你试图drop一个empty的tablespace时,遇到与recyclebin相关的错误
SQL> drop tablespace TEST_TBS including contents and datafiles; drop tablespace TEST_TBS including contents and datafiles * ERROR at line 1: ORA-00604: error occurred at recursive SQL level 1 ORA-38301: can not perform DDL/DML over objects in Recycle Bin
尝试purge dba_recyclebin 并 offline该tablespace也不行:
SQL> purge dba_recyclebin;DBA Recyclebin purged.SQL> alter tablespace TEST_TBS offline;Tablespace altered.SQL> drop tablespace TEST_TBS including contents and datafiles; drop tablespace TEST_TBS including contents and datafiles * ERROR at line 1: ORA-00604: error occurred at recursive SQL level 1 ORA-38301: can not perform DDL/DML over objects in Recycle Bin
原因:
查询 dba_segments显示该表空间内还有segments
SQL> select SEGMENT_NAME,SEGMENT_TYPE,owner from dba_segments where tablespace_name='TEST_TBS'; SEGMENT_NAME SEGMENT_TYPE OWNER ------------------------------ ------------------ ------------------------------ BIN$Pks1AnxmMCTgQ8+Ct10wJA==$0 TABLE ORACLE BIN$Pks790fcQEzgQ8+Ct11ATA==$0 TABLE ORACLE
解决方案:
用该schema的owner登陆sqlplus,然后执行purge recyclebin,然后再删除drop tablespace
SQL> purge recyclebin;SQL> drop tablespace TEST_TBS including contents and datafiles;
若是这么还是不解决问题,最快速的变通方法就是disable掉recyclebin,再drop tablespace,再enable recyclbin
SQL> conn / as sysdbaSQL> alter system set recyclebin=off;SQL> drop tablespace TEST_TBS including contents and datafiles;SQL> alter system set recyclebin=on;
---提醒:11gR2里边,recyclebin貌似是静态参数。
若是上面的步骤不能解决问题,请用下面的方法:
1) sqlplus / as sysdba2) ALTER SYSTEM SET recyclebin = OFF DEFERRED;3) disconnect and exit sqlplus4) sqlplus / as sysdba5) drop tablespace........6) sqlplus / as sysdba7) ALTER SYSTEM SET recyclebin = ON DEFERRED; or ALTER SYSTEM SET recyclebin = ON;
0 0
- 【翻译自mos文章】在alter/drop表空间时遇到错误ORA-38301,ORA-00604,purge dba_recyclebin 也不行
- 【翻译自mos文章】使用aum( Automatic Undo Management) 时遇到 ORA-01555错误--- 原因和解决方案。
- 【翻译自mos文章】运行utlpwdmg.sql之后报ORA-28003, ORA-20001, ORA-20002, ORA-20003, ORA-20004 错误
- 【翻译自mos文章】在Oracle 12c中建立用户--避免ORA-65096 or ORA-65049错误
- 【翻译自mos文章】ORA-19815, Flashback Logs-Space management
- 【翻译自mos文章】OGG Director 不会purge log table
- 【翻译自mos文章】在RHEL7或者OL7上安装11.2.0.4时遇到错误 “undefined reference to symbol ‘B_DestroyKeyObject’”
- 【翻译自mos文章】在10g中,当发生ORA-00020时,sqlplus登陆会报“connected to an idle instance”
- 【翻译自mos文章】在使用Linux大页内存的配置中,使用drop_cache时导致的ORA-600 [KGHLKREM1]问题
- 【翻译自mos文章】/dev/shm应该设置多大Oracle 实例启动时才能不报ORA-00845
- 【翻译自mos文章】rman 备份时报:ORA-02396: exceeded maximum idle time
- 【翻译自mos文章】从tablespace中 删除空的datafile时报ora-03262
- 【翻译自mos文章】使用Windows操作系统的Dell Pcserver,Oracle db报错:ORA-8103
- 【翻译自mos文章】对于JDBC thin client连接,ORA-28040 and SQLNET.ALLOWED_LOGON_VERSION_CLIENT
- 删除表空间时,遇到了ORA-14404错误
- 给表空间增加数据文件的时候遇到ORA-00470错误,数据库也崩溃了!
- 【翻译自mos文章】检测并解决datafile os header(Block Zero)的 损坏- - ORA-27047 DBV-107 ORA-1157/ORA-27048
- 【翻译自mos文章】在Oracle Linux 7上安装11.2.0.4时遇到缺少 pdksh-5.2.14 包
- ORA-00604 ORA-00376 Unable to Drop a tablespace in GoldenGate (文档 ID 1930964.1)
- UIScrollView控件实现缩放功能
- HDOJ 1196 Lowest Bit
- 【hibernate框架】几种集合映射
- uva11367 - Full Tank? 最短路 DP
- 【翻译自mos文章】在alter/drop表空间时遇到错误ORA-38301,ORA-00604,purge dba_recyclebin 也不行
- 软件架构师的能力与特质
- poj1328 贪心
- ScrollView想要OnTouch你,真的不容易。
- hdu4512---吉哥系列故事——完美队形I
- C语言重难点分析(一)
- 黑马程序员——ObjC基础—Cocoa基础框架
- Python逐行读取文件内容
- 如何用C++来计算大数字乘法