XLA_GLT Tables Not Being Dropped in EBS 12.1.3

The Problem


Recently I was going through a list of our top largest tables in our E-Business Suite (12.1.3) database (11.2.0.3) and found many tables in the GL schema that began with XLA_GLT_*.  Looking into this further I found over 300 of these tables (which also had indexes and synonyms) consuming over 30 Gigs of space.


SQL> select table_name, blocks*8192/1024/1024 from dba_tables
where owner='GL'
and table_name like 'XLA_GLT%'
order by 2 desc ;

TABLE_NAME BLOCKS*8192/1024/1024
------------------------------ ---------------------
XLA_GLT_157093 2843.92188
XLA_GLT_4083 2326.01563
XLA_GLT_181055 2126.57031
XLA_GLT_52049 1902.35938
XLA_GLT_27064 1560.99219
XLA_GLT_124057 1480.82813
...

 


SQL>select sum(round(blocks*8192/1024/1024,2)) || ' MB' as Total_Size_GLT_Tables from dba_tables
where owner='GL'
and table_name like 'XLA_GLT%';

TOTAL_SIZE_GLT_TABLES
-------------------------------------------
34090.85 MB

 

Research


After doing some research on My Oracle Support I found several articles that helped explain what we are seeing.  One of them in particular was a direct match to our problem, “SLA: Temporary Tables XLA_GLT_ % Are no Longer Dropped (Doc ID 796417.1)”.  Apparently the behavior has been modified after EBS 12.0.x so that the tables are not purged until a specific concurrent program is used to purge it.  The reason being is to keep these temporary tables around for debugging/troubleshooting purposes.  The new concurrent program that needs to be executed is called ” Purge Temporary Journal Import Interface Tables Created By Subledger Accounting”.  The short name is XLADRPGLT.  The concurrent program itself is not assigned to any responsibility out of the box so that is an additional step that needs to be completed.

Additionally, it is strongly recommended to apply patch 10382869.  This has some updated code that includes making sure to drop the underlying synonyms as well as a performance improvement to the query that compiles the list of tables to drop.  I tested both without the patch (the program took almost 2 hours to execute) and with the patch (the program executed in under 5 minutes).  I would highly recommend the patch!

 

Solution


First the program needs to be assigned to a responsibility that is assigned to a user that will be executing the program.  In our example we are using a user that has the General Ledger Superuser responsibility.  Here are the steps to add the Concurrent Program to this user:

  1. Login to the application with a user that has the System Administrator responsibility.
  2. Navigate to Security > Responsibility >Request
  3. Query for the ‘GL Concurrent Program Group’ Request Group.
  4. Add the program “Purge Temporary Journal Import Interface Tables Created By Subledger Accounting” (Subledger Accounting application) to the above request group.
  5. Save.

screenshot1

Next we’ll want to execute the program using the General Ledger Super User responsibility.  In this example we used an end date of 31-MAY-2015

screenshot7

Once the program has completed the output of the log will look similar to this one:

+---------------------------------------------------------------------------+
Subledger Accounting: Version : 12.0.0

Copyright (c) 1979, 1999, Oracle Corporation. All rights reserved.

XLADRPGLT module: Purge Temporary Journal Import Interface Tables Created By Subledger Accounting
+---------------------------------------------------------------------------+

Current system time is 07-JUL-2015 13:16:59

+---------------------------------------------------------------------------+

**Starts**07-JUL-2015 13:16:59
**Ends**07-JUL-2015 13:53:10
Purge GLT Program completed Normal
+---------------------------------------------------------------------------+
Start of log messages from FND_FILE
+---------------------------------------------------------------------------+
07-JUL-2015 13:16:59 - Starting To Purge The XLA_GLT tables
The following GLT table is purged GL.XLA_GLT_2002
The following GLT table is purged GL.XLA_GLT_2003
The following GLT table is purged GL.XLA_GLT_2004
The following GLT table is purged GL.XLA_GLT_3004
The following GLT table is purged GL.XLA_GLT_3005
The following GLT table is purged GL.XLA_GLT_4038
The following GLT table is purged GL.XLA_GLT_4047
The following GLT table is purged GL.XLA_GLT_4049
The following GLT table is purged GL.XLA_GLT_4061
The following GLT table is purged GL.XLA_GLT_4075
The following GLT table is purged GL.XLA_GLT_4079
The following GLT table is purged GL.XLA_GLT_4082
The following GLT table is purged GL.XLA_GLT_4083
The following GLT table is purged GL.XLA_GLT_5021
The following GLT table is purged GL.XLA_GLT_5062
The following GLT table is purged GL.XLA_GLT_5074
The following GLT table is purged GL.XLA_GLT_5075
The following GLT table is purged GL.XLA_GLT_5076
The following GLT table is purged GL.XLA_GLT_14051
All the GLT tables have been purged successfully for end date 30-JUN-2015 for ledger:
+---------------------------------------------------------------------------+
End of log messages from FND_FILE
+---------------------------------------------------------------------------+

 

Conclusion


After running the purge program to retain only the last 60 days worth of XLA_GLT tables we were able to greatly reduce the amount of space used (approximately 25 Gigs).  Going forward we will schedule this job to run shortly after the monthly financial close and continue to retain the last 60 days worth of these tables in the event we need the data for any debugging/troubleshooting purposes (as it was intended for).

 

Leave a Comment

Your email address will not be published. Required fields are marked *