Purging LOBs:

If we purge a table which contains LOBs , we may not reclaim space. BLOBs are stored out of line once they exceed 4,000 bytes (you can also specify that they are always stored out of line as well).

If the BLOBs were all inline, each taking 4000 or less bytes, then you did free space in your table.  The table will not shrink in size (tables NEVER shrink) but it will have more blocks on its free list for subsequent inserts.

SQL> desc applsys.fnd_lobs
Name                                      Null?    Type
—————————————– ——– —————————-
FILE_ID                                   NOT NULL NUMBER
FILE_NAME                                          VARCHAR2(256)
FILE_CONTENT_TYPE                         NOT NULL VARCHAR2(256)
FILE_DATA                                          BLOB
UPLOAD_DATE                                        DATE
EXPIRATION_DATE                                    DATE
PROGRAM_NAME                                       VARCHAR2(32)
PROGRAM_TAG                                        VARCHAR2(32)
LANGUAGE                                           VARCHAR2(4)
ORACLE_CHARSET                                     VARCHAR2(30)
FILE_FORMAT                               NOT NULL VARCHAR2(10)

SQL> select round(sum(bytes)/1024/1024) Mb from dba_segments where segment_name=’FND_LOBS’;

MB
———-
179

SQL> select round(sum(bytes)/1024/1024) Mb from dba_segments where segment_name=’SYS_LOB0000034032C00004$$’;

MB
———-
20699

SQL> select OBJECT_NAME,OBJECT_ID,OWNER,OBJECT_TYPE,STATUS from dba_objects where object_name=’SYS_LOB0000034032C00004$$’ and object_type=’LOB’;

OBJECT_NAME
——————————————————————————–
OBJECT_ID OWNER                          OBJECT_TYPE         STATUS
———- —————————— ——————- ——-
SYS_LOB0000034032C00004$$
34033 APPLSYS                        LOB                 VALID

SQL> select OWNER,TABLE_NAME,COLUMN_NAME,SEGMENT_NAME,TABLESPACE_NAME from dba_lobs where SEGMENT_NAME=’SYS_LOB0000034032C00004$$’;

OWNER                          TABLE_NAME
—————————— ——————————
COLUMN_NAME
——————————————————————————–
SEGMENT_NAME                   TABLESPACE_NAME
—————————— ——————————
APPLSYS                        FND_LOBS
FILE_DATA
SYS_LOB0000034032C00004$$      APPS_TS_MEDIA

Action Plan
===========
1) Take hotbackup for the instance
2) Execute the following commands as sysdba

SQL> delete from APPLSYS.FND_LOBS
SQL> alter table applsys.FND_LOBS modify lob (FILE_DATA) (shrink space);

3) Then Execute the following commands to confirm the space gained.

SQL> select round(sum(bytes)/1024/1024) Mb from dba_segments where segment_name=’FND_LOBS’;
SQL> select round(sum(bytes)/1024/1024) Mb from dba_segments where segment_name=’SYS_LOB0000034032C00004$$’;

NOTE: It should return “0” for both the commands.

Or

Update the LOB column value with NULL and then execute the following command.

SQL> alter table applsys.FND_LOBS modify lob (FILE_DATA) (shrink space);

OR

Use the DBMS_LOB.ERASE   package and erase the LOBs. Then execute the following command ..

SQL> alter table applsys.FND_LOBS modify lob (FILE_DATA) (shrink space);

In all the case it is compulsory to use the ” alter table ..” command  claim the space.