When you are trying to manage your database size, you might see large SYS_LOB* tables on Oracle based systems.
Questions that will be answered in this blog are:
- How can I find large SYS_LOB tables in my Oracle based SAP system?
- How can I find the real table for the SYS_LOB?
Finding SYS_LOB table size
In DB02 select all segments larger than 1 GB with name SYS_LOB*:
Output might be like this:
On the tab Lobs you can see the table:
From SYS_LOB name to table
Next to the direct view in DB02 as shown above, OSS note 2142497 – What to do for large lobsegment on Oracle database? describes several ways of determining the translation from the SYS_LOB label to the real table.
You can use ST04 SQL query and fire this query: replace the SYS_LOB number from the one in your own system:
SELECT OWNER, TABLE_NAME, COLUMN_NAME FROM DBA_LOBS
WHERE 'SYS_LOB0007061918C00006$$' IN (SEGMENT_NAME, INDEX_NAME);
Or in ST04, you launch the DBA view for DBA_LOBS:
Result:
In this case table DMC_INDXCL is the source of the SYS_LOB. Knowing this it will easily lead you to OSS note 2704146 – How to delete data in DMC_INDXCL? – SLT for clean up.
Frequent SYS_LOB tables
Frequent SYS_LOB tables and solutions:
- SOFFCONT1: move to content server or delete. See this blog for moving to content server, and this blog for deletion.
- DMC_INDXCL; OSS note 2704146 – How to delete data in DMC_INDXCL? – SLT for clean up.
- VBDATA: reorganization program RSM13002. See OSS note 67014 – Reorganizing update requests
- ODQDATA_F: see OSS note 2548747 – How-To: Cleaning delta queues for upgrade or similar procedures