lob segment

[Roshan]

Oracle 19c

Hi Erman,

could you please advise how we can reduce the size of LOB segments? This slows down backups and consumes space.

SEGMENT_NAME                                                 BYTES/1024/1024/1024 SEGMENT_TYPE
------------------------------------------------------------ -------------------- ------------------
SYS_LOB0000341432C00005$$                                              3970.51581 LOBSEGMENT
SYS_LOB0000341432C00018$$                                              814.144714 LOBSEGMENT
SYS_LOB0000254819C00005$$                                              422.572449 LOBSEGMENT
SYS_LOB0000341438C00002$$                                              199.181824 LOBSEGMENT

Regards,
Roshan

[Roshan]

DB is standard edition so compression is not allowed in expdp

[ErmanArslansOracleBlog]

I guess you are using SECUREFILE LOBs right? The other ones, I mean BASIC file LOBs are old school..

Check -> How to Shrink a Securefile LOB Using Online Redefinition (DBMS_REDEFINITION)? (Doc ID 1394613.1)

[Roshan]

Thanks for the update.

for the LOB segment size 3.9T, how much additional space should I need for the redefinition? After redifinition, can I drop original LOB segment?

[ErmanArslansOracleBlog]

Please go thorugh that MOS note Roshan . How to Shrink a Securefile LOB Using Online Redefinition (DBMS_REDEFINITION)? (Doc ID 1394613.1).. REad it carefully and you ll understand.. You create an interim table and then do the redef.. A new lob with a smaller size appears at the end.

Test it on your test environment and examine the storage needs there. The needed storage space is defined as : there should be a sufficient disk space available to hold both the 'unshrunk' table as well as the 'shrunk' table.