How to remove Oracle LOB

Question

The following query can be used to display user database objects:

select object_name, object_type from user_objects; 

There are several entries in which object_type is a LOB.

How can these LOB objects be deleted in Oracle?

Answer 1

One scenario where you can see the LOB in user_objects , but the connection to user_lobs does not find anything if the table has already been deleted but is in the trash .

 create table t42 (my_clob clob); table T42 created. 

As expected, the Justin query shows you a 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 finds nothing:

 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 is still in 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 basket:

 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 still exists on disk and uses storage, which I think is what bothers you. Therefore, in order to answer your question, to really abandon the LOB and free up its storage, you need to clear the entire table:

 purge table t42; table purged. select object_name, object_type, status from user_objects where object_type like 'LOB%'; no rows selected 

---

Interestingly, you do not see this effect if you name the LOB segment:

 create table t42 (my_clob clob) lob (my_clob) store as my_clob_segment; 

Repeating the above steps, the record went through user_objects after 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 

The storage is still in use, of course, and you still need to clear it, it just looks a little more consistent in the data dictionary. So it looks like a (very minor) error, maybe at most. This may be due to the behavior mentioned in support note 394442.1.

Answer 2

The LOB object will be deleted if and when you delete the table containing the corresponding LOB column, or release the LOB column from this table. You can see which column supports a specific LOB object by 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 which table and which column each of the LOB objects in your schema supports.