I wrapped dbms_space in a pl/sql loop and summed it up that way. I've
included the code in case you want to use it.
- cut ----
- Description: Shows space used per partitioned table or index
- Parameters: name of partitioned table or index
- Author: Brian Wisniewski
- Oracle DBA
- brian_wisniewski_at_yahoo.com
- The Protec Group Inc. http://www.protec.com
- Updated: 10/00
@set
declare OP1 number :=0;
OP2 number :=0;
OP3 number :=0;
OP4 number :=0;
OP5 number :=0;
OP6 number :=0;
OP7 number :=0;
cursor object_cursor is
select o.owner, object_name, object_type||' PARTITION',
partition_name
from dba_objects o, dba_segments s
where object_type in ('TABLE', 'INDEX', 'CLUSTER')
and segment_name = object_name
and partition_name is not NULL
and ( object_name like upper('&1') or
o.owner||'.'||object_name like upper('&1') );
CUR_OWN varchar2(30);
CUR_NAME varchar2(128);
CUR_TYPE varchar2(15);
PAR_NAME varchar2(30);
TOTAL_BYTES number := 0;
TOTAL_BLOCKS number :=0;
UNUSED_BLOCKS number :=0;
UNUSED_BYTES number :=0;
begin
open object_cursor;
dbms_output.enable(1000000) ;
loop
fetch object_cursor into CUR_OWN, CUR_NAME, CUR_TYPE, PAR_NAME;
exit when object_cursor%NOTFOUND;
dbms_space.unused_space(CUR_OWN,CUR_NAME,CUR_TYPE,OP1,OP2,OP3,OP4,OP5,OP6,OP7,PAR_NAME);
TOTAL_BLOCKS := TOTAL_BLOCKS + OP1;
TOTAL_BYTES := TOTAL_BYTES + OP2;
UNUSED_BLOCKS := UNUSED_BLOCKS + OP3;
UNUSED_BYTES := UNUSED_BYTES + OP4;
end loop;
dbms_output.put_line('++++++++++++++++++++++++++++++++++++++++');
dbms_output.put_line('OBJECT = '||CUR_OWN||'.'||CUR_NAME);
dbms_output.put_line('TYPE = '||CUR_TYPE);
dbms_output.put_line('PARTITION = '||PAR_NAME);
dbms_output.put_line('++++++++++++++++++++++++++++++++++++++++');
dbms_output.put_line('TOTAL_BLOCKS = '||TOTAL_BLOCKS);
dbms_output.put_line('TOTAL_BYTES = '||TOTAL_BYTES||'
('||round(TOTAL_BYTES/(1024*1024),0)||'M)');
dbms_output.put_line('UNUSED_BLOCKS = '||UNUSED_BLOCKS);
dbms_output.put_line('UNUSED_BYTES = '||UNUSED_BYTES||'
('||round(UNUSED_BYTES/(1024*1024),0)||'M)');
close object_cursor;
end;
/
---- cut ----
- Arun Jacob Muthalaly <arun_jacob_at_0racledba.com> wrote:
> Hi,
>
> Is there any way to calculate the Total Size of a partitioned table
> rather than calculating it individually for each partition.
>
> Cheers
>
> Arun J
> --- "Suhen Pather" <PatherS5_at_telkom.co.za>
> > wrote:
> >Hi Arun
> >
> >For partitioned tables you need to put in the partition name after
> the
> >last variable (OUT)(7th).
> >
> >desc dbms_space
> >This will give you the input parameters.
> >
> >HTH
> >Suhen
> >
> >
> >
> >>>> arun_jacob_at_0racledba.com 10/24/00 12:40PM >>>
> >Hi,
> >
> >Could you please tell me how to modify the following script for
> Oracle
> >8i (Partitioned Tables/Indexes).
> >
> >Cheers
> >
> >Arun J
> >
> >
> > declare
> > VAR1 number;
> > VAR2 number;
> > VAR3 number;
> > VAR4 number;
> > VAR5 number;
> > VAR6 number;
> > VAR7 number;
> > begin
> > dbms_space.unused_space('OPS$CC1','SPACES','TABLE',
> >
> >VAR1,VAR2,VAR3,VAR4,VAR5,VAR6,VAR7,PARTITION_NAME);
> > dbms_output.put_line('OBJECT_NAME = SPACES');
> > dbms_output.put_line('---------------------------');
> > dbms_output.put_line('TOTAL_BLOCKS = '||VAR1);
> > dbms_output.put_line('TOTAL_BYTES = '||VAR2);
> > dbms_output.put_line('UNUSED_BLOCKS = '||VAR3);
> > dbms_output.put_line('UNUSED_BYTES = '||VAR4);
> > dbms_output.put_line('LAST_USED_EXTENT_FILE_ID = '||VAR5);
> > dbms_output.put_line('LAST_USED_EXTENT_BLOCK_ID = '||VAR6);
> > dbms_output.put_line('LAST_USED_BLOCK = '||VAR7);
> > end;
> >
> >_____________________________________________________________
> >Be someone_at_0racledba.com - get your free mail from Oriole
> Corporation
> > http://www.oriolecorp.com - Performance tools for Oracle DBAs
> >
> >Get email for your site ---> http://www.everyone.net
> >
> >--
> >Please see the official ORACLE-L FAQ: http://www.orafaq.com
> >--
> >Author: Arun Jacob Muthalaly
> > INET: arun_jacob_at_0racledba.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: Suhen Pather
> > INET: PatherS5_at_telkom.co.za
> >
> >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).
>
> _____________________________________________________________
> Be someone_at_0racledba.com - get your free mail from Oriole Corporation
>
> http://www.oriolecorp.com - Performance tools for Oracle DBAs
>
> Get email for your site ---> http://www.everyone.net
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Arun Jacob Muthalaly
> INET: arun_jacob_at_0racledba.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 Tue Oct 24 2000 - 13:01:13 CDT
