制定作业，定时采集系统表，业务表的统计信息，
3.1 以 sys 身份，使用 sqlplus 工具，登录生产库，创建定时作业，采集系统表，每周日早上7点开始运行。
create procedure pro_analyze_dict as
begin
dbms_stats.gather_dictionary_stats();
dbms_stats.gather_fixed_objects_stats();
end pro_analyze_dict;
/
begin
dbms_scheduler.create_schedule
(schedule_name => 'SCHEDULE_SUNDAY_AT_0700',
start_date => TO_DATE('2016-08-04 00:00:00','YYYY-MM-DD HH24:MI:SS'),
end_date => NULL,
repeat_interval => 'FREQ=WEEKLY;BYDAY=SUN;BYHOUR=07; BYMINUTE=00; BYSECOND=0',
comments => '');
end;
/
exec dbms_scheduler.drop_program(program_name=> 'PROC_ANALYZE_DICT');
begin
dbms_scheduler.create_program
(program_name => 'PROC_ANALYZE_DICT',
program_type => 'PLSQL_BLOCK',
program_action => 'BEGIN pro_analyze_dict; END;',
number_of_arguments => 0,
enabled => TRUE,
comments => '2016-08-07 07:00:00');
end;
/
begin
dbms_scheduler.create_job
(job_name => 'JOB_ANALYZE_DICT',
program_name => 'PROC_ANALYZE_DICT',
schedule_name => 'SCHEDULE_SUNDAY_AT_0700',
enabled => TRUE,
comments => 'zhang.huafu, analyze sys tables'' stats and fixed objects'' stats');
end;
/
3.2 关闭系统自带的统计信息等功能。
exec dbms_auto_task_admin.disable(client_name => 'auto space advisor', operation => NULL, window_name => null);
exec dbms_auto_task_admin.disable(client_name => 'sql tuning advisor',operation => null, window_name => null);
exec dbms_auto_task_admin.disable(client_name => 'auto optimizer stats collection', operation => NULL, window_name => NULL);
3.3 以 sys 身份，用 sqlplus 工具，登录生产库，创建一个常用的管理账户，并授予合适的权限：
create user manager identified by "20160803";
grant dba to manager;
grant drop any table to manager;
grant delete any table to manager;
grant select any table to manager;
grant select on dba_tables to manager;
grant select on dba_objects to manager;
grant select on dba_recyclebin to manager;
grant select on dba_mview_logs to manager;
grant analyze any to manager;
grant update(failure_count) on sys.scheduler$_job to manager;
--3.3.1 以 manager 身份登录，创建采集业务表的定时作业，每天早上5点运行。
create or replace procedure manager.pro_analyze_stats as
begin
for i in (select owner, table_name from dba_tables a where owner in ('LNZHONGYI','ZHONGYICS') and temporary='N' and table_name not like 'TMP%'
and not exists (select 1 from dba_mview_logs where log_owner=a.owner and log_table=a.table_name) order by owner, last_analyzed nulls first) loop
dbms_stats.gather_table_stats(i.owner,i.table_name);
end loop;
for i in (select owner, table_name from dba_tables where owner in ('LNZHONGYI','ZHONGYICS') and temporary='Y' and last_analyzed is not null
order by owner, table_name) loop
dbms_stats.delete_table_stats(i.owner,i.table_name);
end loop;
end pro_analyze_stats;
/
说明：红色部分，是业务账户名，请根据实际情况更改。
蓝色部分，用于清空临时表（ORACLE定义的临时表）的统计信息，这在11G版本是可行的，到了12C，ORACLE对临时表的统计信息作了进一步的改进，需要分情况处理了。
begin
dbms_scheduler.create_schedule
(schedule_name => 'SCHEDULE_DAILY_AT_0510',
start_date => TO_DATE('2016-08-04 00:00:00','YYYY-MM-DD HH24:MI:SS'),
end_date => NULL,
repeat_interval => 'FREQ=DAILY; BYHOUR=05; BYMINUTE=10; BYSECOND=0',
comments => '');
end;
/
说明：红色部分说明的是作业的运行时间点，可以更改。
begin
dbms_scheduler.create_program
(program_name => 'PROC_ANALYZE_STATS',
program_type => 'PLSQL_BLOCK',
program_action => 'BEGIN pro_analyze_stats; END;',
number_of_arguments => 0,
enabled => TRUE,
comments => '2017-03-13 13:56');
end;
/
begin
dbms_scheduler.create_job
(job_name => 'JOB_ANALYZE_STATS',
program_name => 'PROC_ANALYZE_STATS',
schedule_name => 'SCHEDULE_DAILY_AT_0510',
enabled => TRUE,
comments => 'zhang.huafu, analyze tables'' stats');
end;
/
--3.3.2 创建一作业，删除过期的临时表
-- 创建删除临时表的过程。
create or replace procedure manager.pro_purge_tmptable as
begin
for i in (select table_name, last_analyzed from dba_tables a
where owner='LNZHONGYI' and table_name like 'TMP%' and length(table_name) >= 30
and exists (select 1 from dba_objects where owner='LNZHONGYI'
and object_type='TABLE' and object_name like 'TMP%' and object_name=a.table_name and created < sysdate - 1) ) loop
begin
execute immediate 'drop table LNZHONGYI.'||i.table_name||' purge';
end;
end loop;
delete from lnzhongyi.t_bas_temporarytablename a where not exists (select 1 from dba_tables where owner='LNZHONGYI' and table_name=a.ftablename);
commit;
end pro_purge_tmptable;
/
说明：1 脚本中，红色部分，是用于业务运营的数据库账户。
2 据我了解，这些临时表都应该记录在该账户下的：T_BAS_TEMPORARYTABLENAME 此表中。但由于该表中对临时表的创建时间，FCREATEDATE字段，没有强制 NOT NULL，我担心某些记录没时间，这样，若读取该表的数据来做删除操作，这些没时间的记录，将被漏掉。
SYS@orcl>desc lnzhongyi.t_bas_temporarytablename
Name Null? Type
----------------------------------------- -------- --------------
FTABLENAME NOT NULL VARCHAR2(30)
FCREATEDATE DATE
FUSERTOKEN VARCHAR2(36)
FPROCESSTYPE NUMBER(10)
FUSERTRANSACTIONID VARCHAR2(200)
-- 创建作业运行用的时间表，每小时运行一次。
begin
dbms_scheduler.create_schedule
(schedule_name => 'SCHEDULE_HOURLY_INTERVAL_1',
start_date => TO_DATE('2017-04-18 15:00:00','YYYY-MM-DD HH24:MI:SS'),
end_date => NULL,
repeat_interval => 'FREQ=HOURLY; INTERVAL=1; BYMINUTE=17; BYSECOND=30',
comments => '');
end;
/
-- 创建作业运行的PROGRAM。
begin
dbms_scheduler.create_program
(program_name => 'PROC_PURGE_TMPTABLE',
program_type => 'PLSQL_BLOCK',
program_action => 'BEGIN pro_purge_tmptable; END;',
number_of_arguments => 0,
enabled => TRUE,
comments => '2016-08-04 06:30:00');
end;
/
-- 创建定期作业，
begin
dbms_scheduler.create_job
(job_name => 'JOB_PURGE_TMPTABLE',
program_name => 'PROC_PURGE_TMPTABLE',
schedule_name => 'SCHEDULE_HOURLY_INTERVAL_1',
enabled => TRUE,
comments => '');
end;
/
推荐阅读