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