oracle删除不掉type,如何删除Oracle LOB

您可以在user_objects中看到LOB但是对user_lobs的连接没有找到任何内容的一种情况是该表是否已被删除,但是

is in the recycle bin.

create table t42 (my_clob clob);

table T42 created.

正如预期的那样,Justin的查询显示了专栏：

select l.table_name,l.column_name,l.segment_name lob_name

from user_lobs l

join user_objects o

on( o.object_name = l.segment_name );

TABLE_NAME COLUMN_NAME LOB_NAME

----------- ----------- ------------------------------

T42 MY_CLOB SYS_LOB0000133310C00001$$

drop table t42;

table T42 dropped.

现在贾斯汀的查询没有找到任何东西：

select l.table_name,l.segment_name lob_name

from user_lobs l

join user_objects o

on( o.object_name = l.segment_name );

no rows selected

但它仍然在user_objects中：

select object_name,object_type,status from user_objects

where object_type like 'LOB%';

OBJECT_NAME OBJECT_TYPE STATUS

------------------------------ ------------------- -------

SYS_LOB0000133328C00001$$ LOB VALID

你可以在回收站中看到它：

select * from user_recyclebin;

OBJECT_NAME ORIGINAL_NAME OPERATION TYPE TS_NAME CREATETIME DROPTIME DROPSCN PARTITION_NAME CAN_UNDROP CAN_PURGE RELATED BASE_OBJECT PURGE_OBJECT SPACE

------------------------------ -------------------------------- --------- ------------------------- ------------------------------ ------------------- ------------------- ---------- -------------------------------- ---------- --------- ---------- ----------- ------------ ----------

SYS_IL0000133310C00001$$ SYS_IL0000133310C00001$$ DROP LOB INDEX USERS 2013-08-22:08:33:21 2013-08-22:08:33:21 1.0E+13 NO NO 133310 133310 133310 0

SYS_LOB0000133310C00001$$ SYS_LOB0000133310C00001$$ DROP LOB USERS 2013-08-22:08:33:21 2013-08-22:08:33:21 1.0E+13 NO NO 133310 133310 133310 0

BIN$5IUNXtWkUXLgQwEAAH9TlQ==$0 T42 DROP TABLE USERS 2013-08-22:08:33:21 2013-08-22:08:33:21 1.0E+13 YES YES 133310 133310 133310 0

LOB仍然存在于磁盘上并且正在使用存储,我想这就是你所关心的.因此,要回答您的问题,要真正删除LOB并释放其存储,您需要清除整个表：

purge table t42;

table purged.

select object_name,status from user_objects

where object_type like 'LOB%';

no rows selected

有趣的是,如果您命名LOB段,则不会看到此效果：

create table t42 (my_clob clob)

lob (my_clob) store as my_clob_segment;

重复上述步骤后,条目在删除后从user_objects开始.

drop table t42;

table T42 dropped.

select object_name,status from user_objects

where object_type like 'LOB%';

no rows selected

select * from user_recyclebin;

OBJECT_NAME ORIGINAL_NAME OPERATION TYPE TS_NAME CREATETIME DROPTIME DROPSCN PARTITION_NAME CAN_UNDROP CAN_PURGE RELATED BASE_OBJECT PURGE_OBJECT SPACE

------------------------------ -------------------------------- --------- ------------------------- ------------------------------ ------------------- ------------------- ---------- -------------------------------- ---------- --------- ---------- ----------- ------------ ----------

BIN$5IUNXtWnUXLgQwEAAH9TlQ==$0 MY_CLOB_SEGMENT DROP LOB USERS 2013-08-22:08:36:41 2013-08-22:08:36:41 1.0E+13 NO NO 133316 133316 133316 0

BIN$5IUNXtWoUXLgQwEAAH9TlQ==$0 T42 DROP TABLE USERS 2013-08-22:08:36:41 2013-08-22:08:36:41 1.0E+13 YES YES 133316 133316 133316 0

SYS_IL0000133316C00001$$ SYS_IL0000133316C00001$$ DROP LOB INDEX USERS 2013-08-22:08:36:41 2013-08-22:08:36:41 1.0E+13 NO NO 133316 133316 133316 0

当然,存储仍在使用,您仍需要清除以释放它,它在数据字典中看起来更加一致.所以这看起来像一个(非常小的)bug,也许最多.它可能与支持说明394442.1中提到的行为有关.