Print
카테고리: [ Oracle Database ]
조회수: 10031

이 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;
/