-- +----------------------------------------------------------------------------+ -- | Jeffrey M. Hunter | -- | jhunter@idevelopment.info | -- | www.idevelopment.info | -- |----------------------------------------------------------------------------| -- | Copyright (c) 1998-2012 Jeffrey M. Hunter. All rights reserved. | -- |----------------------------------------------------------------------------| -- | DATABASE : Oracle | -- | FILE : lob_fragmentation_user.sql | -- | CLASS : LOBs | -- | PURPOSE : When a LOB segment is first created, its initial "allocated" | -- | size is 64K, even though there are no rows in the table. As LOB | -- | data is entered into the LOB segment, the allocated space for | -- | the segment will continue to increase. If over time, the LOB | -- | segment starts to experience many deletes and updates, it is | -- | possible for the LOB segment to become fragmented and possibly | -- | waste a considerable amount of disk space. This occurs when the | -- | size of the actual LOB segment data is considerably less than | -- | what is allocated by the LOB segment. Consider a situation | -- | where a LOB segment has 16GB allocated for the segment but only | -- | contains 2GB worth of actual LOB data. Potentially, this is | -- | nearly 14GB of wasted allocated space. This could occur when a | -- | significant number of rows have been deleted from the table | -- | storing the LOB column. | -- | | -- | This script can be used to identify the size and amount of | -- | fragmentation that exists in all LOB segments for a particular | -- | user. | -- | | -- | To reclaim the wasted space within a fragmented LOB segment, | -- | use the following SQL command: | -- | | -- | ALTER TABLE . MODIFY LOB () (SHRINK SPACE); -- | | -- | NOTE: The time required to shrink the LOB segment is a function | -- | of how much data needs to be coalesced. In many cases, | -- | the shrink operation can take minutes or possibly several | -- | hours depending on the amount of data that needs to be | -- | moved. | -- | | -- | NOTE : As with any code, ensure to test this script in a development | -- | environment before attempting to run it in production. | -- +----------------------------------------------------------------------------+ SET TERMOUT OFF; COLUMN current_instance NEW_VALUE current_instance NOPRINT; COLUMN current_user NEW_VALUE current_user NOPRINT; SELECT rpad(instance_name, 17) current_instance, rpad(user, 13) current_user FROM v$instance; SET TERMOUT ON; PROMPT PROMPT +------------------------------------------------------------------------+ PROMPT | Report : LOB Fragmentation for the Current User | PROMPT | Instance : ¤t_instance | PROMPT | User : ¤t_user | PROMPT +------------------------------------------------------------------------+ PROMPT SET ECHO OFF SET FEEDBACK 6 SET HEADING ON SET LINESIZE 180 SET PAGESIZE 50000 SET TERMOUT ON SET SERVEROUTPUT ON SET TIMING OFF SET TRIMOUT ON SET TRIMSPOOL ON SET VERIFY OFF CLEAR COLUMNS CLEAR BREAKS CLEAR COMPUTES declare v_actual_length number; v_allocated_length number; v_lob_fragmentation_pct number; v_actual_length_char varchar2(50); v_allocated_length_char varchar2(50); v_statement varchar2(2000); v_table_column_pad_length constant number := 45; v_actual_length_pad_length constant number := 20; v_allocated_length_pad_length constant number := 20; v_fragmentation_pad_length constant number := 15; begin dbms_output.enable(1000000); -- Print column headers dbms_output.put_line( rpad('LOB COLUMN - [OWNER.TABLE.COLUMN]', v_table_column_pad_length) || ' ' || lpad('ALLOCATED LOB LENGTH', v_allocated_length_pad_length) || ' ' || lpad('ACTUAL LOB LENGTH', v_actual_length_pad_length) || ' ' || lpad('FRAGMENTATION', v_fragmentation_pad_length) ); dbms_output.put_line( rpad('-', v_table_column_pad_length, '-') || ' ' || lpad('-', v_allocated_length_pad_length, '-') || ' ' || lpad('-', v_actual_length_pad_length, '-') || ' ' || lpad('-', v_fragmentation_pad_length, '-') ); -- Get all LOB segments for the current user for v_lob_segment in (select user, l.table_name, l.column_name from user_lobs l join user_segments s using (segment_name, tablespace_name) where l.column_name not like '"%' order by 2,3) loop dbms_output.put(rpad(v_lob_segment.user || '.' || v_lob_segment.table_name || '.' || v_lob_segment.column_name, v_table_column_pad_length)); dbms_output.put(' '); -- Get "allocated size" of the LOB segment v_statement := 'begin ' || 'select to_char(a.bytes, ''999,999,999,999,999'') ' || 'into :col_val2 ' || 'from user_segments a join user_lobs b ' || 'using (segment_name) ' || 'where b.table_name = ''' || v_lob_segment.table_name || '''' || ' and b.column_name = ''' || v_lob_segment.column_name || ''';' || 'end;'; execute immediate v_statement using out v_allocated_length_char; v_allocated_length_char := regexp_replace(v_allocated_length_char, ' ', ''); v_allocated_length := regexp_replace(v_allocated_length_char, ',', ''); dbms_output.put(lpad(v_allocated_length_char, v_allocated_length_pad_length)); dbms_output.put(' '); begin -- Get "actual size" of the LOB segment v_statement := 'begin ' || 'select to_char(sum(dbms_lob.getlength(' || v_lob_segment.column_name || ')), ''999,999,999,999,999'' ) ' || 'into :col_val1 ' || 'from ' || v_lob_segment.table_name || ';' || 'end;'; execute immediate v_statement using out v_actual_length_char; v_actual_length_char := nvl(regexp_replace(v_actual_length_char, ' ', ''), '0'); v_actual_length := nvl(regexp_replace(v_actual_length_char, ',', ''), 0); dbms_output.put(lpad(v_actual_length_char, v_actual_length_pad_length)); dbms_output.put(' '); -- Calculate LOB fragmentation if v_actual_length = 0 then v_actual_length := v_allocated_length; end if; v_lob_fragmentation_pct := round(((1-(v_actual_length/v_allocated_length))*100), 2); dbms_output.put(lpad(v_lob_fragmentation_pct || ' %', v_fragmentation_pad_length)); exception when others then null; end; dbms_output.put_line(''); end loop; end; /