Today we are going to have look on “Shrink Lob segment”. Before we start the article we must know about the heading. What does it stands every one must know.

Shrinking Segments: Overview

Shrinking a sparsely populated segment improves the performance of scan and DML operations on that segment.

It resolves mainly two issues:-
• Full table scans (fewer and denser blocks)
• Better index access (fewer I/Os on range ROWID scans due to a more compact tree)

 

SQL> SELECT b.owner,b.table_name,b.column_name,a.segment_name, a.bytes FROM dba_segments a JOIN dba_lobs b on A.SEGMENT_NAME=B.SEGMENT_NAME and B.SEGMENT_NAME='SYS_LOB0000064465C00005$$';
SQL> ALTER TABLE COGNOS.CMDATA MODIFY LOB (DATAPROP) (SHRINK SPACE);
SQL> SELECT b.owner,b.table_name,b.column_name,a.segment_name, a.bytes FROM dba_segments a JOIN dba_lobs b on A.SEGMENT_NAME=B.SEGMENT_NAME and B.SEGMENT_NAME='SYS_LOB0000064465C00005$$';

Thank you for giving valuable time to add gems to Oracle treasure.

If you want to be updated with all our articles send us the Invitation or Follow us:

Telegram Channel: https://t.me/helporacle

Skant Gupta’s LinkedIn: www.linkedin.com/in/skantali/

Joel Perez’s LinkedIn: Joel Perez’s Profile

LinkedIn Group: Oracle Cloud DBAAS

Facebook Page: OracleHelp

About The Author

Comments

  1. Christian Klammer

    That works for BASICFILE LOB only; for SECUREFILE LOB you have to use either Datapump, edition-based redifinition or moving the LOB segment to another tablespace.

  2. Sushil Shah

    Hi Anuradha,

    Thanks for sharing the details.One suggestion enable row movement before you go ahead with shrinking, This will help you reclaiming more space and disable the same once done with shrinking.

    Thanks
    Sushil Shah

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.