Chinaunix首页 | 论坛 | 博客
  • 博客访问: 9166759
  • 博文数量: 1669
  • 博客积分: 16831
  • 博客等级: 上将
  • 技术积分: 12594
  • 用 户 组: 普通用户
  • 注册时间: 2011-02-25 07:23
个人简介

柔中带刚,刚中带柔,淫荡中富含柔和,刚猛中荡漾风骚,无坚不摧,无孔不入!

文章分类

全部博文(1669)

文章存档

2023年(4)

2022年(1)

2021年(10)

2020年(24)

2019年(4)

2018年(19)

2017年(66)

2016年(60)

2015年(49)

2014年(201)

2013年(221)

2012年(638)

2011年(372)

分类: Oracle

2015-08-05 16:16:34

在alter/drop表空间时遇到错误ORA-38301,ORA-00604,purgedba_recyclebin也不行
2015-02-26      0 个评论    来源:msdnchina的专栏  
收藏    我要投稿

在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相关的错误

?
1
2
3
4
5
6
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也不行:

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
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

?
1
2
3
4
5
6
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

?
1
2
3
SQL> purge recyclebin;
 
SQL> drop tablespace TEST_TBS including contents and datafiles;


若是这么还是不解决问题,最快速的变通方法就是disable掉recyclebin,再drop tablespace,再enable recyclbin

?
1
2
3
4
5
6
7
SQL> conn / as sysdba
 
SQL> alter system set recyclebin=off;
 
SQL> drop tablespace TEST_TBS including contents and datafiles;
 
SQL> alter system set recyclebin=on;

---提醒:11gR2里边,recyclebin貌似是静态参数。

若是上面的步骤不能解决问题,请用下面的方法:

?
1
2
3
4
5
6
7
1) sqlplus / as sysdba
2) ALTER SYSTEM SET recyclebin = OFF DEFERRED;
3) disconnect and exit sqlplus
4) sqlplus / as sysdba
5) drop tablespace........
6) sqlplus / as sysdba
7) ALTER SYSTEM SET recyclebin = ON DEFERRED; or ALTER SYSTEM SET recyclebin = ON;


阅读(2372) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~