How to drop XLA_GLT temporary Tables in EBS

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.

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

==>Save.

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.

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

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.