Oracle不正规操作导致drop表空间drop用户报错ora38301(记录，未解决)

操作系统版本：

1. [oracle@oracle trace]$ uname -a
   
2. 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
   
3. [oracle@oracle trace]$ lsb_release -a
   
4. 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
   
5. Distributor ID: RedHatEnterpriseServer
   
6. Description:    Red Hat Enterprise Linux Server release 6.5 (Santiago)
   
7. Release:        6.5
   
8. Codename:       Santiago

数据库版本：

1. SYS@proc> select * from v$version where rownum=1;
   
2. 
   
3. BANNER
   
4. --------------------------------------------------------------------------------
   
5. 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

1. SYS@proc> create tablespace abc datafile '/u01/app/oracle/oradata/proc/abc01.dbf' size 26m;
   
2. 
   
3. Tablespace created.
   
4. 
   
5. SYS@proc> create user km identified by oracle account unlock;
   
6. 
   
7. User created.
   
8. 
   
9. SYS@proc> grant connect,create table to km;
   
10. 
    
11. Grant succeeded.
    
12. 
    
13. SYS@proc> alter user km quota 26m on abc;
    
14. 
    
15. User altered.
    
16. 
    
17. SYS@proc> select AUTOEXTENSIBLE from dba_data_files where tablespace_name='ABC';
    
18. 
    
19. AUT
    
20. ---
    
21. NO

1. SYS@proc> create table km.t tablespace abc as select * from t;
   
2. create table km.t tablespace abc as select * from t
   
3. *
   
4. ERROR at line 1:
   
5. ORA-01652: unable to extend temp segment by 128 in tablespace ABC
   
6. 
   
7. 
   
8. SYS@proc> col file_name for a50
   
9. SYS@proc> select file_name from dba_data_files;
   
10. 
    
11. FILE_NAME
    
12. --------------------------------------------------
    
13. /u01/app/oracle/oradata/proc/test01.dbf
    
14. /u01/app/oracle/oradata/proc/example01.dbf
    
15. /u01/app/oracle/oradata/proc/users01.dbf
    
16. /u01/app/oracle/oradata/proc/abc01.dbf
    
17. /u01/app/oracle/oradata/proc/sysaux01.dbf
    
18. /u01/app/oracle/oradata/proc/system01.dbf
    
19. /u01/app/oracle/oradata/proc/undotbs2_1.dbf
    
20. /u01/app/oracle/oradata/proc/undotbs2_2.dbf
    
21. 
    
22. 8 rows selected.
    
23. 
    
24. SYS@proc> alter database datafile '/u01/app/oracle/oradata/proc/abc01.dbf' resize 30m;
    
25. 
    
26. Database altered.
    
27. 
    
28. SYS@proc> create table km.t tablespace abc as select * from t;
    
29. 
    
30. Table created.
    
31. 
    
32. SYS@proc> conn km/oracle
    
33. Connected.
    
34. 
    
35. KM@proc> drop table t;
    
36. 
    
37. Table dropped.
    
38. 
    
39. KM@proc> show recycle;
    
40. ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE   DROP TIME
    
41. ---------------- ------------------------------ ------------ -------------------
    
42. T                BIN$Q38bmJwFDYXgU28cqMDtew==$0 TABLE        2016-12-13:07:03:11
    
43. KM@proc> conn / as sysdba
    
44. Connected.
    
45. SYS@proc> delete from recyclebin$;  --不正规操作，正确应该在km用户下执行purge table t或者purge table "BIN$Q38bmJwFDYXgU28cqMDtew==$0"
    
46. 
    
47. 1 row deleted.
    
48. 
    
49. SYS@proc> commit;
    
50. 
    
51. Commit complete.
    
52. 
    
53. SYS@proc> conn km/oracle
    
54. Connected.
    
55. KM@proc> show recycle;
    
56. KM@proc> conn / as sysdba
    
57. Connected.
    
58. SYS@proc> create table km.t tablespace abc as select * from t;
    
59. 
    
60. create table km.t tablespace abc as select * from t
    
61.                                                   *
    
62. ERROR at line 1:
    
63. ORA-01536: space quota exceeded for tablespace 'ABC'
    
64. 
    
65. SYS@proc> alter user km quota unlimited on abc;
    
66. 
    
67. User altered.
    
68. 
    
69. SYS@proc> create table km.t tablespace abc as select * from t;
    
70.  create table km.t tablespace abc as select * from t
    
71. *
    
72. ERROR at line 1:
    
73. ORA-01652: unable to extend temp segment by 128 in tablespace ABC  --这里可以看出虽然在km用户执行show recycle已经是空的，但是真正的空间并没被释放。
    
74. 
    
75. 
    
76. SYS@proc> drop tablespace abc including contents and datafiles;
    
77. drop tablespace abc including contents and datafiles
    
78. *
    
79. ERROR at line 1:
    
80. ORA-00604: error occurred at recursive SQL level 1
    
81. ORA-38301: can not perform DDL/DML over objects in Recycle Bin
    
82. 
    
83. 
    
84. SYS@proc> drop user km cascade;
    
85. drop user km cascade
    
86. *
    
87. ERROR at line 1:
    
88. ORA-00604: error occurred at recursive SQL level 1
    
89. 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 从所有用户的回收站清除所有对象


假如发生了这个场景，应该怎么去解决？

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

转载于:http://blog.itpub.net/30174570/viewspace-2142410/