Large LOBSEGMENT

[Former Member]

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

[Former Member]

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

[Former Member]

Executed the following SQL

select owner, table_name, column_name from dba_lobs where segment_name = 'SYS_LOB0000010737C00004$$';

Output

OWNER                          TABLE_NAME                 COLUMN_NAME
---------------------------------------------------------------------------------------------
SAPSR3                         DYNPSOURCE                     FIELDINFO

Number of rows

select count(*) from sapsr3.DYNPSOURCE;
  COUNT(*)
----------
 185419

Regards

Anil Verma B

[Former Member]

Ok, DYNPSOURCE contains abap code. It is client independant, so there is no relation to client deletes. As the abap source code is pretty much the same in a release, i looked into one of our ERP 6.0 systems (is NW7.0 as well).

SQL> select count(*) from sapsr3.DYNPSOURCE;
  COUNT(*)
----------
    191858

SQL> select s.segment_name, c.column_name, s.bytes/1024/1024 "MB"
  from dba_segments s, dba_lobs c
  where s.segment_name = c.segment_name and c.table_name = 'DYNPSOURCE'

SEGMENT_NAME                   COLUMN_NAME                  MB
------------------------------ -------------------- ----------
SYS_LOB0001205110C00006$$      EXTENSIONS                   25
SYS_LOB0001205110C00005$$      LOGICINFO                   128
SYS_LOB0001205110C00004$$      FIELDINFO                   304

So if your lob sizes are much bigger, then something must be wrong. Can you post the ddl for the table? You can use dbms_metadata.get_ddl:

SQL> set long 5000
SQL> select dbms_metadata.get_ddl('TABLE','DYNPSOURCE','SAPSR3') from dual
DBMS_METADATA.GET_DDL('TABLE','DYNPSOURCE','SAPR3')
--------------------------------------------------------------------------------
  CREATE TABLE "SAPR3"."DYNPSOURCE"
   (    "PROGNAME" VARCHAR2(40) DEFAULT ' ' NOT NULL ENABLE,
        "DYNPNUMBER" VARCHAR2(4) DEFAULT '0000' NOT NULL ENABLE,
        "R3STATE" VARCHAR2(1) DEFAULT ' ' NOT NULL ENABLE,
        "FIELDINFO" BLOB,
        "LOGICINFO" BLOB,
        "EXTENSIONS" BLOB
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 376340480 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "PSAPES700D"
 LOB ("FIELDINFO") STORE AS (
  TABLESPACE "PSAPES700D" ENABLE STORAGE IN ROW CHUNK 8192 PCTVERSION 10
  CACHE
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT))
 LOB ("LOGICINFO") STORE AS (
 ...

Regards, Michael

[Former Member]

Thanks for the reply.....

Yes the LOBSEGMENT is more than 10Gb.

I am unable to execute the statement

Gives the following error

select dbms_metadata.get_ddl ('TABLE','DYNPSOURCE','SAPSR3') from dual DBMS_METADATA.GET_DDL ('TABLE','DYNPSOURCE','SAPR3');

ERROR at line 1:
ORA-00933: SQL command not properly ended

The error is pointed at dual DBMS_METADATA.GET_DDL

Regards

Anil Verma B

[Former Member]

select dbms_metadata.get_ddl ('TABLE','DYNPSOURCE','SAPSR3') from dual;

Sorry there is a semi colon missing in my post...

[Former Member]

Hello Michael,

Here is the output of select dbms_metadata.get_ddl ('TABLE','DYNPSOURCE','SAPSR3') from dual;

DBMS_METADATA.GET_DDL('TABLE','DYNPSOURCE','SAPSR3')
--------------------------------------------------------------------------------

  CREATE TABLE "SAPSR3"."DYNPSOURCE"
   (    "PROGNAME" VARCHAR2(120) DEFAULT ' '

I don't understand why there is a 10Gb LOBSEGMENT for a table that is only 600Mb.

Regards

Anil Verma B

[Former Member]

Sorry forgot to set the Long command, here the new output.

DBMS_METADATA.GET_DDL('TABLE','DYNPSOURCE','SAPSR3')
--------------------------------------------------------------------------------

  CREATE TABLE "SAPSR3"."DYNPSOURCE"
   (    "PROGNAME" VARCHAR2(120) DEFAULT ' ' NOT NULL ENABLE,
        "DYNPNUMBER" VARCHAR2(12) DEFAULT '0000' NOT NULL ENABLE
,
        "R3STATE" VARCHAR2(3) DEFAULT ' ' NOT NULL ENABLE,
        "FIELDINFO" BLOB,
        "LOGICINFO" BLOB,
        "EXTENSIONS" BLOB,
         PRIMARY KEY ("PROGNAME", "DYNPNUMBER", "R3STATE")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTI

DBMS_METADATA.GET_DDL('TABLE','DYNPSOURCE','SAPSR3')
--------------------------------------------------------------------------------
CS
  STORAGE(INITIAL 18653184 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "PSAPSR3700"  ENABLE
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOC
OMPRESS LOGGING
  STORAGE(INITIAL 7913472 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS
 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)


DBMS_METADATA.GET_DDL('TABLE','DYNPSOURCE','SAPSR3')
--------------------------------------------------------------------------------
  TABLESPACE "PSAPSR3700"
 LOB ("FIELDINFO") STORE AS (
  TABLESPACE "PSAPSR3700" ENABLE STORAGE IN ROW CHUNK 8192 PCTVERSION 10

  CACHE
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483
645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT))
 LOB ("LOGICINFO") STORE AS (
  TABLESPACE "PSAPSR3700" ENABLE STORAGE IN ROW CHUNK
 8192 PCTVERSION 10

DBMS_METADATA.GET_DDL('TABLE','DYNPSOURCE','SAPSR3')
--------------------------------------------------------------------------------
  CACHE
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_PO
OL DEFAULT))
 LOB ("EXTENSIONS") STORE AS (
  TABLESPACE "PSAPSR3700" ENABLE STORAGE IN ROW CHUNK 8192
PCTVERSION 10
  CACHE
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEX
TENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEF

DBMS_METADATA.GET_DDL('TABLE','DYNPSOURCE','SAPSR3')
--------------------------------------------------------------------------------
AULT))

[Former Member]

Ok, the ddl looks ok.

I don't understand why there is a 10Gb LOBSEGMENT for a table that is only 600Mb.

The thing here is, that the table data is stored in one segment without the lob column. The lob data is stored in its own segment, so theoretically it is easily possible to have the above sizes. In fact it is very common, that the lob segment is huge compared to the rest of the table. It all depends on the data in the lob column.

But what i don't see for this particular table, is why your FIELDINFO column is 10gb and mine is only 300mb. Do you have another system to compare?

I could come up with some sql to check the structure of the lob, but i would need to dig a bit for that. Can you please check the storage options of the PSAPSR3700 tablespace? Is the extent management uniform or autoallocate? Is it ASSM?

One thing i would try, would be to reorg the table again (if it is not your prod system) and check if it helps.

Regards, Michael

[Former Member]

I checked the same in my development box and FIELDINFO is 10gb.

The extent management for PSAPSR3700 is system.

I did reorg the table twice but still no difference.

I don't think there is much i can do with this table at this time. Please let me know if you can find something on this?

Once again thanks a lot for your time.

Regards

Anil Verma B

[Former Member]

Uhm, i found it. Note: [1010237 - Growth of DYNPSOURCE table after Unicode migration|https://service.sap.com/sap/support/notes/1010237]

It explains, that there can very well be DYNPSOURCE tables which are 15-times as large as usual. So i guess your system is unicode? Well my system wasn't

I checked now in a ERP 6.0 unicode system and the lob is indeed 10gb.

Regards, Michael

[Former Member]

Yes my system is Unicode.... Then there is not much we can do with the 10Gb Lobsegment.

My criteria was to free some space using reorg after which i freed around 2% on PSAPSR3 and strangely increased around 4% on PSAPSR3700.

So I rebuild my INDEXES ranging between 4gb to 100Mb for both tablespaces. This freed about 7-8% space.

Regards

Anil Verma B

[Former Member]

Then there is not much we can do with the 10Gb Lobsegment.

Yup, you have to live with it for the moment. Just two final remarks regarding database size:

- if you don't already know it, have a look at [706478 - Preventing Basis tables from increasing considerably|https://service.sap.com/sap/support/notes/706478]

- Indexes can be compressed to save space, check [ Index key compression|https://www.sdn.sap.com/irj/scn/weblogs?blog=/pub/wlg/9361] [original link is broken] [original link is broken] [original link is broken];

Best regards, Michael

[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