오라클 DB의 테이블(table) 별 row 수 출력하기

이 PL/SQL 프로시저는 오라클 e-business suites 에서 사용하던 프로그램입니다.

TABLE_ROW_DATA 부분에서 owner 는 수정해서 사용하시면 됩니다.

create or replace procedure GET_TABLE_ROWS(FDIR varchar2, FNAME varchar2) is
  V_OUTPUT UTL_FILE.FILE_TYPE;
  L_CURSOR integer;
  L_SOURCE varchar2(1000);
  L_ROWS   integer;
  L_COUNT  number(10);

  cursor TABLE_ROW_DATA is
    select *
      from (select A.OWNER, A.SEGMENT_NAME, sum(A.BYTES / 1024) BYTES
              from DBA_SEGMENTS A, DBA_TABLES B
             where A.OWNER in (select ORACLE_USERNAME from FND_ORACLE_USERID)
               and A.OWNER not in ('SYS', 'SYSTEM', 'APPS', 'APPLSYS', 'PORTAL30', 'PORTAL30_SSO')
               and A.SEGMENT_TYPE like 'TABLE%'
               and A.OWNER = B.OWNER
               and A.SEGMENT_NAME = B.TABLE_NAME
               and B.IOT_TYPE is null
             group by A.OWNER, A.SEGMENT_NAME)
     order by OWNER, SEGMENT_NAME;

begin
  V_OUTPUT := UTL_FILE.FOPEN(FDIR, FNAME, 'W');
  for R_TAB in TABLE_ROW_DATA loop
    L_CURSOR := DBMS_SQL.OPEN_CURSOR;
    L_SOURCE := 'SELECT COUNT(*) FROM ' || R_TAB.OWNER || '.' || R_TAB.SEGMENT_NAME;
    DBMS_SQL.PARSE(L_CURSOR, L_SOURCE, DBMS_SQL.NATIVE);

    DBMS_SQL.DEFINE_COLUMN(L_CURSOR, 1, L_COUNT);
    L_ROWS := DBMS_SQL.EXECUTE_AND_FETCH(L_CURSOR, false);
    DBMS_SQL.COLUMN_VALUE(L_CURSOR, 1, L_COUNT);
    DBMS_SQL.CLOSE_CURSOR(L_CURSOR);

    UTL_FILE.PUT_LINE(V_OUTPUT,
                      RPAD(R_TAB.OWNER, 15, ' ') || RPAD(R_TAB.SEGMENT_NAME, 40, ' ') ||
                      LPAD(TO_CHAR(L_COUNT, '99,999,999,999'), 20, ' ') ||
                      LPAD(TO_CHAR(R_TAB.BYTES, '99,999,999,999'), 20, ' '));
  end loop;
  UTL_FILE.FCLOSE(V_OUTPUT);

exception
  when UTL_FILE.INVALID_PATH then
    DBMS_OUTPUT.PUT_LINE('INVALID PATH');
  when UTL_FILE.INVALID_MODE then
    DBMS_OUTPUT.PUT_LINE('INVALID MODE');
  when UTL_FILE.INVALID_OPERATION then
    DBMS_OUTPUT.PUT_LINE('INVALID OPERATION');
end;
/