Compact blob Oracle data, compacting tables with BLOB objects

BLOBs tables with different tablespaces in the remainder of the table, have a somewhat different treatment.

We will prepare a test environment:

We create two tablespaces with:

Source   
CREATE SMALLFILE TABLESPACE "TS_PFIRMA_DATOS" LOGGING DATAFILE  '/u01/app/oracle/oradata/PRUEBA/TS_PFIRMA_DATOS.dbf' SIZE 10M EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
CREATE SMALLFILE TABLESPACE "TS_PFIRMA_BLOB" LOGGING DATAFILE '/u01/app/oracle/oradata/PRUEBA/TS_PFIRMA_BLOB.dbf' SIZE 10M EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;

The table:

Source   
CREATE TABLE "SCOTT"."PF_ARC"
(
"is" NUMBER(10,0) NOT NULL ENABLE,
"B_ARCHIVO" BLOB
)
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE
(
INITIAL 100000 NEXT 100000 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT
)
TABLESPACE "TS_PFIRMA_DATOS" LOB
(
"B_ARCHIVO"
)
STORE AS
(
TABLESPACE "TS_PFIRMA_BLOB" DISABLE STORAGE IN ROW CHUNK 32768 PCTVERSION 10 NOCACHE LOGGING STORAGE(INITIAL 100000 NEXT 100000 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
) ;

If we add some records in this table we can see the actual size of the BLOB field with:

Source   
SQL> SELECT sum(dbms_lob.getlength(s.B_ARCHIVO)) FROM SCOTT.PF_ARC s;
SUM(DBMS_LOB.GETLENGTH(S.B_ARCHIVO))
------------------------------------
5441368

With the number of extensions:

Source   
SQL> SELECT e.owner, e.segment_name,e.segment_type, count(*) FROM dba_extents e WHERE e.TABLESPACE_NAME='TS_PFIRMA_BLOB' GROUP BY e.owner, e.segment_name,e.segment_type;
OWNER                          SEGMENT_NAME                                                                SEGMENT_TYPE COUNT(*)
------------------------------ ------------------------------------------------------------------------- ------------------------ --------------
SCOTT                            SYS_LOB0000052624C00002$$                                            LOBSEGMENT     21
SCOTT                            SYS_IL0000052624C00002$$                                                LOBINDEX            1

If records are deleted can see that change is reflected in the actual size of the BLOB field:

Source   
SQL> SELECT sum(dbms_lob.getlength(s.B_ARCHIVO)) FROM SCOTT.PF_ARC s;
SUM(DBMS_LOB.GETLENGTH(S.B_ARCHIVO))
------------------------------------
171349

Although the number of extensions being equal, the tablespace does not free space this can be fixed with:

Source   
ALTER TABLE scott.PF_ARC enable row movement;
ALTER TABLE scott.PF_ARC shrink space cascade;

Once executed this, the extensions are released:

Source   
SQL> SELECT e.owner, e.segment_name,e.segment_type, count(*) FROM dba_extents e WHERE e.TABLESPACE_NAME='TS_PFIRMA_BLOB' GROUP BY e.owner, e.segment_name,e.segment_type;
OWNER                          SEGMENT_NAME                                                                SEGMENT_TYPE COUNT(*)
------------------------------ ------------------------------------------------------------------------- ------------------------ --------------
SCOTT                            SYS_LOB0000052624C00002$$                                            LOBSEGMENT     8
SCOTT                            SYS_IL0000052624C00002$$                                                LOBINDEX            1

As in any compaction, you are needed reconstruction of all indexes related to the given table.

Leave a Reply