oracle如何查询表碎片

这篇文章给大家分享的是有关oracle如何查询表碎片的内容。小编觉得挺实用的，因此分享给大家做个参考，一起跟随小编过来看看吧。SELECT OWNER, TABLE_NAME, SEGMENT_TYPE, segment_space_management MANAGEMENT, TABLE_MB USED_MB, ROUND(WASTE_PER * TABLE_MB / 100, 2) FRAG_MB, WASTE_PER fragment_per, LAST_ANALYZED FROM (SELECT OWNER, SEGMENT_NAME TABLE_NAME, LAST_ANALYZED, SEGMENT_TYPE, GREATEST(ROUND(100 * (NVL(HWM – AVG_USED_BLOCKS, 0) / GREATEST(NVL(HWM, 1), 1)), 2), 0) WASTE_PER, ROUND(BYTES / POWER(1024, 2), 2) TABLE_MB, NUM_ROWS, BLOCKS, EMPTY_BLOCKS, HWM HIGHWATER_MARK, AVG_USED_BLOCKS, CHAIN_PER, EXTENTS, MAX_EXTENTS, ALLO_EXTENT_PER, DECODE(GREATEST(MAX_FREE_SPACE – NEXT_EXTENT, 0), 0, ‘N’, ‘Y’) CAN_EXTEND_SPACE, NEXT_EXTENT, MAX_FREE_SPACE, O_TABLESPACE_NAME TABLESPACE_NAME, block_size, segment_space_management FROM (SELECT A.OWNER OWNER, A.SEGMENT_NAME, A.SEGMENT_TYPE, A.BYTES, B.NUM_ROWS, A.BLOCKS BLOCKS, B.EMPTY_BLOCKS EMPTY_BLOCKS, A.BLOCKS – B.EMPTY_BLOCKS – 1 HWM, DECODE(ROUND((B.AVG_ROW_LEN * NUM_ROWS * (1 + (PCT_FREE / 100))) / dt.block_size, 0), 0, 1, ROUND((B.AVG_ROW_LEN * NUM_ROWS * (1 + (PCT_FREE / 100))) / dt.block_size, 0)) + 2 AVG_USED_BLOCKS, ROUND(100 * (NVL(B.CHAIN_CNT, 0) / GREATEST(NVL(B.NUM_ROWS, 1), 1)), 2) CHAIN_PER, ROUND(100 * (A.EXTENTS / A.MAX_EXTENTS), 2) ALLO_EXTENT_PER, A.EXTENTS EXTENTS, A.MAX_EXTENTS MAX_EXTENTS, B.NEXT_EXTENT NEXT_EXTENT, B.TABLESPACE_NAME O_TABLESPACE_NAME, B.LAST_ANALYZED, dt.block_size, DT.segment_space_management FROM SYS.DBA_SEGMENTS A, SYS.DBA_TABLES B, dba_tablespaces dt WHERE A.OWNER = B.OWNER and SEGMENT_NAME = TABLE_NAME and SEGMENT_TYPE = ‘TABLE’ — and dt.segment_space_man免费主机域名agement = ‘AUTO’ — and B.table_name=’LS_REPORT_VALUE_COLLECT’ and dt.tablespace_name = a.tablespace_name –and b.last_analyzed > to_date(‘20070601’, ‘yyyymmdd免费主机域名‘) union all SELECT A.OWNER OWNER, SEGMENT_NAME || ‘.’ || B.PARTITION_NAME, SEGMENT_TYPE, BYTES, B.NUM_ROWS, A.BLOCKS BLOCKS, B.EMPTY_BLOCKS EMPTY_BLOCKS, A.BLOCKS – B.EMPTY_BLOCKS – 1 HWM, DECODE(ROUND((B.AVG_ROW_LEN * B.NUM_ROWS * (1 + (B.PCT_FREE / 100))) / dt.block_size, 0), 0, 1, ROUND((B.AVG_ROW_LEN * B.NUM_ROWS * (1 + (B.PCT_FREE / 100))) / dt.block_size, 0)) + 2 AVG_USED_BLOCKS, ROUND(100 * (NVL(B.CHAIN_CNT, 0) / GREATEST(NVL(B.NUM_ROWS, 1), 1)), 2) CHAIN_PER, ROUND(100 * (A.EXTENTS / A.MAX_EXTENTS), 2) ALLO_EXTENT_PER, A.EXTENTS EXTENTS, A.MAX_EXTENTS MAX_EXTENTS, B.NEXT_EXTENT, B.TABLESPACE_NAME O_TABLESPACE_NAME, d.last_analyzed, dt.block_size, DT.segment_space_management FROM SYS.DBA_SEGMENTS A, SYS.DBA_TAB_PARTITIONS B, SYS.DBA_TABLES D, dba_tablespaces dt WHERE A.OWNER = B.TABLE_OWNER and SEGMENT_NAME = B.TABLE_NAME and SEGMENT_TYPE = ‘TABLE PARTITION’ — and dt.segment_space_management = ‘AUTO’ — and B.table_name=’LS_REPORT_VALUE_COLLECT’ and dt.tablespace_name = a.tablespace_name AND D.OWNER = B.TABLE_OWNER AND D.TABLE_NAME = B.TABLE_NAME AND A.PARTITION_NAME = B.PARTITION_NAME, –AND D.last_analyzed > to_date(‘20070601’, ‘yyyymmdd’)), (SELECT TABLESPACE_NAME F_TABLESPACE_NAME, MAX(BYTES) MAX_FREE_SPACE FROM SYS.DBA_FREE_SPACE GROUP BY TABLESPACE_NAME) WHERE F_TABLESPACE_NAME = O_TABLESPACE_NAME AND GREATEST(ROUND(100 * (NVL(HWM – AVG_USED_BLOCKS, 0) /GREATEST(NVL(HWM, 1), 1)),2), 0) > 25 AND OWNER not in (‘SYS’, ‘SYSMAN’) AND BLOCKS > POWER(1024, 2) / block_size)where ROUND(WASTE_PER * TABLE_MB / 100, 2) > 100ORDER BY 7 DESC;USED_MB：表示对象已使用大小FRAG_MB：表示碎片所占大小FRAGMENT_PER：表示碎片率百分比感谢各位的阅读！关于“oracle如何查询表碎片”这篇文章就分享到这里了，希望以上内容可以对大家有一定的帮助，让大家可以学到更多知识，如果觉得文章不错，可以把它分享出去让更多的人看到吧！

相关推荐: MySQL全面知识点有哪些

这篇文章主要讲解了“MySQL全面知识点有哪些”，文中的讲解内容简单清晰，易于学习与理解，下面请大家跟着小编的思路慢慢深入，一起来研究和学习“MySQL全面知识点有哪些”吧！Windows服务连接与断开服务器数据库操作表的操作数据操作字符集编码数据类型（列类型…