On IFS Applications 10 UPD10 with Oracle 19c, the scheduled Shrink Lob Segments job moved from success into Warning. An early failure was: Shrinking of lob segment FILE_DATA in table EDM_FILE_STORAGE_TAB failed, due to Oracle error ORA-06550: line 1, column 75: PLS-00201: identifier 'DBMS_REDEFINITION' must be declared ... ORA-06512: at "IFSAPP.DATABASE_SYS", line 3783. After that cleared, failures continued with ORA-01691: unable to extend lobsegment IFSAPP.SYS_LOB0000347725C00013$$ by 1024 in tablespace IFSAPP_LOB, and ORA-12008 caused by ORA-01691: unable to extend lob segment IFSAPP.SYS_LOB0000347073C00013$$ by 1024 in tablespace IFSAPP_LOB while shrinking FNDCN_MESSAGE_BODY_TAB.MESSAGE_TEXT and MESSAGE_VALUE. MESSAGE_TEXT showed roughly 297 GB allocated and 144 GB physical, with about 100 GB free in IFSAPP_LOB. How should this be resolved?
Technical details
Environment: IFS Applications 10 UPD10, Oracle 19c. Failing LOBs included FILE_DATA on EDM_FILE_STORAGE_TAB, then MESSAGE_TEXT and MESSAGE_VALUE on FNDCN_MESSAGE_BODY_TAB. Sample sizes: MESSAGE_TEXT roughly 297 GB allocated / 144 GB physical; free space in IFSAPP_LOB estimated ~100 GB. Apps 10 stores LOBs as Oracle SecureFile; shrink uses DBMS_REDEFINITION (online redefinition) rather than ALTER TABLE ... SHRINK SPACE, so temporary space can nearly double. Related Oracle Doc ID 1451124.1 was cited for the temporary-space behaviour.
Any attachments are visible to signed-in members.
1 answer
0
✓ Accepted answer
Partially verified guidance: extend IFSAPP_LOB substantially (guidance cited at least ~100 GB+ beyond current free space, and in practice enough headroom for roughly double the large LOB during redefinition), then re-run Shrink Lob Segments off-peak; first runs can take on the order of two days; schedule weekly or monthly based on growth. Optionally enable Oracle Advanced Compression (Oracle 12c+, Enterprise Edition) to reclaim space and improve LOB handling. Before shrinking, run cleanup of finished Application Messages to reduce FNDCN_APPLICATION_MESSAGE_TAB / FNDCN_MESSAGE_BODY_TAB volume; a community practice is a daily purge of messages (%) from any queue older than 360 days. Conflict to note: one reply suggested disabling Shrink Lob Segments in favour of Advanced Compression, while the accepted answer also says to extend tablespace and continue running Shrink Lob Segments (or use Advanced Compression as an alternative). Treat these as alternate strategies, not both-mandatory steps. Outcome: the poster added ~32 GB/day, still saw warnings, and eventually disabled the shrink job; final success was not confirmed in-thread.
Prerequisites and limitations
Requires DBA capacity planning for IFSAPP_LOB; SecureFile redefinition needs large temporary free space. Advanced Compression needs Oracle EE licensing. Cleanup of finished messages reduces volume but alone may not reclaim IFSAPP_LOB file growth until a successful shrink/compression approach runs. Poster never confirmed a successful shrink after extending space.