如何删除 Oracle LOB
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18367444/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to drop Oracle LOB
提问by byneri
The following query can be used to list the database objects of the user:
以下查询可用于列出用户的数据库对象：
select object_name, object_type from user_objects;
There are couple of entries where the object_type is LOB.
有几个条目的 object_type 是 LOB。
How can these LOB objects be dropped in Oracle?
在 Oracle 中如何删除这些 LOB 对象？
回答by Alex Poole
One scenario where you can see a LOB in user_objectsbut the join to user_lobsdoesn't find anything is if the table has already been dropped, but is in the recycle bin.
您可以在其中看到 LOBuser_objects但连接到user_lobs找不到任何内容的一种情况是，如果表已被删除，但在回收站中。
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
But it's still in user_objects:
但它仍然在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
BINIUNXtWkUXLgQwEAAH9TlQ==purge table t42;
table purged.
select object_name, object_type, status from user_objects
where object_type like 'LOB%';
no rows selected
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
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:
LOB 仍然存在于磁盘上并且正在使用存储，我想这就是您所关心的。因此，要回答您的问题，要真正删除 LOB 并释放其存储，您需要清除整个表：
create table t42 (my_clob clob)
lob (my_clob) store as my_clob_segment;
Interestingly you don't see this effect if you name the LOB segment:
有趣的是，如果您命名 LOB 段，则不会看到这种效果：
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
------------------------------ -------------------------------- --------- ------------------------- ------------------------------ ------------------- ------------------- ---------- -------------------------------- ---------- --------- ---------- ----------- ------------ ----------
BINIUNXtWnUXLgQwEAAH9TlQ==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 )
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
BINIUNXtWoUXLgQwEAAH9TlQ==##代码## 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
Repeating the steps above, the entry has gone from user_objectsafter the drop.
重复以上步骤，该条目已经从去user_objects后drop。
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.
当然，存储仍在使用中，您仍然需要清除以释放它，它只是在数据字典中看起来更加一致。所以这看起来最多是一个（非常小的）错误。它可能与支持说明 394442.1 中提到的行为有关。
回答by Justin Cave
The LOB object will be dropped if and when you drop the table that contains the associated LOB column or drop the LOB column from that table. You can see what column a particular LOB object supports by querying DBA_LOBS, ALL_LOBS, or USER_LOBSdepending on your privileges.
如果删除包含关联 LOB 列的表或从该表中删除 LOB 列，则将删除 LOB 对象。您可以通过查询看到列的特定LOB对象支持DBA_LOBS，ALL_LOBS或者USER_LOBS根据您的权限。
For example
例如
##代码##will show you what table and what column each of the LOBobjects in your schema supports.
将向您显示LOB架构中的每个对象支持的表和列。
