How to drop Oracle LOB

Question

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?

Ashcraft

Answer 1

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

But it's 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 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 

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

---

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;

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 

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.

Indecorum

Thanks for the detailed explaination and sample queries – Ashcraft

+1. Very nice. Thank your for the concise (and readable) answer! – Guyguyana

Answer 2

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.

Firefly

The above query returned 0 rows for my user. – Ashcraft

Also 0 rows when querying all_lobs (instead of user_lobs). Cannot query dba_lobs: "ORA-00942: table or view does not exist" – Ashcraft