OWNER TABLE_NAME COLUMN_NAME SEGMENT_NAME TABLESPACE_NAME CHUNK CACHE LOGGING SEC SEG RETENTI RETENTION_VALUE ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ IFSAPP PDF_ARCHIVE_TAB PDF SYS_LOB0000070639C00009$$ IFSAPP_LOB 8192 NO YES YES YES DEFAULT >>>>>>>>>>>>>>>>>>>> 6274 MB IFSAPP FNDCN_MESSAGE_BODY_TAB MESSAGE_VALUE SYS_LOB0000070604C00011$$ IFSAPP_LOB 8192 NO YES YES YES DEFAULT >>>>>>> 3135 MB IFSAPP XML_REPORT_ARCHIVE_TAB XML_HEADER SYS_LOB0000071118C00005$$ IFSAPP_LOB 8192 NO YES YES YES DEFAULT >>>>>>> 5834 MB IFSAPP XML_REPORT_DATA_TAB DATA SYS_LOB0000071125C00006$$ IFSAPP_LOB 8192 NO YES YES YES DEFAULT <<<<<<<<<<<<< 11959 MB
PDF_ARCHIVE_TAB
FNDCN_MESSAGE_BODY_TAB
XML_REPORT_ARCHIVE_TAB
XML_REPORT_DATA_TAB
ARCHIVE_TAB
Kindly advise
Page 1 / 1
Hi @WSHAH ,
The tables in question belongs to two primary areas in the application. The FNDCN table holds the Application messages while all other tables are related to printing,
If you feel that the growth of these tables are disproportionate to the size of the database you can probably check why the data is getting not getting cleaned up as both these areas have their own mechanisms to clean up the data automatically with pre-defined rules.
for the Application messages, you would need to check whether there are lot of messages in finished state not getting cleaned up or there is a large number of messages in error at any given time and act accordingly. For the report related data you would need to see whether the standard Heavy Cleanup job is having any issues running or whether the report life needs to be adjusted for certain reports from their default values.
Cheers.
Hi Sajith ,
data is getting not cleaned up with automatically pre-defined rules. But Jobs are running smoothly
Light Cleanup
Heavy Cleanup
Regards
Space in LOB segments are not being freed automatically when you remove records in the corresponding table. This is the way Oracle works. For example if you remove records in PDF_ARCHIVE_TAB the data in the LOB segment connected to that table is not removed.
There is a separate database task in IFS to do that which is called “Shrink Lob Segments”.
I do not have so much experience about it but I would not recommend running it when you use the system and it might take time.
Hello @WSHAH,
As @Tomas Ruderfelt mentioned, there is a separate database task used in IFS which is called “Shrink Lob Segments” to handle large sized LOBs. But, from Oracle 12C onwards, there’s a mechanism called “Oracle Advanced Compression” is available to handle large sized objects in a Database.
However, due to some reasons, even with the above mentioned mechanism the LOBS sizes are being increased unexpectedly. So you can try to setup “Shrink LOB Segments job” and check if it works for the large sized LOBS.
But remember that, if you run this job for the first time, it will take a large amount of time (May be about two days) to complete.
Here, I have attached a description on how to setup “Shrink LOB Segments job”.
1. First You need to create a Database Task with following information.
2. Then save it and create a new schedule to run the created database task.
When you schedule the “Shrink LOB Segments” database task to be run, please set it in a non-peak time when the users are not using the system. This job can be setup to run weekly or monthly according to the size of the data amount handled in your system. Hope this information helps.
