EXT_FILE_TRANS_TAB growing and cannot be cleaned

Hi,

I have a faint memory (This was > 10 years ago...) that we found a load job that will “unload” or truncate the EXT_FILE_TRANS_TAB that is scheduled as any other file load jobs.

   /H

Your memory is correct. There is a file template you can run to clean up old transactions:

Make sure to enter the number of days on the templates you want to clean out transactions for and also determine if you want to include file log in the cleanup by ticking “Remove Completely”:

thank you guys that the first promising answer i get including IFS cloud support and our Consultant

Does someone face a problem with using this cleanup method? With over 60GB of data to remove it is not possible to purge this table. Snapshot to old errors occured.

This has nothing to do with the application itself, this relates to how the Oracle DB is configured.

Try to run the first large cleanup when there is less other transactions in the db.
Also perhaps increase db parameter UNDO_RETENTION and maybe increase size of UNDO datafiles. 

Is there a “date” on the “remove job”? -  to remove data in smaller chunks?

There is a date but no easy way to establish the type of external file with big amount of data. Date estimation to remove data in smaller chunks it is also not so easy.

I understand that we can change UNDO_RETENTION parameter but we have easier way to avoid problem using commit after some chunk. Many times used in any Clenaup processes:

Batch_SyS.Fnd_Light_Cleanup and its subsequent calls.

Hi Piotr,
last time I used to copy records to new table (eg. 1 month old). 
After that I renamed base table to different name and new table to base table name.
It worked for table with 2,5TB data. 
After all I was able to use available method to clean old data with success.
 

Hi ​@RafalO 

I know how to figure out the problem but:

1. it is time consuming to establish file types and time frames
2. please imagine your steps on IFS cloud hosted environment without access to ifsapp/sys account

​@knepiosko I can imagine that :)

It will be nice to add some global variables and/or parameters to handle any cleanup or template.
I know that such solution is used in some cleanup processes. (older then .. days, states, etc.)

There is a way to set up a clean process, but it is not easy just do it in one go with 2.5TB!