How to drop Lobs

Lobs can be dropped using alter table command.
A particular column which is of LOB type, can be dropped using

ALTER TABLE DROP COLUMN ;

It can also be dropped using DROP TABLE command

10g onwards, if you drop a table, it goes to the recyclebin. So dropped lob objects are still visible.

I dropped all the objects using,

select 'DROP ' object_type ' ' owner '.' object_name ';'
from dba_objects
where owner in (upper('&UserName'))
and object_type in ('TABLE', 'VIEW', 'PROCEDURE', 'FUNCTION', 'PACKAGE', 'SEQUENCE', 'TRIGGER')
order by object_id desc;

But then, lobs were still present.


select count(1) from dba_objects where owner = 'RCUSER';
COUNT(1)
----------
84
1 row selected.

These were coming from recyclebin. So to clear the recycle bin for all users


SQL> purge dba_recyclebin;
DBA Recyclebin purged.

After this lobs were not found.


SQL> select count(1) from dba_objects where owner = 'RCUSER';
COUNT(1)
----------
0
1 row selected.