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.