Shulou(Shulou.com)05/31 Report--

This article is about how oracle queries for table fragments. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

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, 'Nice,' 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_management = 'AUTO'

-- and B. tablekeeper names selected LSEC repositories VALUELES Collections

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. tablekeeper names selected LSEC repositories VALUELES Collections

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) > 100

ORDER BY 7 DESC

USED_MB: indicates that the size of the object has been used

FRAG_MB: indicates the size of the fragment

FRAGMENT_PER: indicates the percentage of fragmentation rate

Thank you for reading! This is the end of the article on "how to query table fragments in oracle". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, you can share it for more people to see!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.