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.
How can these LOB objects be dropped in Oracle?
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_LOBS
depending on your privileges.
For example
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 )
will show you what table and what column each of the LOB
objects in your schema supports.
One scenario where you can see a LOB in
user_objects
but the join touser_lobs
doesn't find anything is if the table has already been dropped, but is in the recycle bin.As expected, Justin's query shows you the column:
Now Justin's query doesn't find anything:
But it's still in
user_objects
:And you can see it in the recycle bin:
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:
Interestingly you don't see this effect if you name the LOB segment:
Repeating the steps above, the entry has gone from
user_objects
after thedrop
.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.