Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.server -> Re: q: table storage requirement ?
I wrote this some time ago. It doesn't include all of the datatypes, but you can add them to the logic.
HTH, Steve
DECLARE
v_file_id UTL_FILE.FILE_TYPE; v_value VARCHAR2(300); v_table VARCHAR2(50); v_table_size NUMBER := 0; v_db_size NUMBER := 0; v_record_count NUMBER; v_user VARCHAR2(30);
v_integer NUMBER; v_integer1 NUMBER; v_Dummy INTEGER; v_col_dummy INTEGER; v_column_name VARCHAR2(40); v_data_type VARCHAR2(40); v_index_name VARCHAR2(40); v_constraint_name VARCHAR2(40); v_value VARCHAR2(100); v_nullable VARCHAR2(1); v_column_count NUMBER := 0; v_total NUMBER := 0; v_count_statement VARCHAR2(4000); v_statement VARCHAR2(500) := 'SELECT column_name,data_type FROMuser_tab_columns WHERE table_name = :t ORDER BY column_id';
v_separator NUMBER;
BEGIN
v_integer := DBMS_SQL.OPEN_CURSOR; v_integer1 := DBMS_SQL.OPEN_CURSOR; v_count_statement := 'SELECT COUNT(*) AS total_count FROM '||p_table_name;DBMS_SQL.PARSE(v_integer,v_count_statement,DBMS_SQL.NATIVE); DBMS_SQL.DEFINE_COLUMN(v_integer,1,v_record_count); v_Dummy := DBMS_SQL.EXECUTE(v_integer);
LOOP IF DBMS_SQL.FETCH_ROWS(v_integer) = 0 THEN EXIT; END IF; DBMS_SQL.COLUMN_VALUE(v_integer,1,v_record_count); END LOOP; DBMS_SQL.PARSE(v_integer,v_statement,DBMS_SQL.NATIVE); DBMS_SQL.BIND_VARIABLE(v_integer,':t',p_table_name); DBMS_SQL.DEFINE_COLUMN(v_integer,1,v_column_name,30);DBMS_SQL.DEFINE_COLUMN(v_integer,2,v_data_type,30); v_Dummy := DBMS_SQL.EXECUTE(v_integer); LOOP
IF DBMS_SQL.FETCH_ROWS(v_integer) = 0 THEN EXIT; END IF; DBMS_SQL.COLUMN_VALUE(v_integer,1,v_column_name); DBMS_SQL.COLUMN_VALUE(v_integer,2,v_data_type); IF v_data_type = 'VARCHAR2' THEN DBMS_SQL.PARSE(v_integer1,'SELECT SUM(LENGTH('||v_column_name||')) FROM '||p_table_name,DBMS_SQL.NATIVE); DBMS_SQL.DEFINE_COLUMN(v_integer1,1,v_column_count); v_col_dummy := DBMS_SQL.EXECUTE(v_integer1); LOOP IF DBMS_SQL.FETCH_ROWS(v_integer1) = 0 THEN EXIT; END IF; DBMS_SQL.COLUMN_VALUE(v_integer1,1,v_column_count); END LOOP; v_total := v_total + v_column_count; ELSE v_total := v_total + (8 * v_record_count); END IF;
DBMS_SQL.CLOSE_CURSOR(v_integer); DBMS_SQL.CLOSE_CURSOR(v_integer1); IF v_total IS NULL THEN RETURN 0; ELSE RETURN v_total;
BEGIN
SELECT username
INTO v_user
FROM user_users;
SELECT value
INTO v_value
FROM v$parameter
WHERE name = 'utl_file_dir';
v_file_id :=
UTL_FILE.FOPEN(v_value,v_user||TO_CHAR(sysdate,'MMDDYYHHMISS')||'.txt','w');
UTL_FILE.PUT_LINE(v_file_id,RPAD('TABLE NAME',31)||RPAD('BYTES used in
table',21)||'Row Count');
UTL_FILE.PUT_LINE(v_file_id,'==============================
==================== =========');
FETCH c_tables INTO v_table; EXIT WHEN c_tables%NOTFOUND; v_table_size := describe_table(v_table); UTL_FILE.PUT_LINE(v_file_id,RPAD(v_table,31)||RPAD(v_table_size,21)| |v_record_count); v_db_size := v_db_size + v_table_size;END LOOP;
UTL_FILE.PUT_LINE(v_file_id,'==============================
==================== =========');
![]() |
![]() |