[Oracle] 테이블 건수 조회

DECLARE

 

v_table_name varchar2(200);

v_cnt number;

v_ymd varchar2(10);

v_sql varchar2(1000);

 

BEGIN

 

FOR v_row IN (

SELECT table_name

FROM all_tables

WHERE 1 = 1

--AND table_name = 'CT_BBS_MST'

AND owner = 'HFDADMIN'

-- AND rownum <= 100

ORDER BY table_name

) LOOP

 

begin

 

v_sql := 'SELECT count(*), to_char(max(reg_dtime), ''YYYY-MM-DD'') FROM ' || v_row.table_name;

 

-- dbms_output.put_line(v_sql);

 

EXECUTE immediate v_sql into v_cnt, v_ymd;

 

dbms_output.put_line(v_row.table_name || ':' || v_cnt || ':' || v_ymd);

 

EXCEPTION

WHEN OTHERS THEN

dbms_output.put_line('err :' || v_row.table_name || ', errmsg : ' || SQLERRM);

 

END;

 

END LOOP;

 

 

END

;