Skip to Content
avatar image
Former Member

assessment on LOB reorg potential

we have table SXMSCLUR with securefile BLOB built on column CLUSD. Output of script using DBMS_SPACE.SPACE_USAGE (script attached) gives:

  • NAMEVALUE
  • -------------------------------------------------- ------------------------------
  • OwnerSAPSR3
  • Table NameSXMSCLUR
  • Column NameCLUSTD
  • Lob Segment NameSYS_LOB0001420838C00009$$
  • PCT VersionN/A
  • RetentionN/A
  • CacheYES
  • CompressionNO
  • DeduplicationNO
  • In RowYES
  • SecurefileYES
  • NAMEVALUE
  • -------------------------------------------------- ------------------------------
  • Total Blocks52280728
  • Total Size in MB408443.19
  • Used Blocks270570
  • Used Size in MB2113.83
  • Expired Blocks51851034
  • Expired Size in MB405086.20
  • Unexpired Blocks0
  • Unexpired Size in MB0.00
  • Unused Blocks0
  • Unused Size in MB0.00

I want to make assessment for table SXMSCLUR using DBMS_ADVISOR as below

EXEC DBMS_ADVISOR.CREATE_OBJECT(:TASK_NAME, 'TABLE', -
'SAPSR3', 'SXMSCLUR', NULL, NULL, :OBJECT_ID);

it gives me recommendation that does not include assessment on LOB segment (if I am not mistaking since LOB segment is much bigger than table itself):

  • RECOMMENDATION
  • --------------------------------------------------------------------------------
  • ACTION1
  • --------------------------------------------------------------------------------
  • ACTION2
  • --------------------------------------------------------------------------------
  • ACTION3
  • --------------------------------------------------------------------------------
  • Enable row movement of the table SAPSR3.SXMSCLUR and perform shrink, estimated savings is 8,023,655,475 bytes.
  • alter table "SAPSR3"."SXMSCLUR" shrink space
  • alter table "SAPSR3"."SXMSCLUR" shrink space COMPACT
  • alter table "SAPSR3"."SXMSCLUR" enable row movement

Doing analysis for reorg candidates using Space_SegmentFragmentation_SegmentAdvisor (sapnote_0001438410 - attached as well), does not give LOB segment SYS_LOB0001420838C00009$$ as candidate for reorg.

Can you please let me know why assessment for reorg candidates using segment advisor is not highlighting lob SYS_LOB0001420838C00009$$ as a good candidate for reorg and highlight UNUSED space?

Can you please explain if reorg of table SXMSCLUR will remove all Expired Blocks from BLOB?

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • avatar image
    Former Member
    Feb 07 at 04:32 PM
    Add comment
    10|10000 characters needed characters exceeded