RE: unused blocks BELOW HWM

From: Brian Wisniewski <brian_wisniewski_at_yahoo.com>
Date: Wed, 19 Dec 2001 13:41:12 -0800
Message-ID: <F001.003E0AD3.20011219131609@fatcity.com>

Here's what I use. Shows above and below HWM. I have another one for partitioned tables. Hope this works for you.

declare OP1 number :=0;

OP2 number :=0;
OP3 number :=0;
OP4 number :=0;
OP5 number :=0;
OP6 number :=0;
OP7 number :=0;

free_blocks number :=0;

cursor object_cursor is

        select owner, object_name, object_type
        from dba_objects
        where object_type in ('TABLE', 'INDEX', 'CLUSTER')
        and object_name like upper('&1')
        or owner||'.'||object_name like upper('&1')
        --and owner not in ('SYS', 'SYSTEM')
        group by object_type, owner, object_name;
CUR_OWN varchar2(30);

CUR_NAME varchar2(128);
CUR_TYPE varchar2(15);

begin
open object_cursor;
loop

fetch object_cursor into CUR_OWN, CUR_NAME, CUR_TYPE; exit when object_cursor%NOTFOUND;

        dbms_output.enable(1000000) ;
       
dbms_space.unused_space(CUR_OWN,CUR_NAME,CUR_TYPE,OP1,OP2,OP3,OP4,OP5,OP6,OP7);
       
dbms_space.free_blocks(CUR_OWN,CUR_NAME,CUR_TYPE,0,free_blocks);
       
dbms_output.put_line('++++++++++++++++++++++++++++++++++++++++');
        dbms_output.put_line('OBJECT            =
'||CUR_OWN||'.'||CUR_NAME);
        dbms_output.put_line('TYPE              = '||CUR_TYPE);
       
dbms_output.put_line('++++++++++++++++++++++++++++++++++++++++');
        dbms_output.put_line('TOTAL_BLOCKS                      =
'||OP1);
        dbms_output.put_line('TOTAL_BYTES                       =
'||OP2);
        dbms_output.put_line('UNUSED_BLOCKS (above HWM) = '||OP3);
        dbms_output.put_line('UNUSED_BYTES (above HWM)  = '||OP4);
        dbms_output.put_line('FREELIST BLOCKS (below HWM)       =
'||free_blocks);
--      dbms_output.put_line('LAST_USED_EXTENT_FILE_ID  = '||OP5);
--      dbms_output.put_line('LAST_USED_EXTENT_BLOCK_ID = '||OP6);
--      dbms_output.put_line('LAST_USED_BLOCK   = '||OP7);

end loop;
close object_cursor;
end;

Gene Gurevich <g_u_r_e_v_i_c_h_at_yahoo.com> wrote:
> Sure,
>
> I haven't got around to using the dbms procedure yet,
> but these are two queries I ended up with. I have
> checked them on several tables and the results are the
>
> same:
>
> here, of course, 1 is the table name and 2 is the
> owner. the tables need to be analyzed before running
> these queries
>
> select TOTAL_B_HWM - USED_B_HWM
> from
> (select blocks TOTAL_B_HWM from dba_tables
> where table_name = UPPER('&&1') and owner=
> UPPER('&&2')),
> (select
> count(unique(substr(dbms_rowid.rowid_to_restricted(rowid,0),15,4)
> ||
>
> substr(dbms_rowid.rowid_to_restricted(rowid,0),1,8)))
> USED_B_HWM
> from &&2..&&1);
>
> OR
>
> select TOTAL - TOTAL_A_HWM - 1 - USED_B_HWM
> from
> (select blocks TOTAL FRom dba_segments
> where segment_name = UPPER('&&1') and owner=
> UPPER('&&2')),
> (select empty_blocks TOTAL_A_HWM from dba_tables
> where table_name = UPPER('&&1')),
> (select
> count(unique(substr(dbms_rowid.rowid_to_restricted(rowid,0),15,4)
> ||
>
> substr(dbms_rowid.rowid_to_restricted(rowid,0),1,8)))
> USED_B_HWM
> from &&2..&&1);
> undef 1 2
>
> --- DENNIS WILLIAMS <DWILLIAMS_at_LIFETOUCH.COM> wrote:
> > Gene - Now that you've gotten your answer, would you
> > mind to post both of
> > the methods that you found to work so that the rest
> > of us could learn?
> > Thanks.
> > Dennis Williams
> > DBA
> > Lifetouch, Inc.
> > dwilliams_at_lifetouch.com
> >
> >
> > -----Original Message-----
> > Sent: Tuesday, December 18, 2001 7:05 AM
> > To: Multiple recipients of list ORACLE-L
> >
> >
> > Hi.
> >
> > This is just to thank all who replied to my post.
> > Based on what I read, I have got two different ways
> > of
> > calculating that number and they seem to produce the
> > same result.
> >
> > thank you all (you know who you are)!!!!
> >
> >
> > =====
> >
> >
> > __________________________________________________
> > Do You Yahoo!?
> > Check out Yahoo! Shopping and Yahoo! Auctions for
> > all of
> > your unique holiday gifts! Buy at
> > http://shopping.yahoo.com
> > or bid at http://auctions.yahoo.com
> > --
> > Please see the official ORACLE-L FAQ:
> > http://www.orafaq.com
> > --
> > Author: Gene Gurevich
> > INET: g_u_r_e_v_i_c_h_at_yahoo.com
> >
> > Fat City Network Services -- (858) 538-5051 FAX:
> > (858) 538-5051
> > San Diego, California -- Public Internet
> > access / Mailing Lists
> >
> --------------------------------------------------------------------
> > To REMOVE yourself from this mailing list, send an
> > E-Mail message
> > to: ListGuru_at_fatcity.com (note EXACT spelling of
> > 'ListGuru') and in
> > the message BODY, include a line containing: UNSUB
> > ORACLE-L
> > (or the name of mailing list you want to be removed
> > from). You may
> > also send the HELP command for other information
> > (like subscribing).
> > --
> > Please see the official ORACLE-L FAQ:
> > http://www.orafaq.com
> > --
> > Author: DENNIS WILLIAMS
> > INET: DWILLIAMS_at_LIFETOUCH.COM
> >
> > Fat City Network Services -- (858) 538-5051 FAX:
> > (858) 538-5051
> > San Diego, California -- Public Internet
> > access / Mailing Lists
> >
> --------------------------------------------------------------------
> > To REMOVE yourself from this mailing list, send an
> > E-Mail message
> > to: ListGuru_at_fatcity.com (note EXACT spelling of
> > 'ListGuru') and in
> > the message BODY, include a line containing: UNSUB
> > ORACLE-L
> > (or the name of mailing list you want to be removed
> > from). You may
> > also send the HELP command for other information
> > (like subscribing).
>
>
> =====
>
>
> __________________________________________________
> Do You Yahoo!?
> Check out Yahoo! Shopping and Yahoo! Auctions for all of
> your unique holiday gifts! Buy at http://shopping.yahoo.com
> or bid at http://auctions.yahoo.com
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Gene Gurevich
> INET: g_u_r_e_v_i_c_h_at_yahoo.com
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing
> Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).

Do You Yahoo!?
Check out Yahoo! Shopping and Yahoo! Auctions for all of your unique holiday gifts! Buy at http://shopping.yahoo.com or bid at http://auctions.yahoo.com

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Brian Wisniewski
  INET: brian_wisniewski_at_yahoo.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Received on Wed Dec 19 2001 - 15:41:12 CST

RE: unused blocks BELOW HWM - Thanks