Skip to Content
avatar image
Former Member

In My R/3 System SYS_LOB0000014532C00007$$ lobsegment occupies more size.

Dear Experts,

We have face problem in abnormal database growth and it will be around 40 GB every month.

Owner Name TYPE Tablespace Size(MB) SAPSR3 SYS_LOB0000014532C00007$$ LOBSEGMENT PSAPSR3 219,910.13

For re organization for this table we don't have enough space to perform this actvity.

Kindly provide solution for this.

Regards,

Harish.B   😊

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

5 Answers

  • Nov 08, 2013 at 08:47 AM

    Dear Harish B.

    The objects of SYS_LOB* are LOB-segments. If a database table contains a
    column with a data type like 'raw' or 'long-raw' that contains much
    data these columns are stored in LOB-segments. So these tables are
    actually part of another table. To reduce the size of a LOB-segment, the
    table it belongs to needs to be archived / deleted. Information about
    the LOB-segments is available in the view DBA_LOBS. You can use the
    following SQL Query to find the object that lob belongs to.

    select owner, table_name from dba_lobs
    where 'SYS_LOB00000XXXXX$$' in (segment_name, index_name);

    Note 500340 contains very detail introduction about LOB. Also please
    consider note 821687 (refer to the 'LOB segment fragmentation' section).

    The script in this note will provide the space used in the lob segment
    and you could also refer to use the table reorganization with brspace as
    described in the note 646681 to reorg this table for this problem:

    Note 821687: FAQ: Space utilization and fragmentation in Oracle
    Note 646681: Reorganizing tables with BRSPACE

    You can only reduce the size of lob-segment by reorganization.

    Regarding why the LOB-segment increase so fast, you can talk to the
    application team when you find the object that this LOB belongs to.

    Hope this helps.

    Regards,

    Paul

    Add comment
    10|10000 characters needed characters exceeded

  • Nov 08, 2013 at 08:48 AM

    Hi Harish,

    Refer SAP note 500340 - FAQ: LOBS

      5. Which SAP tables use LOBs?


    To find out all tables and their columns using LOBS use this sql statement:
    select table_name, column_name from dba_lobs where owner = '<sapuser>';
    For example the tables DYNPSOURCE, DYNPLOAD, REPOSRC, REPOLOAD are using LOBs.



    Based on the tables identified above, you need to perform housekeeing and re-organization of the table. Later shrink the LOB segment to reduce the storage space occupied.

    Hope this helps.

    Regards,

    Deepak Kori

    Add comment
    10|10000 characters needed characters exceeded

  • Nov 08, 2013 at 09:51 AM

    Hi

    Use the following query to find the table that use that lob:

    SELECT TABLE_NAME, COLUMN_NAME FROM DBA_LOBS WHERE SEGMENT_NAME ='SYS_LOB0000014532C00007$$';

    What is you Oracle version ?

    Best regards

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Nov 13, 2013 at 03:03 PM

    Hi,

    Deleted old logs and re-org the table.

    Regards,

    MS

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Nov 14, 2013 at 05:35 AM

    Dear All,

    we deleted the old logs, even though it shows 220 GB at SAP level, but when we check O/S level it sizes 80 GB, But we have space 100 GB free space, can we do reorganization on line..if it do how much space of image file is 80 GB or 220 GB.

    Kindly provide information.

    Regards,

    Harish.B

    Add comment
    10|10000 characters needed characters exceeded