rem To see the actual space allocated and used for tables and rem corresponding indexes rem rem Biju Thomas rem Aug/98 rem set serveroutput on feedback off echo off verify off lines 80 trims on spool /tmp/tabspace.lst declare cursor ctab is select owner, table_name from all_tables where owner like upper('&1') and table_name like upper('&2'); cursor cind (wowner in varchar2, wtab in varchar2) is select owner, index_name from all_indexes where table_name = wtab and table_owner = wowner; wtotal_blocks number; wtotal_bytes number; wunused_blocks number; wunused_bytes number; wlast_used_extent_file_id number; wlast_used_extent_block_id number; wlast_used_block number; wtaballoc number; wtabused number; wtabfree number; windalloc number; windused number; windfree number; wtottaballoc number := 0; wtottabused number := 0; wtottabfree number := 0; wtotindalloc number := 0; wtotindused number := 0; wtotindfree number := 0; begin dbms_output.enable (9999999); dbms_output.put_line ('Table Owner.Name MBytes Used MB Free MB Indexes Used MB Free MB' ); dbms_output.put_line ('-------------------------- ------- ------- ------- ------- ------- -------'); for rtab in ctab loop dbms_space.unused_space (rtab.owner, rtab.table_name, 'TABLE', wtotal_blocks, wtotal_bytes, wunused_blocks, wunused_bytes, wlast_used_extent_file_id, wlast_used_extent_block_id, wlast_used_block); wtaballoc := wtotal_bytes/1048576; wtabused := (wtotal_bytes - wunused_bytes)/ 1048576; wtabfree := wunused_bytes/1048576; windalloc := 0; windused := 0; windfree := 0; wtottaballoc := wtottaballoc + wtaballoc; wtottabused := wtottabused + wtabused; wtottabfree := wtottabfree + wtabfree; for rind in cind (rtab.owner, rtab.table_name) loop dbms_space.unused_space (rind.owner, rind.index_name, 'INDEX', wtotal_blocks, wtotal_bytes, wunused_blocks, wunused_bytes, wlast_used_extent_file_id, wlast_used_extent_block_id, wlast_used_block); windalloc := windalloc + wtotal_bytes/1048576; windused := windused + (wtotal_bytes - wunused_bytes)/ 1048576; windfree := windfree + wunused_bytes/1048576; end loop; wtotindalloc := wtotindalloc + windalloc; wtotindused := wtotindused + windused; wtotindfree := wtotindfree + windfree; dbms_output.put_line (rpad((rtab.owner || '.' || rtab.table_name),26) || lpad(to_char(wtaballoc, '9999.999'), 9) || lpad(to_char(wtabused, '9999.999'), 9) || lpad(to_char(wtabfree, '9999.999'), 9) || lpad(to_char(windalloc, '9999.999'), 9) || lpad(to_char(windused, '9999.999'), 9) || lpad(to_char(windfree, '9999.999'), 9) ); end loop; dbms_output.put_line ('-------------------------- ------- ------- ------- ------- ------- -------'); dbms_output.put_line (rpad('TOTAL',26) || lpad(to_char(wtottaballoc, '99999.999'), 9) || lpad(to_char(wtottabused, '99999.999'), 9) || lpad(to_char(wtottabfree, '99999.999'), 9) || lpad(to_char(wtotindalloc, '99999.999'), 9) || lpad(to_char(wtotindused, '99999.999'), 9) || lpad(to_char(wtotindfree, '99999.999'), 9) ); end; / set feedback on verify on spool off