Oracle不正规操作导致drop表空间drop用户报错ora38301(记录，未解决)
操作系统版本：
[oracle@oracle trace]$ uname -a
Linux oracle.example.com 2.6.32-431.el6.x86_64 #1 SMP Sun Nov 10 22:19:54 EST 2013 x86_64 x86_64 x86_64 GNU/Linux
[oracle@oracle trace]$ lsb_release -a
LSB Version::base-4.0-amd64:base-4.0-noarch:core-4.0-amd64:core-4.0-noarch:graphics-4.0-amd64:graphics-4.0-noarch:printing-4.0-amd64:printing-4.0-noarch
Distributor ID: RedHatEnterpriseServer
Description:Red Hat Enterprise Linux Server release 6.5 (Santiago)
Release: 6.5
Codename: Santiago
数据库版本：
SYS@proc> select * from v$version where rownum=1;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
环境构造：
SYS@proc> create table t(id int);
Table created.
SYS@proc> insert into t values(1);
1 row created.
SYS@proc> insert into t select * from t;
1 row created.
SYS@proc> /
2 rows created.
...省略相同步骤...
SYS@proc>/
1048576 rows created.
SYS@proc> commit;
Commit complete.
SYS@proc> select BYTES/1024/1024 mb from dba_segments where owner='SYS' and segment_name='T';
MB
----------
25
SYS@proc> create tablespace abc datafile '/u01/app/oracle/oradata/proc/abc01.dbf' size 26m;
Tablespace created.
SYS@proc> create user km identified by oracle account unlock;
User created.
SYS@proc> grant connect,create table to km;
Grant succeeded.
SYS@proc> alter user km quota 26m on abc;
User altered.
SYS@proc> select AUTOEXTENSIBLE from dba_data_files where tablespace_name='ABC';
AUT
---
NO
SYS@proc> create table km.t tablespace abc as select * from t;
create table km.t tablespace abc as select * from t
*
ERROR at line 1:
ORA-01652: unable to extend temp segment by 128 in tablespace ABC
SYS@proc> col file_name for a50
SYS@proc> select file_name from dba_data_files;
FILE_NAME
--------------------------------------------------
/u01/app/oracle/oradata/proc/test01.dbf
/u01/app/oracle/oradata/proc/example01.dbf
/u01/app/oracle/oradata/proc/users01.dbf
/u01/app/oracle/oradata/proc/abc01.dbf
/u01/app/oracle/oradata/proc/sysaux01.dbf
/u01/app/oracle/oradata/proc/system01.dbf
/u01/app/oracle/oradata/proc/undotbs2_1.dbf
/u01/app/oracle/oradata/proc/undotbs2_2.dbf
8 rows selected.
SYS@proc> alter database datafile '/u01/app/oracle/oradata/proc/abc01.dbf' resize 30m;
Database altered.
SYS@proc> create table km.t tablespace abc as select * from t;
Table created.
SYS@proc> conn km/oracle
Connected.
KM@proc> drop table t;
Table dropped.
KM@proc> show recycle;
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
T BIN$Q38bmJwFDYXgU28cqMDtew==$0 TABLE 2016-12-13:07:03:11
KM@proc> conn / as sysdba
Connected.
SYS@proc> delete from recyclebin$; --不正规操作，正确应该在km用户下执行purge table t或者purge table "BIN$Q38bmJwFDYXgU28cqMDtew==$0"
1 row deleted.
SYS@proc> commit;
Commit complete.
SYS@proc> conn km/oracle
Connected.
KM@proc> show recycle;
KM@proc> conn / as sysdba
Connected.
SYS@proc> create table km.t tablespace abc as select * from t;
create table km.t tablespace abc as select * from t
*
ERROR at line 1:
ORA-01536: space quota exceeded for tablespace 'ABC'
SYS@proc> alter user km quota unlimited on abc;
User altered.
SYS@proc> create table km.t tablespace abc as select * from t;
create table km.t tablespace abc as select * from t
*
ERROR at line 1:
ORA-01652: unable to extend temp segment by 128 in tablespace ABC --这里可以看出虽然在km用户执行show recycle已经是空的，但是真正的空间并没被释放。
SYS@proc> drop tablespace abc including contents and datafiles;
drop tablespace abc 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
SYS@proc> drop user km cascade;
drop user km cascade
*
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
正规清理回收站的表：
1、使用 PURGE TABLE original_table_name; 这里的 original_table_name 表示未 drop 以前的名称
2、使用 PURGE TABLE recyclebin_object_name; 这里的 recyclebin_object_name 表示回收站中的对象名称
3、使用 PURGE TABLESPACE tablespace_name 从指定的表空间中清除所有的丢弃对象
4、使用 PURGE TABLESPACE tablespace_name USER user_name 从回收站中清除属于某个特定用户的所有丢弃对象。
5、DROP USER user_name cascade 直接删除指定用户及其所属的全部对象，也就是说，DROP USER 命令会绕过回收站进行直接删除。
6、使用 PURGE RECYCLEBIN 命令清除用户自己的回收站
7、PURGE DBA_RECYCLEBIN 从所有用户的回收站清除所有对象
假如发生了这个场景，应该怎么去解决？
[oracle@oracle trace]$ uname -a
Linux oracle.example.com 2.6.32-431.el6.x86_64 #1 SMP Sun Nov 10 22:19:54 EST 2013 x86_64 x86_64 x86_64 GNU/Linux
[oracle@oracle trace]$ lsb_release -a
LSB Version::base-4.0-amd64:base-4.0-noarch:core-4.0-amd64:core-4.0-noarch:graphics-4.0-amd64:graphics-4.0-noarch:printing-4.0-amd64:printing-4.0-noarch
Distributor ID: RedHatEnterpriseServer
Description:Red Hat Enterprise Linux Server release 6.5 (Santiago)
Release: 6.5
Codename: Santiago
数据库版本：
SYS@proc> select * from v$version where rownum=1;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
环境构造：
SYS@proc> create table t(id int);
Table created.
SYS@proc> insert into t values(1);
1 row created.
SYS@proc> insert into t select * from t;
1 row created.
SYS@proc> /
2 rows created.
...省略相同步骤...
SYS@proc>/
1048576 rows created.
SYS@proc> commit;
Commit complete.
SYS@proc> select BYTES/1024/1024 mb from dba_segments where owner='SYS' and segment_name='T';
MB
----------
25
SYS@proc> create tablespace abc datafile '/u01/app/oracle/oradata/proc/abc01.dbf' size 26m;
Tablespace created.
SYS@proc> create user km identified by oracle account unlock;
User created.
SYS@proc> grant connect,create table to km;
Grant succeeded.
SYS@proc> alter user km quota 26m on abc;
User altered.
SYS@proc> select AUTOEXTENSIBLE from dba_data_files where tablespace_name='ABC';
AUT
---
NO
SYS@proc> create table km.t tablespace abc as select * from t;
create table km.t tablespace abc as select * from t
*
ERROR at line 1:
ORA-01652: unable to extend temp segment by 128 in tablespace ABC
SYS@proc> col file_name for a50
SYS@proc> select file_name from dba_data_files;
FILE_NAME
--------------------------------------------------
/u01/app/oracle/oradata/proc/test01.dbf
/u01/app/oracle/oradata/proc/example01.dbf
/u01/app/oracle/oradata/proc/users01.dbf
/u01/app/oracle/oradata/proc/abc01.dbf
/u01/app/oracle/oradata/proc/sysaux01.dbf
/u01/app/oracle/oradata/proc/system01.dbf
/u01/app/oracle/oradata/proc/undotbs2_1.dbf
/u01/app/oracle/oradata/proc/undotbs2_2.dbf
8 rows selected.
SYS@proc> alter database datafile '/u01/app/oracle/oradata/proc/abc01.dbf' resize 30m;
Database altered.
SYS@proc> create table km.t tablespace abc as select * from t;
Table created.
SYS@proc> conn km/oracle
Connected.
KM@proc> drop table t;
Table dropped.
KM@proc> show recycle;
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
T BIN$Q38bmJwFDYXgU28cqMDtew==$0 TABLE 2016-12-13:07:03:11
KM@proc> conn / as sysdba
Connected.
SYS@proc> delete from recyclebin$; --不正规操作，正确应该在km用户下执行purge table t或者purge table "BIN$Q38bmJwFDYXgU28cqMDtew==$0"
1 row deleted.
SYS@proc> commit;
Commit complete.
SYS@proc> conn km/oracle
Connected.
KM@proc> show recycle;
KM@proc> conn / as sysdba
Connected.
SYS@proc> create table km.t tablespace abc as select * from t;
create table km.t tablespace abc as select * from t
*
ERROR at line 1:
ORA-01536: space quota exceeded for tablespace 'ABC'
SYS@proc> alter user km quota unlimited on abc;
User altered.
SYS@proc> create table km.t tablespace abc as select * from t;
create table km.t tablespace abc as select * from t
*
ERROR at line 1:
ORA-01652: unable to extend temp segment by 128 in tablespace ABC --这里可以看出虽然在km用户执行show recycle已经是空的，但是真正的空间并没被释放。
SYS@proc> drop tablespace abc including contents and datafiles;
drop tablespace abc 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
SYS@proc> drop user km cascade;
drop user km cascade
*
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
正规清理回收站的表：
1、使用 PURGE TABLE original_table_name; 这里的 original_table_name 表示未 drop 以前的名称
2、使用 PURGE TABLE recyclebin_object_name; 这里的 recyclebin_object_name 表示回收站中的对象名称
3、使用 PURGE TABLESPACE tablespace_name 从指定的表空间中清除所有的丢弃对象
4、使用 PURGE TABLESPACE tablespace_name USER user_name 从回收站中清除属于某个特定用户的所有丢弃对象。
5、DROP USER user_name cascade 直接删除指定用户及其所属的全部对象，也就是说，DROP USER 命令会绕过回收站进行直接删除。
6、使用 PURGE RECYCLEBIN 命令清除用户自己的回收站
7、PURGE DBA_RECYCLEBIN 从所有用户的回收站清除所有对象
假如发生了这个场景，应该怎么去解决？
声明：本站所有文章资源内容，如无特殊说明或标注，均为采集网络资源。如若本站内容侵犯了原著者的合法权益，可联系本站删除。
