oracle用户下对象碎片排查

检查用户下哪些表有碎片--How to Find Fragmentation for Tables and LOBs KB138882SETSERVEROUTPUTONSIZE UNLIMITEDSETLINESIZE200SETPAGESIZE1000SETVERIFYOFFDECLAREv_schema VARCHAR2(30):UPPER(schema_name);-- Variables for space usagev_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 summaryv_total_blocks NUMBER :0;v_total_fragmented_blocks NUMBER :0;v_fragmentation_percent NUMBER :0;-- Cursor for all tables in the schemaCURSORc_tablesISSELECTtable_nameFROMall_tablesWHEREownerv_schemaORDERBYtable_name;BEGINDBMS_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(------------------------------------------------------------------);FORr_tableINc_tablesLOOPBEGIN-- Get space usage for the tableDBMS_SPACE.SPACE_USAGE(segment_ownerv_schema,segment_namer_table.table_name,segment_typeTABLE,unformatted_blocksv_unformatted_blocks,unformatted_bytesv_unformatted_bytes,fs1_blocksv_fs1_blocks,fs1_bytesv_fs1_bytes,fs2_blocksv_fs2_blocks,fs2_bytesv_fs2_bytes,fs3_blocksv_fs3_blocks,fs3_bytesv_fs3_bytes,fs4_blocksv_fs4_blocks,fs4_bytesv_fs4_bytes,full_blocksv_full_blocks,full_bytesv_full_bytes);-- Calculate fragmentation percentage (FS1-FS4 as fragmented)IF(v_full_blocksv_fs1_blocksv_fs2_blocksv_fs3_blocksv_fs4_blocks)0THENv_fragmentation_percent :ROUND((v_fs1_blocksv_fs2_blocksv_fs3_blocksv_fs4_blocks)/(v_full_blocksv_fs1_blocksv_fs2_blocksv_fs3_blocksv_fs4_blocks)*100,2);ELSEv_fragmentation_percent :0;ENDIF;-- Output table informationDBMS_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 totalsv_total_blocks :v_total_blocksv_full_blocksv_fs1_blocksv_fs2_blocksv_fs3_blocksv_fs4_blocks;v_total_fragmented_blocks :v_total_fragmented_blocksv_fs1_blocksv_fs2_blocksv_fs3_blocksv_fs4_blocks;EXCEPTIONWHENOTHERSTHENDBMS_OUTPUT.PUT_LINE(Error analyzing table ||r_table.table_name||: ||SQLERRM);END;ENDLOOP;-- Calculate overall fragmentationIFv_total_blocks0THENv_fragmentation_percent :ROUND((v_total_fragmented_blocks/v_total_blocks)*100,2);ELSEv_fragmentation_percent :0;ENDIF;-- Output summaryDBMS_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 recommendationsIFv_fragmentation_percent30THENDBMS_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_percent10THENDBMS_OUTPUT.PUT_LINE(NOTE: Moderate fragmentation detected (10%). Monitor tables with high fragmentation.);ELSEDBMS_OUTPUT.PUT_LINE(NOTE: Fragmentation level is acceptable.);ENDIF;EXCEPTIONWHENOTHERSTHENDBMS_OUTPUT.PUT_LINE(Error: ||SQLERRM);END;/直接将这段代码保存为3.sql执行效果如下输入用户名A后查到一些表的碎片情况轻量级的碎片治理方法可能首选shrink space是否能收缩到指定大小呢可以先评估一下SETSERVEROUTPUTONDECLAREl_can_shrinkBOOLEAN;BEGIN-- 检查 SCOTT 用户下的 EMP 表是否适合收缩l_can_shrink :DBMS_SPACE.VERIFY_SHRINK_CANDIDATE(segment_ownerA,segment_nameTEST,segment_typeTABLE-- 也可以是 INDEX,SHRINK_TARGET_BYTES1073741824-- Shrink to 1GB);IFl_can_shrinkTHENDBMS_OUTPUT.PUT_LINE(该表适合进行 SHRINK 操作。);ELSEDBMS_OUTPUT.PUT_LINE(该表不适合进行 SHRINK 操作请检查。);ENDIF;END;/输出是否适合shrink需要注意的是别写错了用户名和表名否则会直接提示不适合其实是表不存在。表名写对了就提示最后再赠送一个纵向查看对象的碎片脚本(不如上面的直观且会在库里创建函数)-- more info at http://tanelpoder.comcreateFUNCTIONget_space_usage(ownerINVARCHAR2,object_nameINVARCHAR2,segment_typeINVARCHAR2,partition_nameINVARCHAR2DEFAULTNULL)RETURNsys.DBMS_DEBUG_VC2COLL PIPELINEDASufbl NUMBER;ufby NUMBER;fs1bl NUMBER;fs1by NUMBER;fs2bl NUMBER;fs2by NUMBER;fs3bl NUMBER;fs3by NUMBER;fs4bl NUMBER;fs4by NUMBER;fubl NUMBER;fuby NUMBER;BEGINDBMS_SPACE.SPACE_USAGE(owner,object_name,segment_type,ufbl,ufby,fs1bl,fs1by,fs2bl,fs2by,fs3bl,fs3by,fs4bl,fs4by,fubl,fuby,partition_name);PIPEROW(Full blocks /MB ||TO_CHAR(fubl,999999999)|| ||TO_CHAR(fuby/1048576,999999999));PIPEROW(Unformatted blocks/MB ||TO_CHAR(ufbl,999999999)|| ||TO_CHAR(ufby/1048576,999999999));PIPEROW(Free Space 0-25% ||TO_CHAR(fs1bl,999999999)|| ||TO_CHAR(fs1by/1048576,999999999));PIPEROW(Free Space 25-50% ||TO_CHAR(fs2bl,999999999)|| ||TO_CHAR(fs2by/1048576,999999999));PIPEROW(Free Space 50-75% ||TO_CHAR(fs3bl,999999999)|| ||TO_CHAR(fs3by/1048576,999999999));PIPEROW(Free Space 75-100% ||TO_CHAR(fs4bl,999999999)|| ||TO_CHAR(fs4by/1048576,999999999));ENDget_space_usage;col frag_infofora50selectCOLUMN_VALUEasfrag_infofromtable(get_space_usage(A,TE,TABLE));其他参考https://blog.csdn.net/x6_9x/article/details/50596589https://www.cnblogs.com/shunqian/p/17604590.html