Is it possible to release unused space from MESSAGECOMPONENTLOB and MESSAGELOB tables within LOB_TABLESPACE?
Article ID: 378284
Updated On:
Products
Issue/Introduction
The Oracle Database is running out of space, and we are looking to reduce the used space in the MESSAGECOMPONENTLOB and MESSAGELOB tables.
Does Broadcom have any SQL script or queries to reduce the space in these tables?
Resolution
This would be managed by the Oracle Database Administrator (DBA). As long as the DBA does not change the DLP schema's definition, the DBA can use whatever option is available to them for Oracle Enterprise database.
One option is to shut down the Enforce services, back up the problem table using data pump, drop the same table (with the purge option), and finally re-import the dump back into the database. The back-up to the dump file only backs up the undeleted data, skipping over the data that is marked as deleted within the segments, so the dump file would only be large enough to hold undeleted data.
For example, in the case of the tables index, and lob data, the MESSAGECOMPONENTLOB is taking about 1TB of space while the current data in those segments uses about 116 GB of data. This would be about the size of the resulting dump. When the table is dropped with the purge option, the segments for the table (including its LOB segments) are cleared, returning all those extents as being unallocated within the tablespace. The import then creates new segments which will use the available space within the tablespace (always using the available space at the beginning of the data files). From there, the DBA can even shrink any data files down to the high watermark so that each of the data files are as small as possible.
Before doing the import, the DBA should verify and make sure that the LOB segments are already configured to use SecureFiles, which is required for maximising the reuse of new data going into that same segment. Databases that don't have LOB data as SecureFiles often have the old LOB segment management issue, where new data entered into a segment doesn't first reuse deleted space within the same segment.
To verify if SecureFiles is enabled the DBA can run this command:
SELECT table_name, column_name, tablespace_name, securefile FROM user_lobs where table_name IN 'CONDITIONVIOLATIONLOB','MESSAGELOB','MESSAGECOMPONENTLOB','WALKREPORT');
In summary, the DBA is responsible for the maintenance and modification of the Oracle database. The DBA should follow the Oracle Enterprise Edition documentation and Support recommendations while making any such changes. This article serves only as high-level guidance. Any further advice required should be sought from Oracle Support.
Additional Information
Feedback
