天达云 科技型企业 | 亚太互联网络信息中心(APNIC)成员 | 注册免费体验
天达云首页 > 互联网学习教程 > 关系型数据库 > Oracle不正规操作导致drop表空间drop用户报错ora38301(记录，未解决)

Oracle不正规操作导致drop表空间drop用户报错ora38301(记录，未解决)
更新：HHH   时间：2023-1-7

操作系统版本：
  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;

  3. BANNER
  4. --------------------------------------------------------------------------------
  5. Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

环境构造：
  1. SYS@proc> create table t(id int);

  3. Table created.

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

  7. 1 row created.

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

  11. 1 row created.

  13. SYS@proc> /

  15. 2 rows created.

  17. ...省略相同步骤...

  19. SYS@proc>/

  21. 1048576 rows created.
  22.  
  23. SYS@proc> commit;

  25. Commit complete.

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

  29.         MB
  30. ----------
  31.         25

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

  3. Tablespace created.

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

  7. User created.

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

  11. Grant succeeded.

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

  15. User altered.

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

  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


  8. SYS@proc> col file_name for a50
  9. SYS@proc> select file_name from dba_data_files;

  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

  22. 8 rows selected.

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

  26. Database altered.

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

  30. Table created.

  32. SYS@proc> conn km/oracle
  33. Connected.

  35. KM@proc> drop table t;

  37. Table dropped.

  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"

  47. 1 row deleted.

  49. SYS@proc> commit;

  51. Commit complete.

  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;

  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'

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

  67. User altered.

  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已经是空的，但是真正的空间并没被释放。


  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


  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 从所有用户的回收站清除所有对象


假如发生了这个场景，应该怎么去解决？
返回关系型数据库教程...
400-837-6568
7 * 24小时全天全国服务热线400电话
天达云·专业的云计算服务平台 全国400服务电话：400-837-6568
Copyright © 2011-2025 Tdyun.com. All Rights Reserved. 天达云 版权所有 粤ICP备18083267号-7