回收站清空後在obj$還能找到的bug

回收站的物件清除之後，在obj$和dba_segments裡還能找到被清除的物件，但在dba_recyclebin裡已經沒有了。


這是一個未公開的bug，  可以在刪除物件時加 purge或關閉回收站解決這個問題


BIN$ Rows In Sys.obj$ After Purge Of Recyclebin (Doc ID 1506286.1)
In this Document
Symptoms
Changes
Cause
Solution
APPLIES TO:


Oracle Server - Enterprise Edition - Version 10.2.0.4 to 11.2.0.2.0 [Release 10.2 to 11.2]
Information in this document applies to any platform.
SYMPTOMS


After partitioned table dropped, and recyclebin purged obj$ and dba_segments show the dropped table as BIN$...., and space is not released. 


select owner,object_name from dba_recyclebin; 


no rows selected 


SQL> select owner#,name,status from sys.obj$ where name like '%BIN$%'; 


   OWNER# NAME                               STATUS 
---------- ------------------------------ ---------- 
       92 BIN$iF05x2CIYJbgQwoJMvpglg==$0          1 
       92 BIN$iF05x2CIYJbgQwoJMvpglg==$0          1 
       92 BIN$iF05x2CIYJbgQwoJMvpglg==$0          1 
.... 


SQL> select owner,segment_name,segment_type from dba_segments where segment_name like 'BIN$%'; 


OWNER                          SEGMENT_NAME                                                                      SEGMENT_TYPE 
------------------------------ --------------------------------------------------------------------------------- ------------------ 
SYSADM                         BIN$iF05x2CIYJbgQwoJMvpglg==$0                                                    TABLE PARTITION 
SYSADM                         BIN$iF05x2CIYJbgQwoJMvpglg==$0                                                    TABLE PARTITION 
SYSADM                         BIN$iF05x2CIYJbgQwoJMvpglg==$0                                                    TABLE PARTITION 
........ 


Same entries in dba_tab_partitions.


CHANGES


 Partitioned table dropped.


CAUSE


 Unpublished Bug 6450822: RECYCLEBIN: DROPPED PARTITIONED TABLES SHOW UP IN DBA_TAB_PARTITIONS


Recyclebin feature is transparent for non-partitioned tables, but it is not transparent for partitioned tables.
Bug is still under development, is it not solved yet.


In order to avoid this bug:


1. disable recyclebin       ---&gt     recyclebin = OFF


or


2. Use PURGE option when dropping partition table


   drop table purge;
SOLUTION


To fix this inconsistency if happened:


1) select distinct  table_name,tablespace_name from dba_tab_partitions where table_owner='SYSADM' and table_name like 'BIN$%'; 


2) For each table returned by 1 issue 


drop table sysadm."BIN$....." purge; 


Eg: drop table SYSADM."BIN$iF05x2CIYJbgQwoJMvpglg==$0" purge;

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/25099483/viewspace-1071232/，如需轉載，請註明出處，否則將追究法律責任。