首页 » ORACLE 9i-23ai » Invalid segment BIN$xxx and dba_recyclebin was empty (回收站空，释放无效的BIN$xx空间)

Invalid segment BIN$xxx and dba_recyclebin was empty (回收站空，释放无效的BIN$xx空间)

  • 2015/05/14
  • ORACLE 9i-23ai
  • 575 views
  • Invalid segment BIN$xxx and dba_recyclebin was empty (回收站空，释放无效的BIN$xx空间)已关闭评论

近来有套库空间紧张，发现有很大BIN$开头的TABLE partition,index partition 类型的段，查询确认是2个月前删除的对象，手动清空过dba_recyclebin使用purge，但都过去几天了，后来dba_recyclebin一直为空，发现对象BIN$XX还存在，ORACLE 在处理大的分区表时在开启RECYCLEBIN的情况有时会出现这种异常情况，下面记录一下这个CASE。

SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0	 Production
TNS for HPUX: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production
SQL> select bytes,segment_type,owner,tablespace_name,segment_name from dba_segments where segment_name like 'BIN$%'
      BYTES SEGMENT_TYPE	  OWNER		 TABLESPACE_NAME			 SEGMENT_NAME
-------------------- ------------------ --------------- ------------------------------ ------------------------------
    5,006,950,400 INDEX PARTITION    CDR		   CDR_INX7				   BIN$Du34GVECb4zgVAAfKQ3k2w==$0
   13,659,799,552 INDEX PARTITION    CDR		   CDR3					  BIN$Du34GVECb4zgVAAfKQ3k2w==$0
     1,048,576 INDEX PARTITION    CDR		   CDR3					  BIN$Du34GVECb4zgVAAfKQ3k2w==$0
   80,321,970,176 TABLE PARTITION    CDR		   CDR_INX5				   BIN$Du34GVEDb4zgVAAfKQ3k2w==$0
   84,739,620,864 TABLE PARTITION    CDR		   CDR4					  BIN$Du34GVEDb4zgVAAfKQ3k2w==$0
   80,260,104,192 TABLE PARTITION    CDR		   CDR_T1					BIN$Du34GVEDb4zgVAAfKQ3k2w==$0
   80,747,692,032 TABLE PARTITION    CDR		   CDR_INX8				   BIN$Du34GVEDb4zgVAAfKQ3k2w==$0
...
select object_id from dba_objects where object_name like 'BIN%';
--省略
SQL> @oid 578398
owner				 object_name				object_type	   SUBOBJECT_NAME			  CREATED		 LAST_DDL_TIME	status    DATA_OBJECT_ID
------------------------- ------------------------------ ------------------ ------------------------------ ----------------- ----------------- --------- --------------
CDR				   BIN$Du34GVEDb4zgVAAfKQ3k2w==$0 TABLE PARTITION    GPRS312_MAX				20140722 22:27:02 20140828 17:14:30 VALID		   592609
SQL> select * from dba_tables where table_name='BIN$Du34GVEDb4zgVAAfKQ3k2w==$0';
no rows selected
SQL> select * from dba_tables where table_name like 'BIN%';
no rows selected
SQL> select 1 from cdr."BIN$Du34GVEDb4zgVAAfKQ3k2w==$0" partition(GPRS312_01) where rownum<2;
      1
----------
      1
SQL> select * from dba_recyclebin;
no rows selected
SQL> select count(*) from dba_tab_partitions where table_name='BIN$Du34GVEDb4zgVAAfKQ3k2w==$0';
  COUNT(*)
----------
     31

TIP:

当分区表在RECYCLEBIN开启的情况下：

drop N partition ,partitioon 会直接删除而不会进recyclebin

drop table, 表信息会从DBA_TABLES 消失，dba_tab_partition 重命名为BIN$开头的对象，其它TRIGER,INDEX同类同，但同时会在DBA_RECYCLEBIN中记录table的BIN 和原表名及删除时的一些信息,而且可以使用BIN$开头的表名查询表记录。

试着去删除

SQL> purge index cdr."BIN$Du34GVECb4zgVAAfKQ3k2w==$0";
purge index cdr."BIN$Du34GVECb4zgVAAfKQ3k2w==$0"
*
ERROR at line 1:
ORA-38307: object not in RECYCLE BIN

SQL> drop table cdr."BIN$Du34GVEDb4zgVAAfKQ3k2w==$0";
drop table cdr."BIN$Du34GVEDb4zgVAAfKQ3k2w==$0"
               *
ERROR at line 1:
ORA-38301: can not perform DDL/DML over objects in Recycle Bin

解决方法：

SQL> ALTER SESSION SET RECYCLEBIN=OFF;
Session altered.

SQL> drop table cdr."BIN$Du34GVEDb4zgVAAfKQ3k2w==$0";
Table dropped.

如果session级关闭recyclebin不可以,可以尝试在实例级关闭或重启实例后再次尝试删除，本次清理释放了2T的空间。

O对删除大分区表时的最佳实践：Demantra Large Table Partitions and Using the Flashback Recycle bin, recyclebin, dba_recyclebin and sys.RECYCLEBIN$ Purge Best Practice (文档 ID 1962730.1)

Related Posts:

打赏

对不起，这篇文章暂时关闭评论。

上一篇：

下一篇：