How to Drop an Orphan LOB in Oracle Database

In Oracle 11.2.0.3 database, after dropped some tables, there are still lob objects in dba_objects and dba_segments.

Find the table name the lob belongs to:

SQL> show parameter recyclebin

NAME                                 TYPE        VALUE
------------------------------------ ----------- -----------------------
recyclebin                           string      on

SQL> select u.name, o.name TABLENAME, decode(bitand(c.property, 1), 1, ac.name, c.name) as column_name
  from sys.obj$ o, sys.col$ c, sys.attrcol$ ac,sys.lob$ l,sys.obj$ lo,sys.obj$ io,
sys.user$ u,sys.ts$ ts
 where o.owner# = u.user#
and o.obj# = c.obj#
 and c.obj# = l.obj# and c.intcol# = l.intcol#
and l.lobj# = lo.obj# and l.ind# = io.obj# and l.ts# = ts.ts# and c.obj# =
ac.obj#(+)
 and c.intcol# = ac.intcol#(+) and lo.name ='SYS_LOB0000542255C00004$$';

NAME                           TABLENAME
------------------------------ ------------------------------
COLUMN_NAME
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
APP_USER                    BIN$FvVDUKyLUJLgYyshAgoDnQ==$0
DATATBS

SQL>  purge table APP_USER."BIN$FvVDUKwcUJLgYyshAgoDnQ==$0";

Table purged.

The the lob will dropped successfully.

In order to avoid this problem, for databases with recyclebin turned ON, we can:

SQL> drop table APP_USER.TABLENAME PURGE;