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:
- Login to the application with a user that has the System Administrator responsibility.
- Navigate to Security > Responsibility >Request
- Query for the ‘GL Concurrent Program Group’ Request Group.
- Add the program “Purge Temporary Journal Import Interface Tables Created By Subledger Accounting” (Subledger Accounting application) to the above request group.
- Save.
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
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).
