关于删除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博客 ” ，链接：https://blog.itpub.net/29102572/viewspace-2122509/ ，如需转载，请注明出处，否则将追究法律责任。
