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. Displays whether a table is compressed.

Parameters

1 - Table Owner 
2 - Table Name

SQL Source

REM Copyright (C) Think Forward.com 1998- 2005. All rights reserved. 

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, DECODE(compression,'ENABLED','(COMPRESSED)', NULL) compressed    
    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) '     
       || tab_rec.compressed );

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

END;

end loop;

end;
/

spool off

Site Map

SQL Script

Count Table Space Used

Updated: 28-Feb-2002Version: 2.0

Description

Parameters

SQL Source

Related Links

Site Map

Updated: 28-Feb-2002
Version: 2.0