关于删除LOB类型的一个小bug

版本:11.2.0.4

当drop了含LOB类型的table之后，在user_lobs数据字典中已经查不到这个字段，但在SELECT * FROM User_Objects WHERE object_type='LOB';却还是能查到，这个应该是一个小bug，referred to in support note 394442.1.

SQL> SELECT * FROM User_Objects WHERE object_type='LOB';
 
OBJECT_NAME                                                                      SUBOBJECT_NAME                  OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE         CREATED     LAST_DDL_TIME TIMESTAMP           STATUS  TEMPORARY GENERATED SECONDARY  NAMESPACE EDITION_NAME
-------------------------------------------------------------------------------- ------------------------------ ---------- -------------- ------------------- ----------- ------------- ------------------- ------- --------- --------- --------- ---------- ------------------------------
SYS_LOB0001061333C00036$$                                                                                          1061334        1061334 LOB                 1/4/2016 3: 1/4/2016 3:13 2016-01-04:15:13:58 VALID   N         Y         N                  8
SYS_LOB0001061401C00007$$                                                                                          1061402        1061402 LOB                 1/4/2016 3: 1/4/2016 3:14 2016-01-04:15:14:16 VALID   N         Y         N                  8
SYS_LOB0001061405C00007$$                                                                                          1061406        1061406 LOB                 1/4/2016 3: 1/4/2016 3:14 2016-01-04:15:14:18 VALID   N         Y         N                  8
SYS_LOB0001060488C00002$$                                                                                          1060489        1060489 LOB                 1/4/2016 3: 1/4/2016 3:02 2016-01-04:15:02:40 VALID   N         Y         N                  8
SYS_LOB0001059670C00003$$                                                                                          1059671        1059671 LOB                 1/4/2016 2: 1/4/2016 2:50 2016-01-04:14:50:22 VALID   N         Y         N                  8
SYS_LOB0001059681C00009$$                                                                                          1059682        1059682 LOB                 1/4/2016 2: 1/4/2016 2:50 2016-01-04:14:50:42 VALID   N         Y         N                  8
SYS_LOB0001059691C00003$$                                                                                          1059692        1059692 LOB                 1/4/2016 2: 1/4/2016 2:50 2016-01-04:14:50:52 VALID   N         Y         N                  8
SYS_LOB0001059820C00013$$                                                                                          1059823        1059823 LOB                 1/4/2016 2: 1/4/2016 2:53 2016-01-04:14:53:06 VALID   N         Y         N                  8
SYS_LOB0001059820C00009$$                                                                                          1059821        1059821 LOB                 1/4/2016 2: 1/4/2016 2:53 2016-01-04:14:53:06 VALID   N         Y         N                  8
SYS_LOB0001059829C00036$$                                                                                          1059830        1059830 LOB                 1/4/2016 2: 1/4/2016 2:53 2016-01-04:14:53:08 VALID   N         Y         N                  8
 
10 rows selected
 
SQL>

 

SQL> SELECT * FROM User_Lobs;
 
TABLE_NAME                     COLUMN_NAME                                                                      SEGMENT_NAME                   TABLESPACE_NAME                INDEX_NAME                          CHUNK PCTVERSION  RETENTION  FREEPOOLS CACHE      LOGGING ENCRYPT COMPRESSION DEDUPLICATION   IN_ROW FORMAT          PARTITIONED SECUREFILE SEGMENT_CREATED RETENTION_TYPE RETENTION_VALUE
------------------------------ -------------------------------------------------------------------------------- ------------------------------ ------------------------------ ------------------------------ ---------- ---------- ---------- ---------- ---------- ------- ------- ----------- --------------- ------ --------------- ----------- ---------- --------------- -------------- ---------------
 
SQL>

其实，在drop掉table之后，lob类型也进入了回收站，所以，解决这个问题的方式，就是在回收站中purge掉table，或者purge一下整个回收站。

SQL>
SQL> PURGE recyclebin;
 
Done
 
SQL>
SQL> SELECT * FROM User_Objects WHERE object_type='LOB';
 
OBJECT_NAME                                                                      SUBOBJECT_NAME                  OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE         CREATED     LAST_DDL_TIME TIMESTAMP           STATUS  TEMPORARY GENERATED SECONDARY  NAMESPACE EDITION_NAME
-------------------------------------------------------------------------------- ------------------------------ ---------- -------------- ------------------- ----------- ------------- ------------------- ------- --------- --------- --------- ---------- ------------------------------
 
SQL>

purge之后，再次查询这个视图，就查不到了。

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

转载于:http://blog.itpub.net/29102572/viewspace-2122509/