Shrink the large lob objects in the Oracle database

Large Objects (LOBs) are a set of data types which designed to hold large amounts of data. A LOB size ranges from 8 terabytes to 128 terabytes depending on how your database is configured. Storing data in LOBs enables you to access and manipulate the data efficiently in your application.

Check the larger lob objects present in the Oracle Database

find the lob object having a larger size, the following query will return the top 10 larger size lob objects present in the database except for SYS, SYSTEM user.

SELECT *
FROM   (SELECT l.owner,
               l.table_name,
               l.column_name,
               l.segment_name,
               ROUND(s.bytes/1024/1024,2) size_mb
        FROM   dba_lobs l
               JOIN dba_segments s ON s.owner = l.owner AND s.segment_name = l.segment_name where l.owner not in ('SYS','SYSTEM')
        ORDER BY 5 DESC)
WHERE  ROWNUM <= 10;

Check whether the lob object is created as secure or basic:

select table_name, securefile from user_lobs where table_name like '%LOB%'; 

For Basic lob, you can shrink the lob objects with the following commands:

SQL> ALTER TABLE employee_proof MODIFY LOB(data) (SHRINK SPACE);

SQL> ALTER TABLE employee_proof MODIFY LOB(data) (SHRINK SPACE CASCADE);

For Secure lob, you can shrink it by the move command (even use the same tablespace):

ALTER TABLE employee_proof MOVE LOB(data) STORE AS (TABLESPACE users);

Syntax while creating basic or secure lob in create a table:

--use of basic lob
CREATE TABLE employee_proof (
  id NUMBER, data CLOB
) LOB(data) STORE AS BASICFILE (DISABLE STORAGE IN ROW);

--Use of Secure lob
CREATE TABLE employee_proof (
  id NUMBER,data CLOB
)  LOB(data) STORE AS SECUREFILE (DISABLE STORAGE IN ROW);