Skip to Content
avatar image
Former Member

Large LOBSEGMENT

Hello Experts,

We have a IDES system on Oracle 10G.

We had deleted two clients and reorg the database to get back some space. We managed to free some in PSAPSR3 table space but the reorg has increased the used space on SAPSR3700 by about 15Gb.

Further investigation i found that a couple of LOB segments have been created and they have taken up a large chunk of space. Not sure on what they do but is there any way i can reclaim this space.

Can these lob segments be dropped or deleted from the table space?

Regards

Anil Verma B

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • avatar image
    Former Member
    Jul 20, 2009 at 08:04 AM

    No, you must not drop those segments!

    Check to which table the segments belong:

    SQL> select owner, table_name, column_name from dba_lobs
         where segment_name = 'SYS_LOB0000114738C00013$$';
    OWNER     TABLE_NAME                COLUMN_NAME
    ------    --------------------      -------------------------------
    SAPR3     REPLOAD                   QDATA

    Technically your actions shouldn't cause an object to grow, but under certain circumstances that is possible (too large initial extents etc). Let us know the object name, number of rows and we might be able to help further.

    Regards, Michael

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member Former Member

      Thanks for those documents, They might be helpful in the future.

      Also one more thing that can be done for removing old indexes and segments (this did not work for me but might be helpful to others)

      Move the FEILDINFO column to a temp column

      ALTER TABLE SAPSR3.DYNPSOURCE ADD (FIELDINFO_TEMP BLOB);
      UPDATE SAPSR3.DYNPSOURCE set FIELDINFO_TEMP = FIELDINFO;

      Move back data from the temp column and drop the temp column

      ALTER TABLE SAPSR3.DYNPSOURCE DROP COLUMN FIELDINFO;
      ALTER TABLE item ADD (FIELDINFO BLOB);
      UPDATE SAPSR3.DYNPSOURCE SET FIELDINFO = FIELDINFO_TEMP;
      ALTER TABLE SAPSR3.DYNPSOURCE DROP COLUMN FIELDINFO_TEMP;

      This should in theory free up some space on lobsegments with old indexes and segments.

      Regards

      Anil Verma B