Re: Table Size Script

From: Joan Hsieh <jhsieh_at_infonet.tufts.edu>
Date: Mon, 28 Aug 2000 15:26:56 -0400
Message-Id: <10602.115678@fatcity.com>

Ron,

As I said, this script wrote 3 years ago. I did't use it at least 1 and 1/2 years. I might sent the list a wrong one. (I have two version, I even can't remember why I modified again.) I sent it again. This one should work, as I tested. run @dbspace.sql SCHEMA OWNER <use capital letter or you can modify it to accept the lower case>

-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       USE
D         %USED');
dbms_output.put_line('NAME                          BLOCKS    BLOCKS   
BLOCKS    BYTES        BYTES        BYT
ES');
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_ch
ar(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),1
0,' ')||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_op
2)),3,' '));
close rtable;

open rindex;

dbms_output.put_line('-----------------------------------------------------------------------------------------
---------------');
dbms_output.put_line('INDEX                         TOTAL     UNUSED   
USED      TOTAL        UNUSED       USE
D         %USED');
dbms_output.put_line('NAME                          BLOCKS    BLOCKS   
BLOCKS    BYTES        BYTES        BYT
ES');
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_ch
ar(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),1
0,' ')||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_op
2)),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,' ')||RP

AD(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;

This is result: ORACLE just has one table in this case, one index.

SQLPLUS:(stats)>@dbspace ORACLE
107 /
SCHEMA: ORACLE

TABLE                         TOTAL     UNUSED    USED      TOTAL       
UNUSED       USED         %USED
NAME                          BLOCKS    BLOCKS    BLOCKS    BYTES       
BYTES        BYTES
----                          ------    ------    ------    -----       
-----        -----        -----
TS_GROWTH                     10        8         2         81920       
65536        16384        .2
--------------------------------------------------------------------------------------------------------
TABLE TOTAL                   10        8         2         81920       
65536        16384        .2
--------------------------------------------------------------------------------------------------------
INDEX                         TOTAL     UNUSED    USED      TOTAL       
UNUSED       USED         %USED
NAME                          BLOCKS    BLOCKS    BLOCKS    BYTES       
BYTES        BYTES
----                          ------    ------    ------    -----       
-----        -----        -----
--------------------------------------------------------------------------------------------------------

-1476
ORA-01476: divisor is equal to zero

PL/SQL procedure successfully completed.

SQLPLUS:(stats)>exit
"Smith, Ron L." wrote: Received on Mon Aug 28 2000 - 14:26:56 CDT