Re: q: table storage requirement ?

From: SteveDHoward <stevedhoward_at_aol.com>
Date: 24 Sep 2000 17:14:31 GMT
Message-ID: <20000924131431.16209.00000966@ng-cg1.aol.com>

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

  CURSOR c_tables IS
    SELECT table_name
      FROM user_tables;
  FUNCTION describe_table(p_table_name IN VARCHAR2)     RETURN NUMBER
    IS

    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 FROM

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

    END LOOP;

    DBMS_SQL.CLOSE_CURSOR(v_integer);
    DBMS_SQL.CLOSE_CURSOR(v_integer1);
    IF v_total IS NULL THEN
      RETURN 0;
    ELSE
      RETURN v_total;

    END IF;
  END describe_table;

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,'==============================


==================== =========');

  OPEN c_tables;
    LOOP

      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,'==============================


==================== =========');

  UTL_FILE.PUT_LINE(v_file_id,RPAD('TOTAL SCHEMA SIZE',31)||v_db_size);   UTL_FILE.FCLOSE_ALL;
  CLOSE c_tables;
END;
/ Received on Sun Sep 24 2000 - 12:14:31 CDT