dbms_job包的基本用法

statspack的spauto.sql脚本为大家提供了一个学习dbms_job的例子，这里简单记录一下：

--  Schedule a snapshot to be run on this instance every hour, on the hour

variable jobno number;
variable instno number;
begin
  select instance_number into :instno from v$instance;
  dbms_job.submit(:jobno, 'statspack.snap;', trunc(sysdate+1/24,'HH'), 'trunc(SYSDATE+1/24,''HH'')', TRUE, :instno);
  commit;
end;
/

--这里表示statspack收集数据的时间
1/24   HH 每小时一次
1/48   MI   每30分钟一次
1/144 MI   每10分钟一次
1/288 MI   每5分钟一次

prompt
prompt  Job number for automated statistics collection for this instance
prompt  ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
prompt  Note that this job number is needed when modifying or removing
prompt  the job:
print jobno

prompt
prompt  Job queue process
prompt  ~~~~~~~~~~~~~~~~~
prompt  Below is the current setting of the job_queue_processes init.ora
prompt  parameter - the value for this parameter must be greater
prompt  than 0 to use automatic statistics gathering:
show parameter job_queue_processes
prompt

prompt
prompt  Next scheduled run
prompt  ~~~~~~~~~~~~~~~~~~
prompt  The next scheduled run for this job is:
select job, next_date, next_sec
  from user_jobs
 where job = :jobno;

spool off;

那么我该如何删除创建后的job呢？

其实desc一下我们的dbms_job  package答案自现：

SQL> desc dbms_job
FUNCTION BACKGROUND_PROCESS RETURNS BOOLEAN
PROCEDURE BROKEN
参数名称                       类型                    输入/输出默认值？
------------------------------ ----------------------- ------ --------
 JOB                            BINARY_INTEGER          IN
 BROKEN                         BOOLEAN                 IN
 NEXT_DATE                      DATE                    IN     DEFAULT
PROCEDURE CHANGE
参数名称                       类型                    输入/输出默认值？
------------------------------ ----------------------- ------ --------
 JOB                            BINARY_INTEGER          IN
 WHAT                           VARCHAR2                IN
 NEXT_DATE                      DATE                    IN
 INTERVAL                       VARCHAR2                IN
 INSTANCE                       BINARY_INTEGER          IN     DEFAULT
 FORCE                          BOOLEAN                 IN     DEFAULT
PROCEDURE INSTANCE
参数名称                       类型                    输入/输出默认值？
------------------------------ ----------------------- ------ --------
 JOB                            BINARY_INTEGER          IN
 INSTANCE                       BINARY_INTEGER          IN
 FORCE                          BOOLEAN                 IN     DEFAULT
PROCEDURE INTERVAL
参数名称                       类型                    输入/输出默认值？
------------------------------ ----------------------- ------ --------
 JOB                            BINARY_INTEGER          IN
 INTERVAL                       VARCHAR2                IN
PROCEDURE ISUBMIT
参数名称                       类型                    输入/输出默认值？
------------------------------ ----------------------- ------ --------
 JOB                            BINARY_INTEGER          IN
 WHAT                           VARCHAR2                IN
 NEXT_DATE                      DATE                    IN
 INTERVAL                       VARCHAR2                IN     DEFAULT
 NO_PARSE                       BOOLEAN                 IN     DEFAULT
FUNCTION IS_JOBQ RETURNS BOOLEAN
PROCEDURE NEXT_DATE
参数名称                       类型                    输入/输出默认值？
------------------------------ ----------------------- ------ --------
 JOB                            BINARY_INTEGER          IN
 NEXT_DATE                      DATE                    IN
PROCEDURE REMOVE
参数名称                       类型                    输入/输出默认值？
------------------------------ ----------------------- ------ --------
 JOB                            BINARY_INTEGER          IN
PROCEDURE RUN
参数名称                       类型                    输入/输出默认值？
------------------------------ ----------------------- ------ --------
 JOB                            BINARY_INTEGER          IN
 FORCE                          BOOLEAN                 IN     DEFAULT
PROCEDURE SUBMIT
参数名称                       类型                    输入/输出默认值？
------------------------------ ----------------------- ------ --------
 JOB                            BINARY_INTEGER          OUT
 WHAT                           VARCHAR2                IN
 NEXT_DATE                      DATE                    IN     DEFAULT
 INTERVAL                       VARCHAR2                IN     DEFAULT
 NO_PARSE                       BOOLEAN                 IN     DEFAULT
 INSTANCE                       BINARY_INTEGER          IN     DEFAULT
 FORCE                          BOOLEAN                 IN     DEFAULT
PROCEDURE USER_EXPORT
参数名称                       类型                    输入/输出默认值？
------------------------------ ----------------------- ------ --------
 JOB                            BINARY_INTEGER          IN
 MYCALL                         VARCHAR2                IN/OUT
PROCEDURE USER_EXPORT
参数名称                       类型                    输入/输出默认值？
------------------------------ ----------------------- ------ --------
 JOB                            BINARY_INTEGER          IN
 MYCALL                         VARCHAR2                IN/OUT
 MYINST                         VARCHAR2                IN/OUT
PROCEDURE WHAT
参数名称                       类型                    输入/输出默认值？
------------------------------ ----------------------- ------ --------
 JOB                            BINARY_INTEGER          IN
 WHAT                           VARCHAR2                IN

这里我们只需要输入dbms_job.remove(jobno)就ok了。

基本上就是这么简单。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/12361284/viewspace-207570/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/12361284/viewspace-207570/