tablo satır sayıları arşivi oracle plsql

Periyodik olarak tablolardaki satır sayılarını saklamak için kullanılabilecek Oracle stored procedure.


create table sample_schema.table_counts (
owner_schema varchar2(30),
table_name varchar2(30),
row_count number
);

create or replace procedure sample_schema.table_counts_calculator(p_schema varchar2)
is

v_user varchar2(30);
v_schema varchar2(30);
v_now date;
v_cnt number;

cursor c_tables(cp_schema varchar2)
is
select owner, table_name
from all_tables
where owner = cp_schema
order by 1;

begin

select user into v_user from dual;

v_schema := upper(p_schema);

select sysdate into v_now from dual;

for tbl in c_tables(v_schema)
loop

    execute immediate '
    select /*+parallel(8)*/ count(*) cnt
    from '||tbl.owner||'.'||tbl.table_name||'
    ' into v_cnt;

    execute immediate '
    insert into '||v_user||'.table_counts values ( '''||tbl.owner||''','''||tbl.table_name||''', '||v_cnt||' )
    ';

end loop;

commit;

end;
/

Bir cevap yazın