Eason [資料科學//Python學習/資料庫] & [拍片&剪片]

建立procedure  利用游標掃描user_tables，再執行統計分析

********************************

create or replace procedure pro01
is
table_name varchar2(10);
cursor cur1 is
select table_name from user_tables;
begin 
open cur1;
loop
fetch cur1 into table_name;
exit when cur1%NOTFOUND;
dbms_stats.gather_table_stats('user1',table_name);
dbms_output.put_line('susess'||table_name);
end loop;
close cur1;
end;

**************************

透過SCHEDULER包來建立定期統計更新(時間為每周晚上11點執行，今天晚上11點開始)

***************************

select * from user_scheduler_jobs;
select * from user_scheduler_PROGRAMs;
select * from user_scheduler_SCHEDULEs;

BEGIN
DBMS_SCHEDULER.CREATE_PROGRAM (
program_name => 'hr_program',
program_action => 'pro01',
program_type => 'stored_procedure',
enabled => TRUE);
END;
/

BEGIN
DBMS_SCHEDULER.CREATE_SCHEDULE (
schedule_name => 'MySchedule3',
start_date => trunc(sysdate)+23/24,    --今天晚上11點開始
repeat_interval => 'FREQ=weekly; INTERVAL=1',  --一週一次
comments => 'a week');
END;
/

BEGIN
DBMS_SCHEDULER.CREATE_JOB (
job_name => 'hr.ex3',
program_name => 'hr_program',
schedule_name => 'MySchedule3',
enabled => true);
END;
/

*****************************

exec dbms_scheduler.disable('hr.ex3');
exec dbms_scheduler.enable('hr.ex3');