col owner for a10

col table_name for a30

SELECT OWNER,

SEGMENT_NAME TABLE_NAME,

SEGMENT_TYPE,

GREATEST (ROUND ( 100 * ( NVL (HWM - AVG_USED_BLOCKS, 0 ) /

GREATEST (NVL (HWM, 1), 1 )),

2 ),

0 ) WASTE_PER

FROM ( SELECT A.OWNER OWNER,

A.SEGMENT_NAME,

A.SEGMENT_TYPE,

B.LAST_ANALYZED,

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 ))) / C.BLOCKSIZE,

0 ),

0 ,

1 ,

ROUND ((B.AVG_ROW_LEN * NUM_ROWS *

( 1 + (PCT_FREE / 100 ))) / C.BLOCKSIZE,

0 )) + 2 AVG_USED_BLOCKS,

ROUND (100 *

( NVL (B.CHAIN_CNT, 0 ) / GREATEST( NVL (B.NUM_ROWS, 1 ), 1 )),

2 ) CHAIN_PER,

B.TABLESPACE_NAME O_TABLESPACE_NAME

FROM SYS.DBA_SEGMENTS A, SYS.DBA_TABLES B, SYS.TS$ C

WHERE A.OWNER = B.OWNER

and SEGMENT_NAME = TABLE_NAME

and SEGMENT_TYPE = 'TABLE'

AND B.TABLESPACE_NAME = C.NAME)

WHERE GREATEST (ROUND ( 100 * ( NVL (HWM - AVG_USED_BLOCKS, 0 ) /

GREATEST (NVL (HWM, 1), 1 )),

2 ),

0 ) > 50

AND OWNER like 'DEVELOPER'

AND BLOCKS > 100

order by WASTE_PER desc ;