In Oracle E-Business Suite (EBS) environments, it’s a good practice to occasionally analyze the largest database objects to identify potential inefficiencies. Recently, I discovered that the FND_LOG_MESSAGES table in my Oracle EBS 12.2 environment had ballooned to 27.1GB. Here’s a step-by-step guide to how I resolved the issue and significantly reduced its size.
Identifying the Problem
Using the following query, I checked for the largest tables in my environment:
SELECT
owner,
segment_name,
bytes / 1024 / 1024 / 1024 AS gb
FROM
dba_segments
WHERE
segment_type = ‘TABLE’
ORDER BY
gb DESC;
The FND_LOG_MESSAGES table stood out, occupying 29.4GB, despite having only 308 rows. This hinted at a high water mark issue—an artifact of historical growth caused by frequent inserts and deletes.
Initial Analysis
To confirm, I ran the following queries:
SELECT COUNT(*) FROM applsys.fnd_log_messages;
This returned a row count of 410. Then, to verify the table’s size:
SELECT
owner,
segment_name,
bytes / 1024 / 1024 / 1024 AS gb
FROM
dba_segments
WHERE
segment_type = ‘TABLE’
AND segment_name = ‘FND_LOG_MESSAGES’;
Clearly, the table needed attention. Regular execution of the “Purge Logs and Closed System Alerts” concurrent request wasn’t sufficient to prevent this growth.
Resolving the Issue
Step 1: Enable Row Movement
To begin the cleanup, I enabled row movement to allow the table structure to be adjusted:
ALTER TABLE applsys.fnd_log_messages ENABLE ROW MOVEMENT;
Step 2: Shrink the Table
Next, I used the SHRINK SPACE command to reclaim unused space:
ALTER TABLE applsys.fnd_log_messages SHRINK SPACE;
This reduced the size from 27.1GB to 11.2GB.
Step 3: Move the Table
For a more thorough cleanup, I moved the table to reset the high water mark completely:
ALTER TABLE applsys.fnd_log_messages MOVE;
This brought the table size down to an astonishing 0.007GB.
Rebuilding Indexes
After the MOVE operation, the associated indexes became unusable. To restore them, I used the following query to generate the rebuild commands:
SET PAGESIZE 0
SELECT
‘ALTER INDEX ‘ || owner || ‘.’ || index_name || ‘ REBUILD;’ AS rebuild_command
FROM
dba_indexes
WHERE
status = ‘UNUSABLE’;
Then, I executed the generated ALTER INDEX commands for all unusable indexes:
ALTER INDEX applsys.fnd_log_messages_n7 REBUILD;
ALTER INDEX applsys.fnd_log_messages_n8 REBUILD;
ALTER INDEX applsys.fnd_log_messages_n9 REBUILD;
ALTER INDEX applsys.fnd_log_messages_n10 REBUILD;
Key Takeaways
1. Regular Maintenance is Crucial: Periodically analyze and maintain large database tables to avoid unnecessary space consumption.
2. High Water Mark Awareness: Tables with frequent inserts and deletes may develop a high water mark, leading to inefficient space usage.
3. Index Rebuilds After Table Moves: Always rebuild indexes after performing a MOVE operation.
4. Concurrent Request Monitoring: Ensure that purging jobs like “Purge Logs and Closed System Alerts” are running as expected.
By following these steps, I was able to reduce the size of the FND_LOG_MESSAGES table from 27.1GB to a negligible 0.007GB, reclaiming valuable disk space with minimal effort.
—
