Hi All Jared notified me that the attachment did not come through, so I am attaching again. I am also enclosing it in the body of the email.
Credits go to Jared who wrote the shell of this some time back (98 was it Jared ?). I just modified an excellent foundation. <<tblindspace.sql>> set linesize 80 set verify off set echo off set feed off undef tab_name; undef object_type; undef schema_name; prompt Enter the schema and table name, and you are shown the space that the table prompt and each associated index uses in the database prompt accept schema_name prompt 'Enter Schema: ' accept tab_name prompt 'Enter TABLE: ' prompt set serverout on size 1000000 declare ind_name varchar2(30); total_blocks number; unused_blocks number; total_bytes number; unused_bytes number; last_used_extent_file_id number; last_used_extent_block_id number; last_used_block number; cursor find_ind is select index_name from all_indexes where owner=upper('&&schema_name') and table_name=upper('&&tab_name') ; begin dbms_space.unused_space(upper('&&schema_name'),upper('&&tab_name'), 'TABLE',total_blocks, total_bytes,unused_blocks, unused_bytes, last_used_extent_file_id,last_used_extent_block_id,last_used_block); dbms_output.put_line('Total space used by TABLE '|| upper('&&schema_name')||'.' ||upper('&&tab_name')); dbms_output.put_line('TOTAL BLOCKS USED_BLOCKS FREE BLOCKS'); dbms_output.put_line('============ =========== ==========='); dbms_output.put_line(to_char(total_blocks) ||' '|| to_char(total_blocks - unused_blocks)||' '|| to_char(unused_blocks)); dbms_output.put_line(' '); open find_ind ; loop fetch find_ind into ind_name ; exit when find_ind%NOTFOUND or find_ind%NOTFOUND is null ; dbms_space.unused_space(upper('&&schema_name'),upper(ind_name),'INDEX', total_blocks, total_bytes, unused_blocks, unused_bytes, last_used_extent_file_id , last_used_extent_block_id, last_used_block ); dbms_output.put_line('Total space used by INDEX '|| upper('&&schema_name')||'.' ||upper(ind_name)||' '|| to_char(total_blocks) ||' '||to_char(total_blocks - unused_blocks)|| ' '|| to_char(unused_blocks)); end loop ; if find_ind%ISOPEN then close find_ind; end if; end; / set feed on Regards: Ferenc Mantfeld Senior Performance Engineer Siebel Performance Engineering Melbourne, 3000, VIC, Australia Only Robinson Crusoe had all his work done by Friday
tblindspace.sql
Description: Binary data