PROCEDURE table_size calculates the estimated size of a table with estimated number of rows, (if there is data in the table). You need select privileges to some sys-views (DBA is not enough).
CREATE OR REPLACE PACKAGE size
AS
PROCEDURE table_size( owner_name_p VARCHAR2,-- schema of the table
table_name_p VARCHAR2,-- table name
table_rows_p NUMBER -- estimated number of rows
);
END;
/
CREATE OR REPLACE PACKAGE BODY size
AS
PROCEDURE table_size( owner_name_p VARCHAR2,-- schema of the table
table_name_p VARCHAR2,-- table name
table_rows_p NUMBER -- number of rows in table
)
AS
CURSOR columns_cur IS
SELECT column_name,
data_type,
decode(data_length,'NUMBER',data_precision+data_scale,data_length)
FROM sys.dba_tab_columns
WHERE table_name = table_name_p
AND owner = owner_name_p;
DEBUG BOOLEAN := FALSE; -- set TRUE if you want to know what the procedure is doing
col_tmp_v NUMBER := 0; -- size of the column
col_1_v NUMBER := 0; -- columns with size < 250
col_250_v NUMBER := 0; -- columns with size >= 250
col_type_v sys.dba_tab_columns.data_type%TYPE; -- column type
x INTEGER := 0;
y INTEGER := 0;
z NUMBER := 0;
cursor_v INTEGER; -- cursor variable
block_size_v INTEGER := 0; -- block size
pct_free_v INTEGER := 0.10; -- pct_free of the table
-- all_col_length_v NUMBER; --
ignore_v INTEGER; -- for cursor
err_v VARCHAR2(200); -- error number
num_v INTEGER; -- error number
row_size_v NUMBER(12,2); -- size of a row
table_size_v NUMBER(20,2); -- size of the table
vsizestmt_v VARCHAR2(2000); -- sql-clause
grade_v VARCHAR2(10) := 'K'; -- grade (bytes,Kilobytes,Megabytes)
colname_v sys.dba_tab_columns.column_name%TYPE; -- column_name
BEGIN
dbms_output.enable(1000000);
-- PCTFREE ---
select NVL(pct_free/100,0.10) into pct_free_v
from sys.dba_tables
WHERE table_name = table_name_p
AND owner = owner_name_p;
-- BLOCK_SIZE
select value into block_size_v
from sys.v_$parameter where name='db_block_size';
-- sql-clause begins
-- this clause will calculate the size of of each row in bytes
vsizestmt_v := 'SELECT AVG(';
OPEN columns_cur;
LOOP
FETCH columns_cur INTO colname_v,col_type_v,col_tmp_v;
EXIT WHEN columns_cur%NOTFOUND;
IF col_tmp_v >= 250 THEN
col_250_v := col_250_v + 1;
ELSE
col_1_v := col_1_v + 1;
end if;
col_tmp_v := 0;
IF DEBUG THEN
dbms_output.put(colname_v||' c1 '||col_1_v||' c2 '||col_250_v);
dbms_output.new_line;
END IF;
vsizestmt_v := vsizestmt_v||'NVL(VSIZE('||colname_v||'),0)+1 +';
END LOOP;
CLOSE columns_cur;
-- get rid of the last '+'
vsizestmt_v := substr(vsizestmt_v,1,(length(vsizestmt_v)-1));
-- end of the sql-clause
vsizestmt_v := vsizestmt_v||') AVERAGE_ROW_SIZE FROM '||owner_name_p||'.'||table_name_p;
-- execute the sql-clause
cursor_v := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(cursor_v,vsizestmt_v,1);
DBMS_SQL.DEFINE_COLUMN(cursor_v,1,row_size_v);
ignore_v := DBMS_SQL.EXECUTE(cursor_v);
IF DBMS_SQL.FETCH_ROWS(cursor_v)>0 THEN
DBMS_SQL.COLUMN_VALUE(cursor_v,1,row_size_v);
END IF;
DBMS_SQL.CLOSE_CURSOR(cursor_v);
-- row size
row_size_v := row_size_v + 3 + col_1_v + (3 * col_250_v);
-- block row count (x)
x := block_size_v/row_size_v;
-- round x down to the nearest integer
y := block_size_v -((block_size_v -(52+(4*x))) * pct_free_v);
IF DEBUG THEN
dbms_output.put('x:'||x||' y:'||y||' z:'||row_size_v);
dbms_output.new_line;
END IF;
-- validate y >= x*z
WHILE y < x * row_size_v
LOOP
x := x - 1;
END LOOP;
-- calculate the size of the table
table_size_v := table_rows_p / x * block_size_v;
-- if table is empty
IF table_size_v is null then
grade_v := '0 rows?';
table_size_v := -1;
ELSE
-- choose the grade
-- IF table_size > 1048576 THEN
table_size_v := table_size_v / 1048576;
grade_v := 'M';
-- ELSE IF table_size > 1024 THEN
-- table_size := table_size/1024;
-- grade := 'K';
-- END IF;
END IF;
-- Show the result
dbms_output.put_line(rpad(table_name_p,25)||' '||
lpad(table_rows_p,12)||' '||
lpad(to_char(table_size_v,'99999990D99'),12)||' '||grade_v);
----------------- exceptions ----------------------------------------------
EXCEPTION
WHEN OTHERS THEN
err_v := SQLERRM;
num_v := SQLCODE;
dbms_output.put_line(num_v);
dbms_output.put_line(err_v);
END; -- end of the procedure table_size
END: -- end of the package size
