Oracle不正規操作導致drop表空間drop使用者報錯ora38301(記錄,未解決)

PiscesCanon發表於2017-07-21
作業系統版本:
  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. BANNER
  3. --------------------------------------------------------------------------------
  4. Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

環境構造:
  1. SYS@proc> create table t(id int);

  2. Table created.

  3. SYS@proc> insert into t values(1);

  4. 1 row created.

  5. SYS@proc> insert into t select * from t;

  6. 1 row created.

  7. SYS@proc> /

  8. 2 rows created.

  9. ...省略相同步驟...

  10. SYS@proc>/

  11. 1048576 rows created.
  12.  
  13. SYS@proc> commit;

  14. Commit complete.

  15. SYS@proc> select BYTES/1024/1024 mb from dba_segments where owner='SYS' and segment_name='T';

  16.         MB
  17. ----------
  18.         25

  1. SYS@proc> create tablespace abc datafile '/u01/app/oracle/oradata/proc/abc01.dbf' size 26m;

  2. Tablespace created.

  3. SYS@proc> create user km identified by oracle account unlock;

  4. User created.

  5. SYS@proc> grant connect,create table to km;

  6. Grant succeeded.

  7. SYS@proc> alter user km quota 26m on abc;

  8. User altered.

  9. SYS@proc> select AUTOEXTENSIBLE from dba_data_files where tablespace_name='ABC';

  10. AUT
  11. ---
  12. 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. SYS@proc> col file_name for a50
  7. SYS@proc> select file_name from dba_data_files;

  8. FILE_NAME
  9. --------------------------------------------------
  10. /u01/app/oracle/oradata/proc/test01.dbf
  11. /u01/app/oracle/oradata/proc/example01.dbf
  12. /u01/app/oracle/oradata/proc/users01.dbf
  13. /u01/app/oracle/oradata/proc/abc01.dbf
  14. /u01/app/oracle/oradata/proc/sysaux01.dbf
  15. /u01/app/oracle/oradata/proc/system01.dbf
  16. /u01/app/oracle/oradata/proc/undotbs2_1.dbf
  17. /u01/app/oracle/oradata/proc/undotbs2_2.dbf

  18. 8 rows selected.

  19. SYS@proc> alter database datafile '/u01/app/oracle/oradata/proc/abc01.dbf' resize 30m;

  20. Database altered.

  21. SYS@proc> create table km.t tablespace abc as select * from t;

  22. Table created.

  23. SYS@proc> conn km/oracle
  24. Connected.

  25. KM@proc> drop table t;

  26. Table dropped.

  27. KM@proc> show recycle;
  28. ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE   DROP TIME
  29. ---------------- ------------------------------ ------------ -------------------
  30. T                BIN$Q38bmJwFDYXgU28cqMDtew==$0 TABLE        2016-12-13:07:03:11
  31. KM@proc> conn / as sysdba
  32. Connected.
  33. SYS@proc> delete from recyclebin$--不正規操作,正確應該在km使用者下執行purge table t或者purge table "BIN$Q38bmJwFDYXgU28cqMDtew==$0"

  34. 1 row deleted.

  35. SYS@proc> commit;

  36. Commit complete.

  37. SYS@proc> conn km/oracle
  38. Connected.
  39. KM@proc> show recycle;
  40. KM@proc> conn / as sysdba
  41. Connected.
  42. SYS@proc> create table km.t tablespace abc as select * from t;

  43. create table km.t tablespace abc as select * from t
  44.                                                   *
  45. ERROR at line 1:
  46. ORA-01536: space quota exceeded for tablespace 'ABC'

  47. SYS@proc> alter user km quota unlimited on abc;

  48. User altered.

  49. SYS@proc> create table km.t tablespace abc as select * from t;
  50.  create table km.t tablespace abc as select * from t
  51. *
  52. ERROR at line 1:
  53. ORA-01652: unable to extend temp segment by 128 in tablespace ABC  --這裡可以看出雖然在km使用者執行show recycle已經是空的,但是真正的空間並沒被釋放。


  54. SYS@proc> drop tablespace abc including contents and datafiles;
  55. drop tablespace abc including contents and datafiles
  56. *
  57. ERROR at line 1:
  58. ORA-00604: error occurred at recursive SQL level 1
  59. ORA-38301: can not perform DDL/DML over objects in Recycle Bin


  60. SYS@proc> drop user km cascade;
  61. drop user km cascade
  62. *
  63. ERROR at line 1:
  64. ORA-00604: error occurred at recursive SQL level 1
  65. 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/,如需轉載,請註明出處,否則將追究法律責任。

相關文章