1、需求：
Oracle 11g统计信息自动收集默认22点开启，现想在白天临时开启(且不让其他job执行)。
2、具体操作
1) broken job
|
conn CTI/123456
select 'exec dbms_job.broken('||job||',true);' from dba_jobs;
select job,broken from dba_jobs;
|
2)打开job参数
|
alter system set job_queue_processes=100 scope=both sid='*';
|
3）create window,
时间：17:00开始，并加入组MAINTENANCE_WINDOW_GROUP
|
BEGIN
dbms_scheduler.create_window(
window_name => 'TEMP_STAT_WINDOW',
duration => numtodsinterval(8, 'hour'),
resource_plan => 'DEFAULT_MAINTENANCE_PLAN',
repeat_interval => 'FREQ=DAILY;BYHOUR=17;BYMINUTE=00;BYSECOND=0');
END;
/
BEGIN
dbms_scheduler.add_window_group_member(
group_name => 'MAINTENANCE_WINDOW_GROUP',
window_list => 'TEMP_STAT_WINDOW');
END;
/
|
4) 监控进度
|
--查看进度
select count(*) from dba_tables where LAST_ANALYZED is not null;
select owner,table_name,rownum,LAST_ANALYZED from dba_tables order by LAST_ANALYZED;
--查看window是否active
select a.window_name,a.next_start_date,a.active from dba_scheduler_windows a
inner join dba_scheduler_wingroup_members b on a.window_name = b.window_name
where b.window_group_name='ORA$AT_WGRP_OS';
--统计信息历史执行情况
col client_name for a35
col window_name for a20
col window_start_time for a30
col window_duration for a30
col window_end_time for a20
set linesize 200
select client_name,window_name,window_start_time,window_duration,window_end_time
from dba_autotask_client_history where client_name like '%stats%';
--大分区表
select table_name,partition_name,num_rows from dba_TAB_PARTITIONS where table_name='TF_USER';
select count(*) from cti.tf_user partition(PART_USER_0013);
|
5) 删除临时window
|
--从window组中移除
BEGIN
DBMS_SCHEDULER.REMOVE_WINDOW_GROUP_MEMBER(
group_name => 'MAINTENANCE_WINDOW_GROUP',
window_list => 'TEMP_STAT_WINDOW');
END;
/
--drop window
SQL> exec dbms_scheduler.drop_window('sys.TEMP_STAT_WINDOW');
|
注：执行drop_window报错：
|
exec dbms_scheduler.drop_window('sys.TEMP_STAT_WINDOW');
ORA-27480: window "TEMP_STAT_WINDOW" is currently open
解决办法：
exec dbms_scheduler.close_window('sys.TEMP_STAT_WINDOW');
或
exec dbms_scheduler.drop_window('sys.TEMP_STAT_WINDOW',TRUE);
|
注：也可采用下面方法直接收集数据库统计信息，效果一样：
|
exec dbms_stats.gather_database_stats(options=>'gather auto');
|
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/27126919/viewspace-1973958/ ，如需转载，请注明出处，否则将追究法律责任。
请登录后发表评论
登录
全部评论
