Repeating the steps above, the entry has gone from user_objects after the drop. Interestingly you don't see this effect if you name the LOB segment: create table t42 (my_clob clob) Select object_name, object_type, status from user_objects

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 The LOB still exists on disk and is using storage, which I guess is what you're concerned about. OBJECT_NAME ORIGINAL_NAME OPERATION TYPE TS_NAME CREATETIME DROPTIME DROPSCN PARTITION_NAME CAN_UNDROP CAN_PURGE RELATED BASE_OBJECT PURGE_OBJECT SPACE Now Justin's query doesn't find anything: select l.table_name,īut it's still in user_objects: select object_name, object_type, status from user_objectsĪnd you can see it in the recycle bin: select * from user_recyclebin create table t42 (my_clob clob) Īs expected, Justin's query shows you the column: select l.table_name,

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.

Oracle dropbox logo