如何重建DBMS_SCHEDULER默认的job和任务

如何重建DBMS_SCHEDULER默认的job和任务，适合任务失效或被删除的情况。（>=Oracle10.2.0.4版本）

例如统计信息收集任务不小心被删除了，如何重建？

[oracle@lnkf dmp]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Wed Jan 31 11:56:13 2024

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> 
SQL> SELECT owner,job_name,enabled FROM DBA_SCHEDULER_JOBS WHERE job_name ='BSLN_MAINTAIN_STATS_JOB';

OWNER                          JOB_NAME                       ENABL
------------------------------ ------------------------------ -----
SYS                            BSLN_MAINTAIN_STATS_JOB        TRUE

SQL> EXECUTE DBMS_SCHEDULER.DROP_JOB('SYS.BSLN_MAINTAIN_STATS_JOB');

PL/SQL procedure successfully completed.

SQL> SELECT owner,job_name,enabled FROM DBA_SCHEDULER_JOBS WHERE job_name ='BSLN_MAINTAIN_STATS_JOB';

no rows selected

SQL> @$ORACLE_HOME/rdbms/admin/execbsln.sql

PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.

SQL> SELECT owner,job_name,enabled FROM DBA_SCHEDULER_JOBS WHERE job_name ='BSLN_MAINTAIN_STATS_JOB';

OWNER                          JOB_NAME                       ENABL
------------------------------ ------------------------------ -----
SYS                            BSLN_MAINTAIN_STATS_JOB        TRUE

19c默认job，task参考如下：
启用的JOB：

select JOB_NAME,PROGRAM_NAME,JOB_TYPE,ENABLED,COMMENTS,JOB_ACTION  from DBA_SCHEDULER_JOBS where enabled='TRUE' order by 1,3;

1. BSLN_MAINTAIN_STATS_JOB
2. CLEANUP_NON_EXIST_OBJ
3. CLEANUP_ONLINE_IND_BUILD
4. CLEANUP_ONLINE_PMO
5. CLEANUP_TAB_IOT_PMO
6. CLEANUP_TRANSIENT_PKG
7. CLEANUP_TRANSIENT_TYPE
8. DRA_REEVALUATE_OPEN_FAILURES
9. FILE_SIZE_UPD
10. ORA$AUTOTASK_CLEAN
11. PMO_DEFERRED_GIDX_MAINT_JOB
12. PURGE_LOG
13. RSE$CLEAN_RECOVERABLE_SCRIPT
14. SM$CLEAN_AUTO_SPLIT_MERGE

全部JOB：

select JOB_NAME,PROGRAM_NAME,JOB_TYPE,ENABLED,COMMENTS,JOB_ACTION from DBA_SCHEDULER_JOBS order by 1,3;

1. BSLN_MAINTAIN_STATS_JOB
2. CLEANUP_NON_EXIST_OBJ
3. CLEANUP_ONLINE_IND_BUILD
4. CLEANUP_ONLINE_PMO
5. CLEANUP_TAB_IOT_PMO
6. CLEANUP_TRANSIENT_PKG
7. CLEANUP_TRANSIENT_TYPE
8. DRA_REEVALUATE_OPEN_FAILURES
9. FGR$AUTOPURGE_JOB
10. FILE_SIZE_UPD
11. FILE_WATCHER
12. HM_CREATE_OFFLINE_DICTIONARY
13. LOAD_OPATCH_INVENTORY
14. ORA$AUTOTASK_CLEAN
15. ORA$PREPLUGIN_BACKUP_JOB
16. PMO_DEFERRED_GIDX_MAINT_JOB
17. PURGE_LOG
18. RSE$CLEAN_RECOVERABLE_SCRIPT
19. SM$CLEAN_AUTO_SPLIT_MERGE
20. XMLDB_NFS_CLEANUP_JOB

参考：How to Rebuild DBMS_SCHEDULER Default Jobs and Autotasks (Doc ID 2089546.1)

其它默认job和task参考如下方法重建：

1.Jobs:
ORA$AUTOTASK_CLEAN - Create autotask repository data ageing job;
HM_CREATE_OFFLINE_DICTIONARY - Create job for creation of offline dictionary for Database Repair Advisor;
DRA_REEVALUATE_OPEN_FAILURES - Create job for reevaluate open failures for Database Repair Advisor;
Programs:
gather_stats_prog - Create gather stats program;
auto_space_advisor_prog - Create auto space advisor program;
ora$age_autotask_data - Create autotask repository data ageing program;

重建办法：
@$ORACLE_HOME/rdbms/admin/catnomwn.sql -- this drops is any windows or leftovers of the autotasks remain
@$ORACLE_HOME/rdbms/admin/catmwin.sql -- this recreates them

2.Jobs:
PURGE_LOG - Create purge log job
FILE_WATCHER - Create file watcher job
CLEANUP_NON_EXIST_OBJ - create scheduler job to remove non-existent objects
CLEANUP_ONLINE_PMO - scheduler job to perform online PMO cleanup
CLEANUP_TAB_IOT_PMO - create scheduler job to perform tab$ and tabpart$ cleanup
CLEANUP_TRANSIENT_PKG - create scheduler job to cleanup cursor transient packages
CLEANUP_TRANSIENT_TYPE - create scheduler job to cleanup transient types
FILE_SIZE_UPD - create scheduler job to perform file size update
PMO_DEFERRED_GIDX_MAINT_JOB - create deferred global index maintenance job
Programs:
purge_log_prog - Create purge log program
FILE_WATCHER_PROGRAM - Create file watcher program
PMO_DEFERRED_GIDX_MAINT - create deferred global index maintenance program

重建办法：
@$ORACLE_HOME/rdbms/admin/execsch.sql


3.BSLN_MAINTAIN_STATS_JOB - Create compute statistics job
Programs:
BSLN_MAINTAIN_STATS_PROG

重建办法：
@$ORACLE_HOME/rdbms/admin/execbsln.sql


4.FGR$AUTOPURGE_JOB - Create auto-purge job for file groups
RSE$CLEAN_RECOVERABLE_SCRIPT - Create daily job to clean recoverable scripts
SM$CLEAN_AUTO_SPLIT_MERGE - Create daily job to clean auto split merge views

重建办法：
@$ORACLE_HOME/rdbms/admin/execstr.sql


5.Load_opatch_inventory - Create job on current oracle home
重建办法：
@$ORACLE_HOME/rdbms/admin/execqopi.sql


6.AUTO_SQL_TUNING_PROG - Create the automatic SQL Tuning program

重建办法：
@$ORACLE_HOME/rdbms/admin/execsqlt.sql


 注意，这些作业中有一些在旧版本中不存在。