SQL Script Count Table Space Used

Updated : 28-Feb-2002
Version : 2.0

Description

This script calculates the actual space used by a table (and not just the space used by all the extents). This script is useful for sizing estimates where the initial sizes have been overestimated.

Parameters

&1 - Table Owner
&2 - Table Name

SQL Source

set verify off
spool size_&&1..lst
set serverout on size 1000000

declare
   sql_stmt varchar2(1024);
   block_count number;

cursor tab is
    select owner, table_name,blocks    
    from dba_tables
    where owner=upper('&&1') and table_name like UPPER('%&2%');

l_db_block_size NUMBER;

begin

dbms_output.put_line('Displaying Actual Space Used for schema &&1 ');
dbms_output.put_line('Log file to Check_counts2.lst ....');
dbms_output.put_line('....');

SELECT value INTO l_db_block_size  
FROM v$parameter WHERE name='db_block_size';

FOR tab_rec IN tab LOOP

BEGIN

sql_stmt:='select count(distinct( dbms_rowid.rowid_block_number(rowid) )) from '
   ||tab_rec.owner||'.' ||tab_rec.table_name;

EXECUTE IMMEDIATE sql_stmt INTO block_count;

   dbms_output.put_line(rpad(tab_rec.table_name,30)
       ||' '||TO_CHAR(block_count*l_db_block_size)/1024/1024||' Mb'
       ||' ('||TO_CHAR(block_count*l_db_block_size)/1024||' Kb)'     
       ||' Analyze:'
           ||TO_CHAR(tab_rec.blocks*l_db_block_size)/1024/1024||' Mb'
       ||' ('||TO_CHAR(tab_rec.blocks*l_db_block_size)/1024||' Kb)'     
       );

exception when others then
   dbms_output.put_line('Error counting rows for table '||tab_rec.table_name||' '||SQLERRM);

END;

end loop;

end;
/

spool off

Previous Oracle Version Links

Count Table Space Used

Return to Index of SQL Scripts

Home | Company Profile | Services | Contact Us | SQL scripts and tips | Quiz Legal