本文以Oracle11g为例，介绍了怎样查看Oracle数据库预先定义的维护任务，这些任务执行的时间，执行的操作。
由于Oracle定义的维护任务的信息分布在多个视图内，要获得关于Oracle 维护任务的信息就需要对这些视图进行查询。涉及的视图比较多，为了避免查询语句过于复杂，分成多个步骤来执行。
1 查询预定义的自动化任务
通过DBA_AUTOTASK_OPERATION视图查询数据库定义的自动化任务及任务执行的操作。
SQL>select CLIENT_NAME,OPERATION_NAME from DBA_AUTOTASK_OPERATION;CLIENT_NAME OPERATION_NAME ------------------------------------------------ ----------------------------------------------------------------auto optimizer stats collection auto optimizer stats job auto space advisor auto space advisor job sql tuning advisor automatic sql tuning task
2 查询任务执行的程序
从dba_scheduler_programs视图中可以查到每个程序执行的脚本或存储过程。
SQL> l 1*select OWNER,PROGRAM_NAME,PROGRAM_ACTION from dba_scheduler_programs SQL>/ OWNE PROGRAM_NAME PROGRAM_ACTION ---- -------------------------------- ---------------------------------------------------------------- SYS JDM_XFORM_SEQ_PROGRAM SYS.dbms_jdm_internal.xform_seq_task SYS JDM_XFORM_PROGRAM SYS.dbms_jdm_internal.xform_task SYS JDM_TEST_PROGRAM SYS.dbms_jdm_internal.test_task SYS JDM_SQL_APPLY_PROGRAM SYS.dbms_jdm_internal.sql_apply_task SYS JDM_PROFILE_PROGRAM SYS.dbms_jdm_internal.profile_task SYS JDM_PREDICT_PROGRAM SYS.dbms_jdm_internal.PREDICT_TASK SYS JDM_IMPORT_PROGRAM SYS.dbms_jdm_internal.import_task SYS JDM_EXPORT_PROGRAM SYS.dbms_jdm_internal.export_task SYS JDM_EXPLAIN_PROGRAM SYS.dbms_jdm_internal.explain_task SYS JDM_BUILD_PROGRAM SYS.dbms_jdm_internal.build_task SYS HS_PARALLEL_SAMPLING sys.dbms_hs_parallel_metadata.table_sampling SYS AQ$_PROPAGATION_PROGRAM SYS.DBMS_AQADM_SYS.aq$_propagation_procedure SYS PURGE_LOG_PROG dbms_scheduler.auto_purge SYS BSLN_MAINTAIN_STATS_PROG begin if prvt_advisor.is_pack_enabled('DIAGNOSTIC') then dbsnmp. bsln_internal.maintain_statistics; end if; end; SYS AUTO_SQL_TUNING_PROG DECLARE ename VARCHAR2(30);BEGIN ename := dbms_sqltune.execute_tuning_task('SYS_AUTO_SQL_TUNING_TASK'); END; SYS AUTO_SPACE_ADVISOR_PROG dbms_space.auto_space_advisor_job_proc SYS FILE_WATCHER_PROGRAM dbms_isched.file_watch_job SYS GATHER_STATS_PROG dbms_stats.gather_database_stats_job_proc SYS ORA$AGE_AUTOTASK_DATA dbms_autotask_prvt.age19 rows selected.
3 查询任务执行的窗口
SQL> l 1select a.CLIENT_NAME,a.STATUS,a.WINDOW_GROUP,b.WINDOW_NAME2from dba_autotask_client a left join3* DBA_SCHEDULER_WINGROUP_MEMBERS b on a.WINDOW_GROUP=b.WINDOW_GROUP_NAMEorderby1 SQL>/ CLIENT_NAME STATUS WINDOW_GROUP WINDOW_NAME ------------------------------------------------ -------- -------------------- ------------------------------ auto optimizer stats collection ENABLED ORA$AT_WGRP_OS SATURDAY_WINDOW auto optimizer stats collection ENABLED ORA$AT_WGRP_OS MONDAY_WINDOW auto optimizer stats collection ENABLED ORA$AT_WGRP_OS WEDNESDAY_WINDOW auto optimizer stats collection ENABLED ORA$AT_WGRP_OS TUESDAY_WINDOW auto optimizer stats collection ENABLED ORA$AT_WGRP_OS SUNDAY_WINDOW auto optimizer stats collection ENABLED ORA$AT_WGRP_OS FRIDAY_WINDOW auto optimizer stats collection ENABLED ORA$AT_WGRP_OS THURSDAY_WINDOW auto space advisor ENABLED ORA$AT_WGRP_SA SATURDAY_WINDOW auto space advisor ENABLED ORA$AT_WGRP_SA FRIDAY_WINDOW auto space advisor ENABLED ORA$AT_WGRP_SA WEDNESDAY_WINDOW auto space advisor ENABLED ORA$AT_WGRP_SA MONDAY_WINDOW auto space advisor ENABLED ORA$AT_WGRP_SA SUNDAY_WINDOW auto space advisor ENABLED ORA$AT_WGRP_SA TUESDAY_WINDOW auto space advisor ENABLED ORA$AT_WGRP_SA THURSDAY_WINDOW sql tuning advisor ENABLED ORA$AT_WGRP_SQ FRIDAY_WINDOW sql tuning advisor ENABLED ORA$AT_WGRP_SQ SUNDAY_WINDOW sql tuning advisor ENABLED ORA$AT_WGRP_SQ WEDNESDAY_WINDOW sql tuning advisor ENABLED ORA$AT_WGRP_SQ TUESDAY_WINDOW sql tuning advisor ENABLED ORA$AT_WGRP_SQ SATURDAY_WINDOW sql tuning advisor ENABLED ORA$AT_WGRP_SQ MONDAY_WINDOW sql tuning advisor ENABLED ORA$AT_WGRP_SQ THURSDAY_WINDOW 21 rows selected.
这三个任务在周一到周日的七个维护窗口内都会执行。
4 查看窗口的执行时间和执行间隔
SQL>select WINDOW_NAME,REPEAT_INTERVAL,DURATION from DBA_SCHEDULER_WINDOWS;WINDOW_NAME REPEAT_INTERVAL DURATION ------------------------------ -------------------------------- ---------------------------------------------------------------------------MONDAY_WINDOW freq=daily;byday=MON;byhour=22;b +00004:00:00 yminute=0; bysecond=0TUESDAY_WINDOW freq=daily;byday=TUE;byhour=22;b +00004:00:00 yminute=0; bysecond=0WEDNESDAY_WINDOW freq=daily;byday=WED;byhour=22;b +00004:00:00 yminute=0; bysecond=0THURSDAY_WINDOW freq=daily;byday=THU;byhour=22;b +00004:00:00 yminute=0; bysecond=0FRIDAY_WINDOW freq=daily;byday=FRI;byhour=22;b +00004:00:00 yminute=0; bysecond=0SATURDAY_WINDOW freq=daily;byday=SAT;byhour=6;by+00020:00:00 minute=0; bysecond=0SUNDAY_WINDOW freq=daily;byday=SUN;byhour=6;by+00020:00:00 minute=0; bysecond=0WEEKNIGHT_WINDOW freq=daily;byday=MON,TUE,WED,THU +00008:00:00,FRI;byhour=22;byminute=0; bysec ond=0WEEKEND_WINDOW freq=daily;byday=SAT;byhour=0;by+00200:00:00 minute=0;bysecond=09 rows selected.
这里DURATION列的前三个数字表时日期，后面表示时间。
