See Case Study #2 for syntax

Sample output:

3) Determine if the LOBSEGMENT is a candidate for shrink / reorganization

The following query will show the extents allocated for the LOBSEGMENT

SELECT BYTES, COUNT(*) FROM DBA_EXTENTS WHERE SEGMENT_NAME = ''  GROUP BY BYTES ORDER BY 2;

If NON Data Bytes is one or more of the extent sizes from this query ...
     then this segment is a candiate for a shrink / reorganization as this process will likely be able to reduce the size of the LOBSEGMENT by at least one extent

4) If #3 is true ... then the method in the following note may be used to shrink / reorganize the lob segment

     How to Shrink a SECUREFILE LOB using Online Redefinition (DBMS_REDEFINITION)? (Document  1394613.1)

NOTE: The securefile lob space allocation algorithm is designed such that extra space is allocated to prevent enq: HW Contention waits ... as such ... it may be found that after shrinking / reorganizing a LOB ... the
           extra space shrunk may be quickly reallocated in order to prevent waits ... this is normal behavior.


For an example of this process .. see  Case Study #2

CASE STUDY #1: BASICFILE

create user test identified by test;
grant dba to test;
alter user test default tablespace users;
connect test/test;

-- CREATE THE TEST TABLES

CREATE TABLE test ( ID NUMBER, PHOTO BLOB ) ;
CREATE TABLE test_bfile ( B_FILE BFILE) ;

ALTER TABLE TEST MODIFY LOB (PHOTO) (RETENTION);

-- CREATE THE DIRECTORY IN WHICH THE BLOB (PHOTO) RESIDES

CREATE or REPLACE DIRECTORY test as '/home/oracle/kbcook';

-- INSERT THE BFILE LOCATOR FOR THE PHOTO

insert into test_bfile values ( bfilename('TEST','1.jpg'));

commit;

-- INSERT 100 COPIES OF THE PHOTO INTO THE TEST TABLE

declare
    tmp_blob blob default EMPTY_BLOB();
    tmp_bfile bfile:=null;
    dest_offset integer:=1;
    src_offset integer:=1;
begin
     select b_file into tmp_bfile from test_bfile;
     DBMS_LOB.OPEN (tmp_bfile, DBMS_LOB.FILE_READONLY);
     dbms_lob.createtemporary(tmp_blob, TRUE);
     DBMS_LOB.LOADBLOBFROMFILE(tmp_blob,tmp_bfile,DBMS_LOB.LOBMAXSIZE,dest_offset,src_offset);
     for i in 1..100 loop
          insert into test values(i,tmp_blob);
          commit;
     end loop;
     DBMS_LOB.CLOSE(tmp_bfile);
end;
/

-- EXAMINE THE SIZE OF THE LOB DATA (BEFORE GENERATING UNDO)

SELECT SUM(DBMS_LOB.GETLENGTH(PHOTO)) "LOB DATA" FROM TEST;

  LOB DATA
----------
 102382400

-- GENERATE UNDO COPIES FOR 33 ROWS

DELETE FROM TEST WHERE (ID/3) = TRUNC(ID/3);

COMMIT;

-- #1 DETERMINE THE SEGMENT NAME OF THE COLUMN TO BE EXAMINED

SELECT SEGMENT_NAME FROM DBA_LOBS WHERE WNER = 'TEST' AND TABLE_NAME = 'TEST' AND COLUMN_NAME = 'PHOTO';

-- #2 DETERMINE THE STORAGE SIZE OF THE LOBSEGMENT

SELECT SUM(BYTES) "STORAGE" FROM DBA_EXTENTS WHERE SEGMENT_NAME = 'SYS_LOB0000067591C00002$$';

-- #3 DETERMINE THE SIZE OF THE LOB DATA

SELECT SUM(DBMS_LOB.GETLENGTH(PHOTO)) "LOB DATA" FROM TEST;

-- #4 DETERMINE THE SIZE OF THE DATA THAT IS NOT LOB DATA

SELECT 109051904-68596208 "non LOB DATA SIZE" FROM DUAL;

SELECT BYTES, COUNT(*) FROM DBA_EXTENTS WHERE SEGMENT_NAME = 'SYS_LOB0000067591C00002$$'  GROUP BY BYTES ORDER BY 2;

-- 5) DETERMINE IF THE LOBSEGMENT IS A CANDIDATE FOR SHRINK / REORGANIZATIONn

-- There are 40,455,696 bytes of storage that are being used by LOB data ... there are many extents small than this size ... this LOBSEGMENT is a candidate for shrink / reorganization

-- 6) If #5 is true ... then one of the methods in the following note may be used to shrink / reorganize the lob segment
--     How to Shrink (make less sparse) a LOB (BASICFILE or SECUREFILE)? (Document 1451124.1)

ALTER TABLE TEST ENABLE ROW MOVEMENT;

ALTER TABLE TEST SHRINK SPACE CASCADE;

SELECT SUM(BYTES) FROM DBA_EXTENTS WHERE SEGMENT_NAME = 'SYS_LOB0000067591C00002$$';

CASE STUDY #2: SECUREFILES

create user test identified by test;
grant dba to test;
alter user test default tablespace users;
connect test/test;

-- CREATE THE TEST TABLES

CREATE TABLE test ( ID NUMBER, PHOTO BLOB) LOB (PHOTO) STORE AS SECUREFILE ;
CREATE TABLE test_bfile ( B_FILE BFILE) ;

ALTER TABLE TEST MODIFY LOB (PHOTO) (RETENTION);

-- CREATE THE DIRECTORY IN WHICH THE BLOB (PHOTO) RESIDES

CREATE or REPLACE DIRECTORY test as '/home/oracle/kbcook';

-- INSERT THE BFILE LOCATOR FOR THE PHOTO

insert into test_bfile values ( bfilename('TEST','1.jpg'));

commit;

-- INSERT 100 COPIES OF THE PHOTO INTO THE TEST TABLE

declare
    tmp_blob blob default EMPTY_BLOB();
    tmp_bfile bfile:=null;
    dest_offset integer:=1;
    src_offset integer:=1;
begin
     select b_file into tmp_bfile from test_bfile;
     DBMS_LOB.OPEN (tmp_bfile, DBMS_LOB.FILE_READONLY);
     dbms_lob.createtemporary(tmp_blob, TRUE);
     DBMS_LOB.LOADBLOBFROMFILE(tmp_blob,tmp_bfile,DBMS_LOB.LOBMAXSIZE,dest_offset,src_offset);
     for i in 1..100 loop
          insert into test values(i,tmp_blob);
          commit;
     end loop;
     DBMS_LOB.CLOSE(tmp_bfile);
end;
/

-- CREATE A PROCEDURE TO EXAMINE THE SPACE USAGE OF THE SECUREFILE LOB SEGMENT

SET SERVEROUTPUT ON

CREATE OR REPLACE PROCEDURE check_space_securefile (u_name in varchar2, v_segname varchar2 ) IS
     l_segment_size_blocks NUMBER;
     l_segment_size_bytes NUMBER;
     l_used_blocks NUMBER;
     l_used_bytes NUMBER;
     l_expired_blocks NUMBER;
     l_expired_bytes NUMBER;
     l_unexpired_blocks NUMBER;
     l_unexpired_bytes NUMBER;
     l_unused_blocks NUMBER;
     l_unused_bytes NUMBER;
     l_non_data_blocks NUMBER;
     l_non_data_bytes NUMBER;

     BEGIN
          DBMS_SPACE.SPACE_USAGE( segment_owner =>u_name,
                                  segment_name => v_segname,
                                  segment_type => 'LOB',
                                  segment_size_blocks => l_segment_size_blocks,
                                  segment_size_bytes => l_segment_size_bytes,
                                  used_blocks => l_used_blocks,
                                  used_bytes => l_used_bytes,
                                  expired_blocks => l_expired_blocks,
                                  expired_bytes => l_expired_bytes,
                                  unexpired_blocks => l_unexpired_blocks,
                                  unexpired_bytes => l_unexpired_bytes
           );

          l_unused_blocks := l_segment_size_blocks - (l_used_blocks + l_expired_blocks + l_unexpired_blocks);
          l_unused_bytes := l_segment_size_bytes - (l_used_bytes + l_expired_bytes + l_unexpired_bytes);

          l_non_data_blocks := l_unused_blocks + l_expired_blocks + l_unexpired_blocks;
          l_non_data_bytes :=  l_unused_bytes + l_expired_bytes + l_unexpired_bytes;

          DBMS_OUTPUT.ENABLE;
          DBMS_OUTPUT.PUT_LINE(' Segment Blocks/Bytes   = '||l_segment_size_blocks||' / '||l_segment_size_bytes);
          DBMS_OUTPUT.PUT_LINE(' Unused Blocks/Bytes    = '||l_unused_blocks||' / '||l_unused_bytes);
          DBMS_OUTPUT.PUT_LINE(' Used Blocks/Bytes      = '||l_used_blocks||' / '||l_used_bytes);
          DBMS_OUTPUT.PUT_LINE(' Expired Blocks/Bytes   = '||l_expired_blocks||' / '||l_expired_bytes);
          DBMS_OUTPUT.PUT_LINE(' Unexpired Blocks/Bytes = '||l_unexpired_blocks||' / '||l_unexpired_bytes);
          DBMS_OUTPUT.PUT_LINE('===========================================================================');
          DBMS_OUTPUT.PUT_LINE(' NON Data Blocks/Bytes  = '||l_non_data_blocks||' / '||l_non_data_bytes);
     END;
/

-- #1 DETERMINE THE SEGMENT NAME OF THE COLUMN TO BE EXAMINED

SELECT SEGMENT_NAME FROM DBA_LOBS WHERE WNER = 'TEST' AND TABLE_NAME = 'TEST' AND COLUMN_NAME = 'PHOTO';

-- EXAMINE THE STORAGE USAGE IN THE LOBSEGMENT USING DBMS_SPACE_USAGE (BEFORE GENERATING UNDO)

exec check_space_securefile('TEST','SYS_LOB0000067626C00002$$');

-- GENERATE UNDO COPIES FOR 33 ROWS

DELETE FROM TEST WHERE (ID/3) = TRUNC(ID/3);

COMMIT;

-- #2 DETERMINE THE STORAGE USAGE IN THE LOBSEGMENT USING DBMS_SPACE_USAGE

exec check_space_securefile('TEST','SYS_LOB0000067626C00002$$');

-- #3 DETERMINE IF THE LOBSEGMENT IS A CANDIDATE FOR SHRINK / REORGANIZATION

SELECT BYTES, COUNT(*) FROM DBA_EXTENTS WHERE SEGMENT_NAME = 'SYS_LOB0000067626C00002$$'  GROUP BY BYTES ORDER BY 2;

-- There are 52,625,408 bytes bytes of storage that are being used by LOB data ... there are many extents smaller than this size ... this LOBSEGMENT is a candidate for shrink / reorganization

-- 4) If #3 is true ... then the method in the following note may be used to shrink / reorganize the lob segment

--      How to Shrink a SECUREFILE LOB using Online Redefinition (DBMS_REDEFINITION)? (Document  1394613.1)

-- DETERMINE IF THE TABLE CAN BE REDEFINED ONLINE

BEGIN
    DBMS_REDEFINITION.CAN_REDEF_TABLE('TEST','TEST', DBMS_REDEFINITION.CONS_USE_ROWID);
END;
/

PL/SQL procedure successfully completed.

-- CREATE THE INTERIM TABLE

CREATE TABLE interim ( ID NUMBER, PHOTO BLOB) LOB (PHOTO) STORE AS SECUREFILE ;
ALTER TABLE interim MODIFY LOB (PHOTO) (RETENTION);

-- START THE REDEFINITION

BEGIN
   DBMS_REDEFINITION.START_REDEF_TABLE(
                uname => 'TEST',
                orig_table => 'TEST',
                int_table => 'INTERIM',
                options_flag => DBMS_REDEFINITION.CONS_USE_ROWID);
END;
/

-- FINISH THE REDEFINITION

exec DBMS_REDEFINITION.FINISH_REDEF_TABLE('TEST','TEST','INTERIM');

-- DROP THE INTERIM TABLE

DROP TABLE INTERIM;

--  THE REDEFINITION HAS CREATED A NEW TABLE AS SUCH THE SEGMENT NAME OF THE COLUMN OF THE NEW TABLE TO BE EXAMINED NEEDS TO BE DETERMINED

SELECT SEGMENT_NAME FROM DBA_LOBS WHERE WNER = 'TEST' AND TABLE_NAME = 'TEST' AND COLUMN_NAME = 'PHOTO';

SEGMENT_NAME
------------------------------
SYS_LOB0000067633C00002$$

exec check_space_securefile('TEST','SYS_LOB0000067633C00002$$');

References