Skip to main content
Solved

Data is constantly increasing in APP9 tables

WSHAH
Do Gooder (Customer)
Forum|alt.badge.img+8
  • WSHAH
  • Do Gooder (Customer)
  • 42 replies

 

 

Hi All,

 

Data is constantly increasing in APP9 tables

 

SEGMENT_NAME OWNER SEGMENT_TYPE Size(MB)
SYS_LOB0000071125C00006$$ IFSAPP LOBSEGMENT 11959.13 <<<<<<<<<<<<
SYS_LOB0000070639C00009$$ IFSAPP LOBSEGMENT 6274.19 <<<<<<<<<<<<
SYS_LOB0000071118C00005$$ IFSAPP LOBSEGMENT 5834.19 <<<<<<<<<<<
SYS_LOB0000070604C00011$$ IFSAPP LOBSEGMENT 3135.19 <<<<<<<<<

 

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

Best answer by Imal Thiunuwan

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.

 

Cheers!

View original
Did this topic help you find an answer to your question?
This topic has been closed for comments

4 replies

S
Superhero (Employee)
Forum|alt.badge.img+21
  • Sajith D
  • Superhero (Employee)
  • 427 replies
  • June 10, 2021

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. 

P
1 person likes this
  • P
    ProGarryP
WSHAH
Do Gooder (Customer)
Forum|alt.badge.img+8
  • WSHAH
  • Author
  • Do Gooder (Customer)
  • 42 replies
  • June 10, 2021

Hi Sajith ,

data is getting not  cleaned up with automatically pre-defined rules. But Jobs are running smoothly

Light Cleanup

Heavy Cleanup

 

 

Regards

T
Superhero (Employee)
Forum|alt.badge.img+19

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.

Yasas Kasthuriarachchi
Imal Thiunuwan
Ruwandi De Saram
3 people like this
  • Yasas Kasthuriarachchi
    Yasas Kasthuriarachchi
  • Imal Thiunuwan
    Imal Thiunuwan
  • Ruwandi De Saram
    Ruwandi De Saram
Imal Thiunuwan
Superhero (Employee)
Forum|alt.badge.img+16
  • Imal Thiunuwan
  • Superhero (Employee)
  • 117 replies
  • Answer
  • June 10, 2021

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.

 

Cheers!

Banu Liyanapathirana
Yasas Kasthuriarachchi
durette
6 people like this
  • Banu Liyanapathirana
    Banu Liyanapathirana
  • Yasas Kasthuriarachchi
    Yasas Kasthuriarachchi
  • durette
    durette
  • B
    Bernd Osterkamp
  • RanukaSerasinghe
    RanukaSerasinghe
  • T
    Tomas Ruderfelt
Terms & ConditionsCookie settings

Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings