SQL Script

Count Table Space Used

Updated : 28-Feb-2001
Version : 1.2

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.

Please Note : This script works for both Oracle 7 and 8 and contains one technique for determining database version.

Parameters

&1 - Table Owner
&2 - Table Name

SQL Source

set verify off
spool size_&&1..lst
set serverout on size 1000000
declare
        w_p_string varchar2(254);
        w_cursor_id integer;
        w_p_exec integer;
        col1 number;
        block_count number;
        row1      number;
        row2      number;
cursor get_tab is
    select table_name
    from dba_tables
    where owner=upper('&&1') and table_name like UPPER('%&2%');
l_db_block_size NUMBER;
l_db_version VARCHAR2(12);
l_db_comp VARCHAR2(12);
l_use_v8 BOOLEAN DEFAULT FALSE;
begin
/* Obtain the database version */
BEGIN
   dbms_utility.db_version (l_db_version, l_db_comp);
   IF  l_db_version > '8.0.0.0.0' THEN
      l_use_v8 := TRUE;
   END IF;
EXCEPTION WHEN OTHERS THEN /* any errors then assume version 7 */
      l_use_v8 := FALSE;
END;
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';
w_cursor_id:=dbms_sql.open_cursor;
for get_tab_rec in get_tab loop
BEGIN
IF l_use_v8 THEN
   w_p_string := 'select count(distinct( dbms_rowid.rowid_block_number(rowid) ))
 col1 from &&1..'
                                                ||get_tab_rec.table_name;
ELSE
   w_p_string := 'select count(distinct( substr(rowid,1,8) )) col1 from &&1..'
                                                ||get_tab_rec.table_name;
END IF;
   dbms_sql.parse(w_cursor_id,w_p_string,1);
   dbms_sql.define_column(w_cursor_id, 1, col1);
   w_p_exec := dbms_sql.execute(w_cursor_id);
   w_p_exec := dbms_sql.fetch_rows(w_cursor_id);
   dbms_sql.column_value (w_cursor_id,1,block_count);

   dbms_output.put_line('Table '||rpad(get_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)'
     );

exception when others then
      dbms_output.put_line('Error counting rows for table '||get_tab_rec.table_n
ame||' '||SQLERRM);

END;

end loop;

dbms_sql.close_cursor(w_cursor_id);

end;
/
set verify on
spool off