Reclaiming Unused LOB(BLOB) Space With OutSystems

Reclaiming Unused LOB(BLOB) Space With OutSystems

  

I was questioned by the client, after performing the cleaning of the data using the DBCleaner in the Oracle database, the follow-up of LOBs (BLOBs) was not shrinked. In this article I went through the step-by-step process of solving the problem.

-- A test was performed using Oracle Database 12c Enterprise Edition Release 12.1.0.2.0. A copy table of another was created with the LOB field.

SQL>CREATE TABLE OSADMIN.OSSYS_ESPACE_VERSION_BKP AS SELECT * FROM OSADMIN.OSSYS_ESPACE_VERSION;

-- Query of the segment LOBs

SQL> SELECT *

  FROM (  SELECT b.owner,

                 b.table_name,

                 b.column_name,

                 a.segment_name,

                 SUM (a.bytes) / (1024 * 1024) "size"

            FROM dba_segments a, dba_lobs b

           WHERE     a.segment_name LIKE 'SYS_LOB%'

                 AND a.segment_name = b.segment_name

                 AND a.owner NOT IN ('SYS', 'SYSTEM', 'DBSNMP', 'OUTLN')

                 AND TABLE_NAME='OSSYS_ESPACE_VERSION_BKP'

        GROUP BY b.owner,

                 b.table_name,

                 b.column_name,

                 a.segment_name

        ORDER BY 5 DESC)

WHERE ROWNUM < 11;


-- The result of the query before (Query of the segment LOBs)


--

SQL> DELETE FROM OSADMIN.OSSYS_ESPACE_VERSION_BKP;

32 linhas excluído.

SQL>commit;

-- The result of the query after the delete, was the same size. (Query of the segment LOBs)

Solution

-- Criate a new tablespace.

sql> create tablespace OSLOB datafile '/u01/app/oracle/product/12.1.0.2/db_1/dbs/OUTSYSTEMS_LOB01.dbf' size 1G autoextend on next 200M maxsize 10G;

sql> ALTER USER OSADMIN QUOTA 100M ON OSLOB;

sql> GRANT UNLIMITED TABLESPACE TO OSADMIN;

sql> alter table OSADMIN.OSSYS_ESPACE_VERSION_BKP enable row movement;

Sql> alter table OSADMIN.OSSYS_ESPACE_VERSION_BKP enable row movement;

--

sql> ALTER TABLE OSADMIN.OSSYS_ESPACE_VERSION_BKP MOVE LOB(oml_file) STORE AS (TABLESPACE OSLOB);

SQL> alter table OSADMIN.OSSYS_ESPACE_VERSION_BKP shrink space compact;

-- The result of the query after the shrink, the size has been reduced. (Query of the segment LOBs)


sql> INSERT INTO OSADMIN.OSSYS_ESPACE_VERSION_BKP3

(ID,VERSION,OML_FILE,UPLOADED_BY,UPLOADED_DATE,ESPACE_ID,IS_VALID,SERVICE_STUDIO_VERSION,BASED_ON_PREVIOUS,DESCRIPTION,LAST_UPGRADE_VERSION,         

PUBLISHING_ID,HASH,LAST_MODIFIED,HAS_HTTPS,HAS_INTEGRATEDAUTHENTICATION,HAS_SMS,HAS_WEBSERVICES,HAS_HTTPSCLIENTCERTIFICATES,DBCATALOG_ID,                

IS_USER_PROVIDER,USER_PROVIDER_KEY,USER_PROVIDER_NAME,ESPACE_NAME,OMLHASH,OMLHASHVERSION,HMAC,HMACVERSION,LASTUPDATEDATE,DIRECT_UPGRADE_FROM_HASH,     

GENERALHASH,JQUERYVERSION,HAS_PUBLICELEMENTS,DEFAULT_THEME_IS_MOBILE)

SELECT ID,VERSION,OML_FILE,UPLOADED_BY,UPLOADED_DATE,ESPACE_ID,IS_VALID,SERVICE_STUDIO_VERSION,BASED_ON_PREVIOUS,DESCRIPTION,LAST_UPGRADE_VERSION,         

PUBLISHING_ID,HASH,LAST_MODIFIED,HAS_HTTPS,HAS_INTEGRATEDAUTHENTICATION,HAS_SMS,HAS_WEBSERVICES,HAS_HTTPSCLIENTCERTIFICATES,DBCATALOG_ID,                 

IS_USER_PROVIDER,USER_PROVIDER_KEY,USER_PROVIDER_NAME,ESPACE_NAME,OMLHASH,OMLHASHVERSION,HMAC,HMACVERSION,LASTUPDATEDATE,DIRECT_UPGRADE_FROM_HASH,     

GENERALHASH,JQUERYVERSION,HAS_PUBLICELEMENTS,DEFAULT_THEME_IS_MOBILE 

FROM OSADMIN.OSSYS_ESPACE_VERSION;]

-- The result of the query after the insert. (Query of the segment LOBs)

SQL> DELETE FROM OSADMIN.OSSYS_ESPACE_VERSION_BKP3;

32 linhas excluído.

SQL>commit;

SQL>ALTER TABLE OSADMIN.OSSYS_ESPACE_VERSION_BKP3 MOVE LOB(oml_file) STORE AS (TABLESPACE OSLOB);

-- The resulting query after the MOVE LOB. (Query of the segment LOBs)



Solution

Hey Guilherme Henrique Castro Silva, thanks so much for posting your problem and solution! 

Cheers for contributing to the community,

-Alexandra

Solution