1 Reply Latest reply on Nov 15, 2015 5:22 PM by Bashar.

    Oracle EBS 12: FND_LOBS table - purge attachments

    user1987306

      Good morning!

      Guys, please provide me your wise advice.

       

      Scenario:

      OS: RHEL 5.7 x86_64

      Oracle DB: 11gR1 64 bits (11.1.0.7.0)

      Oracle applications (EBS): 12.1.3

       

      I have worked with other instances of Oracle applications, the “basic” administrative tasks. This time I have assigned to solve a situation related to the APPS_TS_MEDIA tablespace.

       

      The APPS_TS_MEDIA tablespace is huge, this is the detail:

      Table and size: FND_LOBS / 1025.375 MB / 6,554,236 rows

      LOB segment and size: SYS_LOB0000034032C00004$$ / 274.27 GB

      LOB index and size: SYS_IL0000034032C00004$$ / 70.5 MB

       

      I have queried the related tables to get a quick view of the attached documents, these are the results:

       

      By file type:

      .out : 7,154 records.

      .doc or .docx : 2 records.

      .xls or .xlsx : 34 records.

      .pdf : 18,760 records.

      .xml : 6,472,313 records.

       

      By date of creation:

      Before Jan 1st 2015

      4,145,227

      After Jan 1st 2015

      2,409,009

       

      I want to delete (purge) all of the files whose creation date is before Jan 1st 2015.

       

      1. I have read about a concurrent program to purge the FND_LOBS table, but the EXPIRATION_DATE field is null, what can I do? Is there any MOS document about the procedure?
      2. There are a lot of attachments (.xml files) created by SYSADMIN user, should I purge this files?

       

      PS. After purge the attachments I want to reclaim the unused space, but I will ask about it after achieve purge the files.

       

      Thanks for your time and help!