SQL Script

Count Table space used

Updated: 19-Jul-2004
Version: 1.0

Description

Calculates the actual space used.

Parameters

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    
    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

Site Map

SQL Script

Count Table space used

Updated: 19-Jul-2004Version: 1.0

Description

Parameters

SQL Source

Related Links

Site Map

Updated: 19-Jul-2004
Version: 1.0