Shrinking FND_LOG_MESSAGES

EBS Table Inventory

For a variety of reasons, I took inventory of the largest objects (in this particular case, tables) in my R12.2 EBS environment.  I strongly suggest you do the same, upon occasion, as it’s somewhat enlightening.

select
  owner
 ,segment_name
 ,bytes/1024/1024/1024 gb
from
  dba_segments
where
  segment_type = 'TABLE'
order by
  bytes/1024/1024/1024 desc

At the very top of my list was APPLSYS.FND_LOG_MESSAGES, checking in at 29.4GB.  I now had a target.

Read the directions

This is EBS, we can’t just arbitrarily start shooting and hope we don’t break anything.  There’s a number of MOS notes on support describing various aspects of purging this table.  Number one on the list was the concurrent request, “Purge Logs and Closed System Alerts”.  There were also some notes about a bug which prevented this CR from performing correctly, but they didn’t seem relevant to EBS 12.2

Back to my problem child

SQL> select count(*) from applsys.fnd_log_messages;

  COUNT(*)
----------
       308

This jumps out immediately.

SQL> select owner, segment_name, bytes/1024/1024/1024 gb 
from dba_segments where segment_type = 'TABLE' 
and segment_name = 'FND_LOG_MESSAGES';

OWNER                     SEGMENT_NAME              GB
------------------------- ------------------------- ----------
APPLSYS                   FND_LOG_MESSAGES          29.4005127

I run Concurrent Request “Purge Logs and Closed System Alerts” every week, and the log shows it’s completing normal.  Why are 308 rows consuming so much space on disk?  Let’s assume that this table has had no attention since it was first installed about 14 years ago.  There were times when debug was on, to be sure, filling FND_LOG_MESSAGES faster than normal.  But after a quick check of Profile Option Values I see that at present, nobody has debug enabled.

Proving out the solution

Is it possible that 14 years of inserts and deletes could cause this table to have developed a bad case of high water mark 29 GB deep?

SQL> alter table applsys.fnd_log_messages enable row movement;

Table altered.

Elapsed: 00:00:00.08
SQL> alter table applsys.fnd_log_messages shrink space;

Table altered.

Elapsed: 00:56:47.99
SQL> select owner, segment_name, 
bytes/1024/1024/1024 gb from dba_segments 
where segment_type = 'TABLE' 
and segment_name = 'FND_LOG_MESSAGES';

OWNER        SEGMENT_NAME     GB
------------ ---------------- ---------
APPLSYS      FND_LOG_MESSAGES 14.501709
Elapsed: 00:00:00.31

Not bad; we’re down from 29.4GB to 14.5GB without much effort.  Let’s go in for the kill

SQL> alter table applsys.fnd_log_messages move;

Table altered.

Elapsed: 00:01:30.32

SQL> select owner, segment_name, 
bytes/1024/1024/1024 gb from dba_segments 
where segment_type = 'TABLE' 
and segment_name = 'FND_LOG_MESSAGES';

OWNER   SEGMENT_NAME     GB
------- ---------------- ----------
APPLSYS FND_LOG_MESSAGES .000366211
Elapsed: 00:00:00.13

Sweet.  29GB down to .0003GB.

Don’t forget that alter table xyz move renders indexes invalid.

set pagesize 0
select
'alter index '
|| owner
|| '.'
|| index_name
|| ' rebuild;'
from
dba_indexes
where
status = 'UNUSABLE'
;

SQL> alter index applsys.fnd_log_messages_n4 rebuild;

Index altered.

SQL> alter index applsys.fnd_log_messages_n5 rebuild;

Index altered.

SQL> alter index applsys.fnd_log_messages_n7 rebuild;

Index altered.

SQL> alter index applsys.fnd_log_messages_n8 rebuild;

Index altered.