Practice: Shrinking SecureFile LOBs

Overview

This practice shows how to reclaim space and improve performance with SecureFile LOBs.

Before starting any new practice, refer to the Practices Environment recommendations.

Step 1 : Create a table with a SecureFile LOB

  • Execute a shell script that creates a tablespace with sufficient space to let the LOB grow and be a candidate for shrinking.

    
    $ cd /home/oracle/labs/M104780GC10
    $ /home/oracle/labs/M104780GC10/setup_LOB.sh
    ...
    SQL> DROP TABLESPACE tbs_for_users INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS;
    
    Tablespace dropped.
    
    SQL> CREATE TABLESPACE tbs_for_users DATAFILE SIZE 500M;
    
    Tablespace created.
    
    SQL> create user hr identified by password default tablespace tbs_for_users;
    
    User created.
    
    SQL> grant dba to hr;
    
    Grant succeeded.
    
    SQL> exit
    
    $
  • Create a table with a CLOB column in PDB21.

    
    $ sqlplus system@PDB21
    
    Copyright (c) 1982, 2019, Oracle.  All rights reserved.
    
    Enter password:
    Last Successful login time: Fri Dec 13 2019 10:42:50 +00:00
    
    Connected to:
    
    SQL> CREATE TABLE hr.t1 ( a CLOB) LOB(a) STORE AS SECUREFILE TABLESPACE tbs_for_users;
    
    Table created.
    
    SQL>

Step 2 : Shrink the SecureFile LOB after rows are inserted and updated

  • Insert rows, update the CLOB data, and commit.

    
    SQL> INSERT INTO hr.t1 values ('aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa');
    
    1 row created.
    
    SQL> INSERT INTO hr.t1 Select * from hr.t1;
    
    1 row created.
    
    SQL> INSERT INTO hr.t1 Select * from hr.t1;
    
    2 rows created.
    
    SQL> INSERT INTO hr.t1 Select * from hr.t1;
    
    4 rows created.
    
    SQL> UPDATE hr.t1 SET a=a||a||a||a||a||a||a;
    
    8 rows updated.
    
    SQL> UPDATE hr.t1 SET a=a||a||a||a||a||a||a;
    
    8 rows updated.
    
    SQL> COMMIT;
    
    Commit complete.
    
    SQL>
  • Shrink the LOB segment.

    
    SQL> ALTER TABLE hr.t1 MODIFY LOB(a) (SHRINK SPACE);
    
    Table altered.
    
    SQL>
  • Display the number of extents or blocks freed.

    
    SQL> SET PAGES 100
    SQL> SELECT * FROM v$securefile_shrink;
    
      LOB_OBJD SHRINK_STATUS
    ---------- ----------------------------------------
    START_TIME
    ---------------------------------------------------------------------------
    END_TIME
    ---------------------------------------------------------------------------
    BLOCKS_MOVED BLOCKS_FREED BLOCKS_ALLOCATED EXTENTS_ALLOCATED EXTENTS_FREED
    ------------ ------------ ---------------- ----------------- -------------
    EXTENTS_SEALED     CON_ID
    -------------- ----------
         76063 COMPLETE
    10-NOV-20 11.30.55.545 AM +00:00
    10-NOV-20 11.30.55.917 AM +00:00
               2            2                2                 1             1
                 1          3
    
    SQL>

    As a result, two blocks are freed.

Step 3 : Shrink the SecureFile LOB after rows are updated

  • Update the CLOB.

    
    SQL> UPDATE hr.t1 SET a=a||a||a||a||a||a||a;
    
    8 rows updated.
    
    SQL> UPDATE hr.t1 SET a=a||a||a||a||a||a||a;
    
    8 rows updated.
    
    SQL> UPDATE hr.t1 SET a=a||a||a||a||a||a||a;
    
    8 rows updated.
    
    SQL> UPDATE hr.t1 SET a=a||a||a||a||a||a||a;
    
    8 rows updated.
    
    SQL> COMMIT;
    
    Commit complete.
    
    SQL>
  • Shrink the LOB segment.

    
    SQL> ALTER TABLE hr.t1 MODIFY LOB(a) (SHRINK SPACE);
    
    Table altered.
    
    SQL>
  • Display the number of extents or blocks freed.

    
    SQL> SELECT * FROM v$securefile_shrink WHERE LOB_OBJD=76063;
    
      LOB_OBJD SHRINK_STATUS
    ---------- ----------------------------------------
    START_TIME
    ---------------------------------------------------------------------------
    END_TIME
    ---------------------------------------------------------------------------
    BLOCKS_MOVED BLOCKS_FREED BLOCKS_ALLOCATED EXTENTS_ALLOCATED EXTENTS_FREED
    ------------ ------------ ---------------- ----------------- -------------
    EXTENTS_SEALED     CON_ID
    -------------- ----------
         76063 COMPLETE
    10-NOV-20 11.32.57.963 AM +00:00
    10-NOV-20 11.33.01.828 AM +00:00
            2648         2648             2648                 1            11
                11          3
    
         76063 COMPLETE
    10-NOV-20 11.30.55.545 AM +00:00
    10-NOV-20 11.30.55.917 AM +00:00
               2            2                2                 1             1
                 1          3
    
    SQL> 

    As a result, 2648 blocks are freed. Observe that the first row remains static.

  • Update the CLOB.

    
    SQL> UPDATE hr.t1 SET a=a||a;
    
    8 rows updated.
    
    SQL> COMMIT;
    
    Commit complete.
    
    SQL>
  • Shrink the LOB segment.

    
    SQL> ALTER TABLE hr.t1 MODIFY LOB(a) (SHRINK SPACE);
    
    Table altered.
    
    SQL>
  • Display the number of extents or blocks freed.

    
    SQL> SELECT * FROM v$securefile_shrink WHERE LOB_OBJD=76063;
    
      LOB_OBJD SHRINK_STATUS
    ---------- ----------------------------------------
    START_TIME
    ---------------------------------------------------------------------------
    END_TIME
    ---------------------------------------------------------------------------
    BLOCKS_MOVED BLOCKS_FREED BLOCKS_ALLOCATED EXTENTS_ALLOCATED EXTENTS_FREED
    ------------ ------------ ---------------- ----------------- -------------
    EXTENTS_SEALED     CON_ID
    -------------- ----------
         76063 COMPLETE
    15-DEC-20 01.08.44.404 PM +00:00
    15-DEC-20 01.08.48.270 PM +00:00
            2648         2648             2648                 1            11
                11          3
    
         76063 COMPLETE
    15-DEC-20 01.09.22.785 PM +00:00
    15-DEC-20 01.09.30.739 PM +00:00
            5523         5523             5523                 1            19
                19          3
    
    
    SQL> EXIT
    $

    As a result, 5523 blocks are freed. Observe that only the row of the previous shrinking operation is kept.