Re: Need report that shows total space used at a table level

From: Joan Hsieh <jhsieh_at_infonet.tufts.edu>
Date: Mon, 28 Aug 2000 14:04:13 -0400
Message-Id: <10602.115665@fatcity.com>

Cherie,

I rewrote this script from some other simple script and used couple years,it is very helpful. Since I moved to peoplesoft enviroment now, this script become useless, as all tables owned by one schema. So I don't know it is outdated or not. Log on as schema owner, it will report all the tables and index info. for you.

Joan

• op1 total_blocks
  • total number of blocks in the segment
  • op2 total_bytes
  • the same as above, expressed in bytes
  • op3 unused_blocks
  • number of blocks which are not used
  • op4 unused_bytes
  • the same as above, expressed in bytes
  • op5 last_used_extent_file_id
  • the file ID of the last extent which contains data
  • op6 last_used_extent_block_id
  • the block ID of the last extent which contains data
  • op7 last_used_block
  • the last block within this extent which contains data set serveroutput on set verify off set lines 150 set termout on

declare

err_num number;
err_msg char(150);
rtable_name varchar2(30);
rindex_name varchar2(30);
rowner varchar2(20);

tot_idx_op1 number :=0;
tot_idx_op2 number :=0;
tot_idx_op3 number :=0;
tot_idx_op4 number :=0;
tot_idx_op5 number :=0;
tot_idx_op6 number :=0;
tot_tab_op1 number :=0;
tot_tab_op2 number :=0;
tot_tab_op3 number :=0;
tot_tab_op4 number :=0;
tot_tab_op5 number :=0;
tot_tab_op6 number :=0;
tot_tab_op7 number :=0;
total_op1 number :=0;
total_op2 number :=0;
total_op3 number :=0;
total_op4 number :=0;
total_op5 number :=0;

total_op6 number :=0;
total_op7 number :=0;

op1 number;
op2 number;
op3 number;
op4 number;
op5 number;
op6 number;
op7 number;

cursor rtable is

   select table_name from all_tables where owner='&1'; cursor rindex is

   select index_name from all_indexes where owner='&1';

begin
open rtable;
dbms_output.put_line('SCHEMA: '||'&1');

dbms_output.put_line('TABLE                         TOTAL     UNUSED   
USED      TOTAL        UNUSED       USED         %USED');
dbms_output.put_line('NAME                          BLOCKS    BLOCKS   
BLOCKS    BYTES        BYTES        BYTES');
dbms_output.put_line('----                          ------    ------   
------    -----        -----        -----        -----');

loop
fetch rtable into rtable_name;
exit when rtable%notfound;
dbms_output.Enable(90000);
dbms_space.unused_space('&1',rtable_name,'TABLE', op1,op2,op3,op4,op5,op6,op7);
dbms_output.put_line(RPAD(rtable_name,30,' ')||RPAD(to_char(op1),10,'

')||RPAD(to_char(op3),10,' ')||RPAD(to_char(op1-op3),10,'
')||RPAD(to_char(op2),13,' ')||RPAD(to_char(op4),13,'
')||RPAD(to_char(op2-op4),13,' ')||RPAD(to_char(((op2-op4)/op2)),3,'
'));

tot_tab_op1:=tot_tab_op1+op1;
tot_tab_op2:=tot_tab_op2+op2;
tot_tab_op3:=tot_tab_op3+op3;
tot_tab_op4:=tot_tab_op4+op4;

tot_tab_op5:=tot_tab_op5+op5;
tot_tab_op6:=tot_tab_op6+op6;

end loop;

dbms_output.put_line('--------------------------------------------------------------------------------------------------------');

dbms_output.put_line(RPAD('TABLE TOTAL',30,'

')||RPAD(to_char(tot_tab_op1),10,' ')||RPAD(to_char(tot_tab_op3),10,'
')||RPAD(to_char(tot_tab_op1-tot_tab_op3),10,'
')||RPAD(to_char(tot_tab_op2),13,' ')||RPAD(to_char(tot_tab_op4),13,'
')||RPAD(to_char(tot_tab_op2-tot_tab_op4),13,'
')||RPAD(to_char(((tot_tab_op2-tot_tab_op4)/tot_tab_op2)),3,' '));

close rtable;

open rindex;

dbms_output.put_line('--------------------------------------------------------------------------------------------------------');
dbms_output.put_line('INDEX                         TOTAL     UNUSED   
USED      TOTAL        UNUSED       USED         %USED');
dbms_output.put_line('NAME                          BLOCKS    BLOCKS   
BLOCKS    BYTES        BYTES        BYTES');
dbms_output.put_line('----                          ------    ------   
------    -----        -----        -----        -----');

loop
fetch rindex into rindex_name;
exit when rindex%notfound;
dbms_output.Enable(90000);
dbms_space.unused_space('&1',rindex_name,'INDEX', op1,op2,op3,op4,op5,op6,op7);
dbms_output.put_line(RPAD(rindex_name,30,' ')||RPAD(to_char(op1),10,'

')||RPAD(to_char(op3),10,' ')||RPAD(to_char(op1-op3),10,'
')||RPAD(to_char(op2),13,' ')||RPAD(to_char(op4),13,'
')||RPAD(to_char(op2-op4),13,' ')||RPAD(to_char(((op2-op4)/op2)),3,'
'));

tot_idx_op1:=tot_idx_op1+op1;
tot_idx_op2:=tot_idx_op2+op2;
tot_idx_op3:=tot_idx_op3+op3;
tot_idx_op4:=tot_idx_op4+op4;

tot_idx_op5:=tot_idx_op5+op5;
tot_idx_op6:=tot_idx_op6+op6;

end loop;

dbms_output.put_line('--------------------------------------------------------------------------------------------------------');

dbms_output.put_line(RPAD('INDEX TOTAL',30,'

')||RPAD(to_char(tot_idx_op1),10,' ')||RPAD(to_char(tot_idx_op3),10,'
')||RPAD(to_char(tot_idx_op1-tot_idx_op3),10,'
')||RPAD(to_char(tot_idx_op2),13,' ')||RPAD(to_char(tot_idx_op4),13,'
')||RPAD(to_char(tot_idx_op2-tot_idx_op4),13,'
')||RPAD(to_char(((tot_idx_op2-tot_idx_op4)/tot_idx_op2)),3,' '));

total_op1:=tot_idx_op1+tot_tab_op1;

total_op2:=tot_idx_op2+tot_tab_op2;
total_op3:=tot_idx_op3+tot_tab_op3;
total_op4:=tot_idx_op4+tot_tab_op4;
total_op5:=tot_idx_op5+tot_tab_op5;
total_op6:=tot_idx_op6+tot_tab_op6;

dbms_output.put_line('--------------------------------------------------------------------------------------------------------');

dbms_output.put_line(RPAD('TOTAL',30,' ')||RPAD(to_char(total_op1),10,'

')||RPAD(to_char(total_op3),10,'
')||RPAD(to_char(total_op1-total_op3),10,'
')||RPAD(to_char(total_op2),13,' ')||RPAD(to_char(total_op4),13,'
')||RPAD(to_char(total_op2-total_op4),13,'
')||RPAD(to_char(((total_op2-total_op4)/total_op2)),3,' '));
dbms_output.put_line('--------------------------------------------------------------------------------------------------------');

close rindex;
 exception
   when others then

       err_num := sqlcode;
       err_msg := substr(sqlerrm,1,150);
      dbms_output.put_line(err_num);
      dbms_output.put_line(err_msg);

end;

Cherie_Machler_at_gelco.com wrote:
>
> One of my developer asked for a report that shows the
> amount of space used by each table. We have one that
> shows at the tablespace level but not at the table level.
>
> I can write something that looks at extents and sums them
> up for each table. Does anyone else already have a script
> that does this so he can have it right away this morning.
>
> TIA,
>
> Cherie
>
> --
> Author:
> INET: Cherie_Machler_at_gelco.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
Received on Mon Aug 28 2000 - 13:04:13 CDT