DBA_AUTOTASK

Check the current status of the autotask job。

Dba_Autotask_Client：（task 名称，运行窗口，资源组，状态）

select client_name,client_tag,status,window_group,consumer_group from Dba_Autotask_Client

select job_class_name,resource_consumer_group,service from dba_scheduler_job_classes;

PLAN and CONSUMER_GROUPS:

1,select * from dba_rsrc_plans where plan='DEFAULT_MAINTENANCE_PLAN';

2,Select  plan,group_or_subplan,type from dba_rsrc_plan_directives where plan not like 'DSS%' and  plan not  like 'ETL%' and plan not like 'MIXED%' order by plan;

3,select * from  DBA_RSRC_CONSUMER_GROUPS

Maintenance Window Groups:

查看数据库的窗口组和窗口信息：

select * from dba_scheduler_window_groups;

select * from dba_scheduler_wingroup_members；

select a.window_name,a.resource_plan,a.repeat_interval from dba_scheduler_windows a;

 

（ORA$AT_WGRP_OS，ORA$AT_WGRP_SA，ORA$AT_WGRP_SQ的汇总表。）

select * from DBA_AUTOTASK_WINDOW_CLIENTS;

WINDOW

select window_name, REPEAT_INTERVAL, DURATION,resource_plan from DBA_SCHEDULER_WINDOWS;---查看数据库定义的windown :定义的job的开始时间和资源使用情况

 

The duration times for windows of Monday thru Friday are from 10:00PM till 02:00AM and those for windows of Saturday thru Sunday are from 06:00AM  till 02:00AM.

WINDOW_GROUPS

select *  from DBA_SCHEDULER_WINDOW_GROUPS;

默认4个时间窗口组：3个AUTOTASK使用，1个MAINTENANCE_WINDOW_GROUP。

WINGROUP_MEMBERS

AUTOTASKE_WINDOW_GROUP：

（ORA$AT_WGRP_OS，ORA$AT_WGRP_SA, ORA$AT_WGRP_SQ）

select * from DBA_SCHEDULER_WINGROUP_MEMBERS  where window_group_name = 'ORA$AT_WGRP_OS';

MAINTENANCE_WINDOW_GROUP：

select OWNER,JOB_NAME,STATE,SCHEDULE_NAME from dba_scheduler_jobs –（这里面有些job用到MAINTENANCE_WINDOW_GROUP窗口）

select OWNER,JOB_NAME,STATE,SCHEDULE_NAME from dba_scheduler_job（这里面有些job用到MAINTENANCE_WINDOW_GROUP窗口）

（SCHEDULE_NAME：Name of the schedule that the job uses，can be a window or a window group。Job的开始时间--Scheduled Date）

 

 

查看是数据库自动维护作业否正在运行：

select * from DBA_AUTOTASK_WINDOW_CLIENTS; ---窗口打开，是否到了时间？

----只有在运行的时候该视图才有内容的（DBA_AUTOTASK_CLIENT_JOB）：

----  displays information about currently running Scheduler jobs created for automated maintenance tasks

select job_name,client_name, JOB_SCHEDULER_STATUS from DBA_AUTOTASK_CLIENT_JOB;--job是否已经执行完成？

手动运行计划：

EXEC DBMS_AUTO_TASK_IMMEDIATE.GATHER_OPTIMIZER_STATS;

关闭正在运行的任务：

--

BEGIN

DBMS_SCHEDULER.stop_JOB

(job_name  => ' ORA$AT_SQ_SQL_SW_26',force=>true);

END;

/

查看历史运行情况：

select * from DBA_AUTOTASK_JOB_HISTORY;

select * from dba_scheduler_job_run_details;（包含所有的scheduler）

 

关闭AUTOTASK_JOB：

select client_name,status from Dba_Autotask_Client

select * from DBA_AUTOTASK_WINDOW_CLIENTS

 BEGIN

   DBMS_AUTO_TASK_ADMIN.DISABLE(

                  client_name => 'auto optimizer stats collection',

                   operation => NULL, window_name => NULL);

  END;

  /

 

 

修改运行策略：

SHAPE  \* MERGEFORMAT

BEGIN
dbms_auto_task_admin.disable(client_name =>'auto optimizer stats collection', operation =>NULL, window_name =>'THURSDAY_WINDOW');
dbms_auto_task_admin.disable(client_name =>'auto optimizer stats collection', operation =>NULL, window_name =>'SATURDAY_WINDOW');
END;

 

BEGIN
dbms_auto_task_admin.enable(client_name => 'auto optimizer stats collection', operation => NULL, window_name => NULL);
END;

 

 

How To Manually Execute the Optimizer Statistics Auto Task in Oracle11g (文档 ID 731935.1)

 

In 11g the Auto-Task infrastructure replaced the need for the gather_stats_job and you can execute the following command to accomplish manual statistics collection:

SQL> exec DBMS_AUTO_TASK_IMMEDIATE.GATHER_OPTIMIZER_STATS

Note: The user performing this command must have DBA privilege

 

This prompts the Automated Maintenance Tasks subsystem into starting a job that will gather optimizer statistics, unless such a job is already running. The latter occurs if a maintenance window is currently open. If the job is spawned it will be named ORA$_AT_OS_MANUAL_nnnnnn (nnnnn is one or more decimal digits). Unlike regular Automated Maintenance jobs, the "MANUAL" job is not tied to a specific maintenance window.


To monitor the progress of the execution of the task use the following command:

SQL> select job_name,state from dba_scheduler_jobs where program_name='GATHER_STATS_PROG';

To interrupt the task use the following commands:

SQL> variable jobid varchar2(32)
SQL> exec select job_name into :jobid from dba_scheduler_jobs where program_name='GATHER_STATS_PROG';
SQL> print :jobid
SQL> exec dbms_scheduler.stop_job(:jobid,false)