How to drop XLA_GLT temporary Tables in EBS

Drop XLA_GLT temporary Tables| As an Oracle DBA we need to reclaim space in database. For this we need to clear all temporary tables in our instance, which is no longer required. Temporary tables used in the GL Transfer process are no longer cleared down once the transfer is complete. using program ‘Purge Temporary Journal Import Interface Tables Created By Subledger Accounting’ we can purge temporary journal import interface table. This is a change in behavior as these tables were normally dropped for fully processed GL Transfers.

How to drop XLA_GLT temporary Tables Tables in EBS.


These tables are named XLA_GLT_% where % is the group id used in the General Ledger Transfer. Run below query to find out those tables.


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

How to find out Total size of XLA_GLT_% table.

SQL>select sum(round(blocks*8192/1024/1024,2)) ||  ‘ SIZE_IN_MB’ from dba_tables
where owner=’GL’
and table_name like ‘XLA_GLT%’;

As mentioned in Oracle (Doc ID 796417.1), Once all the data is transferred to GL without error for each particular group id then the tables can be dropped using the concurrent program: XLADRPGLT – Purge Temporary Journal Import Interface Tables Created by Subledger Accounting.

The behavior before the patch is:

– GL transfer dumps data into a temporary gt table and Journal import processes this data. On successful completion, this gt table is dropped.


The behavior after the patch:


– Table xla_glt_groupid is not dropped, so that table is available for debugging.

Steps to run Purge Temporary Journal Import Interface Tables Created By Subledger Accounting.


To run the concurrent program, Purge Temporary Journal Import Interface Tables Created By Subledger Accounting. Need to be add program to request qroup as mentioned below.


==> 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.

drop XLA_GLT temporary Tables



==>Add the program “Purge Temporary Journal Import Interface Tables Created by Subledger Accounting” (Subledger Accounting application) to the above request group.

Steps to purge XLA_GLT temporary Tables












==>Save.

To run concurrent “Purge Temporary Journal Import Interface Tables Created By Subledger Accounting” follow the below steps.

1)  Switch responsibility General Ledger Super User and go to veiw and then request to run the concurrent program “Purge Temporary Journal Import Interface Tables Created By Subledger Accounting”.

Be careful while purging the temporary table because current year financial, data may be use for troubleshot/debugging, so it should be last year financial year.

 

Purge Temporary Journal Import Interface Tables

2) Select “Primary Ledger” and End date as above and click on submit.

Purge Temporary Journal Import Interface Tables Created By Subledger Accounting
If you are facing the below error in request log. Oracle Strongly recommended to apply the patch 10382869.
 
 
Start of log messages from FND_FILE 
+—————————————————————————+ 
04-OCT-2018 11:00:11 – Starting To Purge The XLA_GLT tables 
ORA-01722: invalid number 
+—————————————————————————+ 
End of log messages from FND_FILE

3) After that check the XLA table size using below sql query and compare it with previous one.

select sum(round(blocks*8192/1024/1024,2)) || as ‘ SIZE_IN_MB’ from dba_tables
where owner=’GL’ and table_name like ‘XLA_GLT%’;


Alternatively, you may check another post, click here.

1 thought on “How to drop XLA_GLT temporary Tables in EBS”

Leave a Comment