[오라클] 실제 테이블 row 수를 count 하는 테이블 생성

 

SET SERVEROUTPUT ON; 

DECLARE 
	v_owner			VARCHAR2(128);
    v_table_name	VARCHAR2(128);
    v_sql			VARCHAR2(500);
    v_row_cnt		NUMBER;
BEGIN
	FOR rec IN ( SELECT OWNER, TABLE_NAME FROM DBA_TABLES WHERE OWNER IN ('IGWOWN','MBMS') AND TABLE_NAME NOT LIKE 'BIN$%' AND NOT REGEXP_LIKE(TABLE_NAME, '.*[0-9]{8}$')) LOOP
    	v_owner := rec.owner;
        v_table_name := rec.table_name;
        BEGIN
        	v_sql := 'SELECT /*+ PARALLEL(4) FULL(A) */ COUNT(*) FROM '|| v_owner||'.'||v_table_name ||' A';
            -- DBMS_OUTPUT.PUT_LINE(v_sql);
            EXECUTE IMMEDIATE v_sql INTO v_row_cnt ;
            INSERT INTO DBADM.TB_TOBE_COUNT_INFO VALUES (v_owner, v_table_name, v_row_cnt, SYSDATE);
            COMMIT; 
        EXCEPTION
        	WHEN OTHERS THEN
            	IF SQLCODE = -942 THEN
                	dbms_output.put_line('테이블 '||v_owner ||'.'||v_table_name || ' 이 존재하지 않습니다. ');
                ELSE
                	dbms_output.put_line('[오류] '||v_owner ||'.'||v_table_name || ';' ||SQLERRM);
                END; 
            END LOOP;
        END;    
        /