oracle用户下对象碎片排查

检查用户下哪些表有碎片

--How to Find Fragmentation for Tables and LOBs KB138882

SET SERVEROUTPUT ON SIZE UNLIMITED 
SET LINESIZE 200 
SET PAGESIZE 1000 
SET VERIFY OFF 

DECLARE 
v_schema VARCHAR2(30) := UPPER('&schema_name'); 

-- Variables for space usage 
v_unformatted_blocks NUMBER; 
v_unformatted_bytes NUMBER; 
v_fs1_blocks NUMBER; 
v_fs1_bytes NUMBER; 
v_fs2_blocks NUMBER; 
v_fs2_bytes NUMBER; 
v_fs3_blocks NUMBER; 
v_fs3_bytes NUMBER; 
v_fs4_blocks NUMBER; 
v_fs4_bytes NUMBER; 
v_full_blocks NUMBER; 
v_full_bytes NUMBER; 

-- Variables for summary 
v_total_blocks NUMBER := 0; 
v_total_fragmented_blocks NUMBER := 0; 
v_fragmentation_percent NUMBER := 0; 

-- Cursor for all tables in the schema 
CURSOR c_tables IS 
SELECT table_name 
FROM all_tables 
WHERE owner = v_schema 
ORDER BY table_name; 

BEGIN 
DBMS_OUTPUT.PUT_LINE('=================================================================='); 
DBMS_OUTPUT.PUT_LINE('Fragmentation Analysis for Schema: ' || v_schema); 
DBMS_OUTPUT.PUT_LINE('=================================================================='); 
DBMS_OUTPUT.PUT_LINE(RPAD('Table Name', 30) || 
LPAD('Unformatted', 12) || 
LPAD('FS1', 10) || 
LPAD('FS2', 10) || 
LPAD('FS3', 10) || 
LPAD('FS4', 10) || 
LPAD('Full', 10) || 
LPAD('Frag%', 10)); 
DBMS_OUTPUT.PUT_LINE('------------------------------------------------------------------'); 

FOR r_table IN c_tables LOOP 
BEGIN 
-- Get space usage for the table 
DBMS_SPACE.SPACE_USAGE( 
segment_owner => v_schema, 
segment_name => r_table.table_name, 
segment_type => 'TABLE', 
unformatted_blocks => v_unformatted_blocks, 
unformatted_bytes => v_unformatted_bytes, 
fs1_blocks => v_fs1_blocks, 
fs1_bytes => v_fs1_bytes, 
fs2_blocks => v_fs2_blocks, 
fs2_bytes => v_fs2_bytes, 
fs3_blocks => v_fs3_blocks, 
fs3_bytes => v_fs3_bytes, 
fs4_blocks => v_fs4_blocks, 
fs4_bytes => v_fs4_bytes, 
full_blocks => v_full_blocks, 
full_bytes => v_full_bytes); 

-- Calculate fragmentation percentage (FS1-FS4 as fragmented) 
IF (v_full_blocks + v_fs1_blocks + v_fs2_blocks + v_fs3_blocks + v_fs4_blocks) > 0 THEN 
v_fragmentation_percent := ROUND( 
(v_fs1_blocks + v_fs2_blocks + v_fs3_blocks + v_fs4_blocks) / 
(v_full_blocks + v_fs1_blocks + v_fs2_blocks + v_fs3_blocks + v_fs4_blocks) * 100, 2); 
ELSE 
v_fragmentation_percent := 0; 
END IF; 

-- Output table information 
DBMS_OUTPUT.PUT_LINE( 
RPAD(r_table.table_name, 30) || 
LPAD(v_unformatted_blocks, 12) || 
LPAD(v_fs1_blocks, 10) || 
LPAD(v_fs2_blocks, 10) || 
LPAD(v_fs3_blocks, 10) || 
LPAD(v_fs4_blocks, 10) || 
LPAD(v_full_blocks, 10) || 
LPAD(v_fragmentation_percent, 10)); 

-- Accumulate totals 
v_total_blocks := v_total_blocks + v_full_blocks + v_fs1_blocks + v_fs2_blocks + v_fs3_blocks + v_fs4_blocks; 
v_total_fragmented_blocks := v_total_fragmented_blocks + v_fs1_blocks + v_fs2_blocks + v_fs3_blocks + v_fs4_blocks; 

EXCEPTION 
WHEN OTHERS THEN 
DBMS_OUTPUT.PUT_LINE('Error analyzing table ' || r_table.table_name || ': ' || SQLERRM); 
END; 
END LOOP; 

-- Calculate overall fragmentation 
IF v_total_blocks > 0 THEN 
v_fragmentation_percent := ROUND((v_total_fragmented_blocks / v_total_blocks) * 100, 2); 
ELSE 
v_fragmentation_percent := 0; 
END IF; 

-- Output summary 
DBMS_OUTPUT.PUT_LINE('------------------------------------------------------------------'); 
DBMS_OUTPUT.PUT_LINE('TOTAL BLOCKS: ' || v_total_blocks || 
', FRAGMENTED BLOCKS: ' || v_total_fragmented_blocks || 
', FRAGMENTATION: ' || v_fragmentation_percent || '%'); 
DBMS_OUTPUT.PUT_LINE('=================================================================='); 

-- Additional recommendations 
IF v_fragmentation_percent > 30 THEN 
DBMS_OUTPUT.PUT_LINE('WARNING: High fragmentation detected (>30%). Consider reorganizing tables with high fragmentation.'); 
DBMS_OUTPUT.PUT_LINE('Actions to consider:'); 
DBMS_OUTPUT.PUT_LINE('1. ALTER TABLE ... MOVE for tables with high fragmentation'); 
DBMS_OUTPUT.PUT_LINE('2. Export/Import for very large tables'); 
DBMS_OUTPUT.PUT_LINE('3. Online table redefinition for minimal downtime'); 
ELSIF v_fragmentation_percent > 10 THEN 
DBMS_OUTPUT.PUT_LINE('NOTE: Moderate fragmentation detected (>10%). Monitor tables with high fragmentation.'); 
ELSE 
DBMS_OUTPUT.PUT_LINE('NOTE: Fragmentation level is acceptable.'); 
END IF; 

EXCEPTION 
WHEN OTHERS THEN 
DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM); 
END; 
/

直接将这段代码保存为3.sql，执行效果如下：

输入用户名A后查到一些表的碎片情况
轻量级的碎片治理方法可能首选shrink space，是否能收缩到指定大小呢？可以先评估一下

SET SERVEROUTPUT ON
DECLARE
    l_can_shrink BOOLEAN;
BEGIN
    -- 检查 'SCOTT' 用户下的 'EMP' 表是否适合收缩
    l_can_shrink := DBMS_SPACE.VERIFY_SHRINK_CANDIDATE (
        segment_owner => 'A',
        segment_name  => 'TEST',
        segment_type  => 'TABLE'  -- 也可以是 'INDEX'
		,SHRINK_TARGET_BYTES=>1073741824 -- Shrink to 1GB 
    );

    IF l_can_shrink THEN
        DBMS_OUTPUT.PUT_LINE('该表适合进行 SHRINK 操作。');
    ELSE
        DBMS_OUTPUT.PUT_LINE('该表不适合进行 SHRINK 操作，请检查。');
    END IF;
END;
/

输出是否适合shrink

需要注意的是，别写错了用户名和表名，否则会直接提示不适合，其实是表不存在。
表名写对了就提示：

最后再赠送一个纵向查看对象的碎片脚本(不如上面的直观，且会在库里创建函数)

-- more info at http://tanelpoder.com
create FUNCTION get_space_usage(owner IN VARCHAR2, object_name IN VARCHAR2, segment_type IN VARCHAR2, partition_name IN VARCHAR2 DEFAULT NULL) RETURN sys.DBMS_DEBUG_VC2COLL PIPELINED
    AS
        ufbl   NUMBER; 
        ufby   NUMBER;
        fs1bl  NUMBER;
        fs1by  NUMBER;
        fs2bl  NUMBER;
        fs2by  NUMBER;
        fs3bl  NUMBER;
        fs3by  NUMBER;
        fs4bl  NUMBER;
        fs4by  NUMBER;
        fubl   NUMBER;
        fuby   NUMBER;       
    BEGIN
        DBMS_SPACE.SPACE_USAGE(owner,object_name,segment_type, ufbl, ufby, fs1bl,fs1by, fs2bl,fs2by, fs3bl,fs3by, fs4bl,fs4by, fubl,fuby, partition_name);
        PIPE ROW('Full blocks       /MB '||TO_CHAR(fubl,  '999999999')||' '||TO_CHAR(fuby  /1048576,'999999999'));
        PIPE ROW('Unformatted blocks/MB '||TO_CHAR(ufbl,  '999999999')||' '||TO_CHAR(ufby  /1048576,'999999999'));
        PIPE ROW('Free Space      0-25% '||TO_CHAR(fs1bl, '999999999')||' '||TO_CHAR(fs1by /1048576,'999999999'));
        PIPE ROW('Free Space     25-50% '||TO_CHAR(fs2bl, '999999999')||' '||TO_CHAR(fs2by /1048576,'999999999'));
        PIPE ROW('Free Space     50-75% '||TO_CHAR(fs3bl, '999999999')||' '||TO_CHAR(fs3by /1048576,'999999999'));
        PIPE ROW('Free Space    75-100% '||TO_CHAR(fs4bl, '999999999')||' '||TO_CHAR(fs4by /1048576,'999999999'));
    END get_space_usage;
	
col frag_info for a50
select COLUMN_VALUE as frag_info from table(get_space_usage('A','TE','TABLE'));

其他参考
https://blog.csdn.net/x6_9x/article/details/50596589
https://www.cnblogs.com/shunqian/p/17604590.html