如何删除Oracle LOB [英] How to drop Oracle LOB
问题描述
以下查询可用于列出用户的数据库对象:
The following query can be used to list the database objects of the user:
select object_name, object_type from user_objects;
有一些条目的object_type为LOB.
There are couple of entries where the object_type is LOB.
如何在Oracle中删除这些LOB对象?
How can these LOB objects be dropped in Oracle?
推荐答案
在一种情况下，您可以在user_objects中看到一个LOB，但是与user_lobs的连接找不到任何东西是如果表已经被删除，但是位于回收站中.
One scenario where you can see a LOB in user_objects but the join to user_lobs doesn't find anything is if the table has already been dropped, but is in the recycle bin.
create table t42 (my_clob clob);
table T42 created.
正如预期的那样，贾斯汀的查询向您显示该列:
As expected, Justin's query shows you the column:
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.
现在，贾斯汀的查询找不到任何内容:
Now Justin's query doesn't find anything:
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 );
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
您可以在回收站中看到它:
And you can see it in the recycle bin:
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并释放其存储空间，您需要清除整个表:
The LOB still exists on disk and is using storage, which I guess is what you're concerned about. So to sort of answer your question, to really drop the LOB and release its storage you need to purge the whole table:
purge table t42;
table purged.
select object_name, object_type, status from user_objects
where object_type like 'LOB%';
no rows selected
有趣的是，如果您命名LOB段，您不会看到这种效果:
Interestingly you don't see this effect if you name the LOB segment:
create table t42 (my_clob clob)
lob (my_clob) store as my_clob_segment;
重复上述步骤，条目从user_objects之后的user_objects消失.
Repeating the steps above, the entry has gone from user_objects after the drop.
drop table t42;
table T42 dropped.
select object_name, object_type, 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
当然，该存储仍在使用中，您仍然需要清除以释放它，它在数据字典中看起来更加一致.因此，这似乎最多是一个(非常小的)错误.这可能与支持说明394442.1中提到的行为有关.
The storage is still being used of course and you still need to purge to free it, it just looks a bit more consistent in the data dictionary. So this looks like a (very minor) bug, maybe, at most. It might be related to the behaviour referred to in support note 394442.1.
这篇关于如何删除Oracle LOB的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
