Oracle eBS經驗分享

In this Document

Applies to:

Goal

How to purge attachments in with no expiration date to reduce the size of the FND_LOBS LOBSEGMENT?

Per MOS document How To Purge FND_LOBS "export" Records With No EXPIRATION_DATE, ran the program "Purge Obsolete Generic File Manager Data" in the following scenarios:

• With parameters Expired => No & Program Name => export.  It completed file and purged records from FND_LOBS for Program "export" and program "FND_HELP"
• But, with parameters Expired => No & Program Name => FNDATTCH , is NOT deleting records from FND_LOBS for program "FNDATTCH".

Even after the executions described, the FND_LOBS table still storing hundreds of gigabytes of space and the LOBSEGMENT linked to table FND_LOB occupies hundreads of gigabytes.  The following SQL from MOS document FAQ - Performance Considerations For FND_LOBS shows this:

PROGRAM_NAME    COUNT(0)   SIZE_GB
--------------- -------- ---------
FNDATTCH          865331     416.7
                  175993      18.8

OWNER    OBJECT_NAME                OBJECT_TYPE CREATED    TABLESPACE_NAME  SUM(B.BYTES/1024/1024/1024)
-------- -------------------------- ----------- ---------- --------------- ----------------------------
APPLSYS  SYS_LOB0000034032C00004$$  LOB         14-MAY-00  APPS_TS_MEDIA                     674.401855

Solution

Table FND_LOBS

FND_LOBS stores information about all LOBs managed by the Generic File Manager (GFM). Each row includes the file identifier, name, content-type, and actual data.
Each row also includes the dates the file was uploaded and will expire, the associated program name and tag, and the language and Oracle characterset.

The file data, which is a binary LOB, is stored exactly as it is uploaded from a client browser, which means that no translation work is required during a download to
make it HTTP compliant. Therefore uploads from non-browser sources will have to prepare the contents appropriately (for instance, separating lines with CRLF).

The program_name and program_tag may be used by clients of the GFM for any purpose, such as striping, partitioning, or purging the table if the program is de-installed.
They are otherwise strictly informative. These columns and the expiration date are properly set when the procedure FND_GFM.CONFIRM_UPLOAD is called.
If not called, the column expiration_date remains set, and will eventually be purged by the procedure FND_GFM.PURGE_EXPIRED.

Table Structure:

Many DISTINCT values in column PROGRAM_NAME, FNDATTCH is among them:

Table FND_DOCUMENTS

This table stores language-independent information about a document. For example, each row contains a document identifier, a category identifier, the method of security used for the document (SECURITY_TYPE, where 1=Organization, 2=Set of Books, 3=Business unit, 4=None), the period in which the document is active, and a flag to indicate whether or not the document can be shared outside of the security type (PUBLISH_FLAG).

Other specifications in this table include: datatype (DATATYPE_ID, where 1=short text, 2=long text, 3=image, 4=OLE object, 5=Web Page, 6=File, etc)

The document can be referenced by many application entities and changed only in the define document form (USAGE_TYPE=S); it can be used as a fill-in-the-blanks document, where each time you use a template, you make a copy of it (USAGE_TYPE=T); or it can be used only one time (USAGE_TYPE=O). Images and OLE Objects cannot be used as templates.

These documents are the ones linked to an EBS entity/transaction, like for example, a Payables Invoice or Payment, or a Purchasing Purchase Order, etc.

FND_LOBS links to FND_DOCUMENTS via the MEDIA_ID column (to match FND_LOBS FILE_ID).

Table FND_ATTACHED_DOCUMENTS

This table stores information relating a document to an application entity. For example, a record may link a document to a sales order or an item. Each row contains foreign keys to FND_DOCUMENTS and FND_DOCUMENT_ENTITIES.  FND_DOCUMENT_ENTITIES lists each entity to which attachments can be linked. For example, attachments can be linked to Items, Sales Orders, Invoices, etc.

Per above, the following SQL should show the list of entities related to Payables (AP):

ENTITY_NAME                TABLE_NAME
-------------------------- ------------------------------
AP_CHECKS                  AP_CHECKS
AP_INVOICES                AP_INVOICES
                           PO_VENDOR_SITES
                           AP_EXPENSE_REPORT_HEADERS
                           AP_INVOICES_INTERFACE
                           AP_RECURRING_PAYMENTS
                           AP_INVOICES
PO_VENDORS                 PO_VENDORS
8 rows selected.

This shows how all documents of entity_name AP_INVOICES, refer to attachments to Invoices, Expense Reports, Recurring Payments, etc.

Concurrent program "Purge Obsolete Generic File Manager Data" - FNDGFMPR

Concurrent parameters are:

• Expired: Yes/No.
• Program Name:  The distinct program names stored in table FND_LOBS.  POssible values are: FNDATTCH, FND_HELP, PERWSIMGm, etc.
• Program Tag:  Also, the distinct progam tags sotred in table FND_LOBS for a given "Program Name".
• Purge Orphaned:  Yes/No.

Concurrent Executable is the PLSQL call to Server-side API FND_GFM.Purge.  Package FND_GFM source files are:

• Package Body - $FND_TOP/patch/115/sql/AFGFMB.pls
• Package Specification - $FND_TOP/patch/115/sql//AFGFMS.pls

When the concurrent program FNDGFMPR executes, API FND.GFM.Purge calls procedure fnd_gfm.purge_expired or procedure fnd_gfm.purge_set as follow:

• IF concurrent program is called with program name FNDATTCH and Expired parameter set to anything other than "No" (N)
  THEN it calls fnd_gfm.purge_expired ( 'FNDATTCH', [Purge Orphanded] ); 
• ELSE it calls fnd_gfm.purge_set ( 'FNDATTCH', [Program Tag] );

So to the following to reduce the FND_LOBS LOBSEGMENT size:

1. Run concurrent "Purge Obsolete Generic File Manager Data" executed with parameters:
   Program Name = FNDATTCH,  Expired = Yes, and Purge Orphaned = Yes
2. To confirm the effect of this, run the following SQL:
   SELECT count(*)
     FROM FND_LOBS FL 
    WHERE NOT EXISTS (SELECT '1'  FROM FND_DOCUMENTS FD  WHERE FD.MEDIA_ID = FL.FILE_ID  AND FD.DATATYPE_ID = 6)
      AND PROGRAM_NAME = 'FNDATTCH'
      AND EXPIRATION_DATE IS NULL ;
   For the particular case that originated this MOS document, this step removed 3175 records.
    
   IMPORTANT:  The records removed by the program are records that have NO link or reference to a particular Application Document (Table FND_DOCUMENTS). Hence, the "NOT EXIST" condition in the SQL.
3. Now, run the following SQL to check for records in FND_LOBS linked to a FND_DOCUMENT.  Notice we removed the "NOT" from the "NOT EXISTS" statement:  
   SELECT count(*) 
     FROM FND_LOBS FL 
    WHERE EXISTS (SELECT '1'  FROM FND_DOCUMENTS FD  WHERE FD.MEDIA_ID = FL.FILE_ID  AND FD.DATATYPE_ID = 6) 
      AND PROGRAM_NAME = 'FNDATTCH'  AND EXPIRATION_DATE IS NULL ;  
   For the particular case that originated this MOS document, this SQL returned almost 900K records.  This is 900K records in FND_LOBS linked to real EBS Applications Documents Files (DataType= 6) that belong to an EBS application module - transaction/entity, like a Payables Invoice or a Purchasing PO.
4. Check on these transactions "Upload Dates" (since the expiration date is NULL), using: 
   SELECT COUNT(*), TO_CHAR(upload_date, 'YYYY') upload_Year
     FROM FND_LOBS
    WHERE PROGRAM_NAME = 'FNDATTCH'
      AND expiration_date is NULL
    GROUP BY TO_CHAR(upload_date, 'YYYY')
    ORDER BY 2 ;

   For the particular case that originated this MOS document, this SQL returned the following:
    
        COUNT(*) UPLOAD_YEAR
   ---------- -----------
        58699 2015
        80043 2016
        93663 2017
        94081 2018
       118106 2019
       118335 2020
       120961 2021
       102911 2022
       110001 2023
         9846 2024
   10 rows selected.

    Notice there is 10 years of transactions attachments.
5. Perhaps they need to review their Transaction Retention Policies.
   
   For transaction related attachments, there is are Purge programs. For example for AP, there is the "Submit Purge" form.  This is a form you can find under the responsibility "Payables Purge".  This form executable file is APXPGSUB.  In this form, the user creates a "Purge" definition that includes a Name, a category which my varies of what needds to be purged; like: Single Invoices, Invoices and POs, Suppliers, etc.  The user also must give the "Last Activity Date" which is the date used to fetch the transactions to purge.  The basic idea is that the Purge will fetch all transactions for which Last Update Date on any of its linked records (Accounting, payment, POs , etc) is before the parameter date entered.
    
   The records purged by the module -specific purge program, include attachments.
   
   How to run the Payables Purge program:
   1. Login Oracle Applications using a Payables Purge responsibility.
   2. Open the "Submit Purge" form
      (N) Purge 
   3. Create a new "Purge Definition" in the "Submit Purge" form.
       
   4. Once you save the new Purge definition record, you get the button to [Initiate].
      This button will submit the concurrent program "AP/PO Purge Initiation (Selection) Routine" - APXPGSEL
      This program will fetch the records to be purge and create a "preliminary" report for the user to review and decide if they want to proceed with the purge.
      The "Purge definition" will refresh on the screen and now you see the buttons to [Abort], [Restart], or [Confirm]
      Each button has its own concurrent program.  In particular, [Confirm] will run the "AP/PO Purge Deletion Routine" to perform the actual DELETEs from the tables, like AP_INVOICES, AP_INVOICE_LINES, AP_CHECKS, PO_HEADERS, etc depending on the conditions and category set.
      The Purge Deletion routine spawns child process that run in parallel (for better performance).
       
      For the particular case that originated this MOS document, a Payable Purge was submitted with a "Last Activity Date" of 2015.  This purged 377 Invoices (with Lines and distributions, etc), 105 checks, and 203 POs, among other records.  Some attachments were purged as well.
         
6. Recheck the table FND_LOBS and the LOBSEGMENT using the SQLs given above.
   For the particular case that originated this MOS document, the table and segement size started to decrease after many additional executions of the Payable Purge.
7. The following SQL should show the count "Totals" of records in FND_LOBS linked to any Payables transaction:
    
   • Totals for Payables: 
     SELECT COUNT(*), TO_CHAR( FLS.upload_date, 'YYYY' ) Upload_Yr, FDE.entity_name, FDE.table_name, FDS.datatype_ID
     FROM FND_ATTACHED_DOCUMENTS FAD
     , FND_DOCUMENTS FDS
     , FND_LOBS FLS
     , FND_DOCUMENT_ENTITIES FDE
     WHERE 2=2
     AND FLS.file_ID = FDS.media_Id
     AND FAD.document_ID = FDS.document_ID
     -- AND FDS.datatype_ID = 6
     AND FLS.program_name = 'FNDATTCH'
     AND FAD.entity_name = FDE.entity_name
     AND FDE.application_ID = 200 -- >> ONLY PAYABLES
     GROUP BY TO_CHAR( FLS.upload_date, 'YYYY' ), FDE.entity_name, FDE.table_name, FDS.datatype_ID
     ORDER BY 2, 3, 4, 5 ;
      
   • For details on the transactions: 
     SELECT -- COUNT(*)
     TO_CHAR( FLS.upload_date, 'YYYY' ) Upload_Yr, FDE.entity_name, FDE.table_name, FDS.datatype_ID, PK1_VALUE, PK2_VALUE
     FROM FND_ATTACHED_DOCUMENTS FAD
     , FND_DOCUMENTS FDS
     , FND_LOBS FLS
     , FND_DOCUMENT_ENTITIES FDE
     WHERE 2=2
     AND FLS.file_ID = FDS.media_Id
     AND FAD.document_ID = FDS.document_ID
     -- AND FDS.datatype_ID = 6
     AND FLS.program_name = 'FNDATTCH'
     AND FAD.entity_name = FDE.entity_name
     AND FDE.application_ID = 200
     -- GROUP BY TO_CHAR( FLS.upload_date, 'YYYY' ), FDE.entity_name, FDE.table_name, FDS.datatype_ID
     ORDER BY 1, 2, 3, 4 ;
   1. 1.   
    
   1.     

More documentation

To get more information about Payabels Purging, please refer to Chapter 9 - Resource Management of the "Oracle® Payables User's Guide Release 12.2" - Part No. E48760-17; in PDF or Web formats.

Here you will find all about:

• Criteria for Purging Records
• Submitting Purges
• Viewing the Status of a Purge
• Aborting a Purge / Restarting a Purge
• Purge Reports: Preliminary Purged Listings, Final Purged Listings, etc.

.

References