Join this Blog for direct reference of any post into your inbox. To join just click on "Join this Site" under "Follower" then Login with your Email.*** DECLARATION: I Maintain this Blog for Helping Myself While at Work and Welcome any body Needing Help!!!.*** CAUTION: Using any of the script from this Blog may contain at Own Risk. These scripts May or May not have been Tested.***

Saturday, 12 May 2012

Script: List tables with high water mark not equal to used blocks

set verify off
column owner format a10
column alcblks heading 'Allocated|Blocks' just c
column usdblks heading 'Used|Blocks' just c
column hgwtr heading 'High|Water' just c
break on owner skip page

    a.owner, a.table_name, b.blocks  alcblks, a.blocks  usdblks,
    (b.blocks-a.empty_blocks-1)  hgwtr
from dba_tables a, dba_segments b
where  a.table_name=b.segment_name and a.owner=b.owner
    and a.owner not in('SYS','SYSTEM') and a.blocks <> (b.blocks-a.empty_blocks-1)
    and a.owner like upper('&owner')||'%' and a.table_name like upper('&table_name')||'%'
order by 1,2


Post a Comment