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 <[EMAIL PROTECTED]> 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 <[EMAIL PROTECTED]> 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.
> > [EMAIL PROTECTED]
> >
> >
> > -----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: [EMAIL PROTECTED]
> >
> > 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
> >
> > 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
>
> 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
