dbms_scheduler package系列(一)

--附上網上關於此包的資料

---建立job
SQL> begin
  2  dbms_scheduler.create_job(job_name => 'job_1',job_type => 'executable',job_action => 'exp file=c:\t_exp.dmp tables=t_exp;',start_date => sysdate,repeat_interval => 'freq=daily;interval=1');
  3  end;
  4  /
 
PL/SQL procedure successfully completed

SQL> select * from user_scheduler_jobs;
 
JOB_NAME                       JOB_SUBNAME                    JOB_STYLE   JOB_CREATOR                    CLIENT_ID                                                        GLOBAL_UID                       PROGRAM_OWNER                                                                    PROGRAM_NAME                                                                     JOB_TYPE         JOB_ACTION                                                                       NUMBER_OF_ARGUMENTS SCHEDULE_OWNER                                                                   SCHEDULE_NAME                                                                    SCHEDULE_TYPE START_DATE                                                                       REPEAT_INTERVAL                                                                  EVENT_QUEUE_OWNER              EVENT_QUEUE_NAME               EVENT_QUEUE_AGENT                                                                EVENT_CONDITION                                                                  EVENT_RULE                                                        FILE_WATCHER_OWNER                                                               FILE_WATCHER_NAME                                                                END_DATE                                                                         JOB_CLASS                      ENABLED AUTO_DROP RESTARTABLE STATE           JOB_PRIORITY  RUN_COUNT   MAX_RUNS FAILURE_COUNT MAX_FAILURES RETRY_COUNT LAST_START_DATE                                                                  LAST_RUN_DURATION                                                               NEXT_RUN_DATE                                                                    SCHEDULE_LIMIT                                                                  MAX_RUN_DURATION                                                                LOGGING_LEVEL STOP_ON_WINDOW_CLOSE INSTANCE_STICKINESS RAISE_EVENTS                                                                     SYSTEM JOB_WEIGHT NLS_ENV                                                                          SOURCE                                                                           NUMBER_OF_DESTINATIONS DESTINATION_OWNER                                                                DESTINATION                                                                      CREDENTIAL_OWNER               CREDENTIAL_NAME                INSTANCE_ID DEFERRED_DROP ALLOW_RUNS_IN_RESTRICTED_MODE COMMENTS                                                                              FLAGS
------------------------------ ------------------------------ ----------- ------------------------------ ---------------------------------------------------------------- -------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ---------------- -------------------------------------------------------------------------------- ------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ------------------------------ ------------------------------ -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ----------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ------------------------------ ------- --------- ----------- --------------- ------------ ---------- ---------- ------------- ------------ ----------- -------------------------------------------------------------------------------- ------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ------------------------------------------------------------------------------- ------------------------------------------------------------------------------- ------------- -------------------- ------------------- -------------------------------------------------------------------------------- ------ ---------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ---------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ------------------------------ ------------------------------ ----------- ------------- ----------------------------- -------------------------------------------------------------------------------- ----------
JOB_1                                                         REGULAR     SCOTT                                                                                                                                                                                                                                                                                              EXECUTABLE       exp file=c:\t_exp.dmp tables=t_exp;                                       0                                                                                                                                                                   CALENDAR      31-JAN-13 09.38.31.000000 PM +08:00                                              freq=daily;interval=1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 DEFAULT_JOB_CLASS              FALSE   TRUE      FALSE       DISABLED                   3          0                        0                        0                                                                                                                                                                                                                                                                                                                                                                                                                   OFF           FALSE                TRUE                                                                                                 FALSE           1 NLS_LANGUAGE='AMERICAN' NLS_TERRITORY='AMERICA' NLS_CURRENCY='$' NLS_ISO_CURRENC                                                                                                       1                                                                                                                                                                                                                                             FALSE         FALSE                                                                                                             2099248
 

SQL> begin
  2  dbms_scheduler.enable(name => 'JOB_1');
  3  end;
  4  /
 
PL/SQL procedure successfully completed
 
SQL> select usj.ENABLED from user_scheduler_jobs usj;
 
ENABLED
-------
TRUE

SQL> begin
  2  dbms_scheduler.run_job(job_name => 'JOB_1');
  3  end;
  4  /
 
begin
dbms_scheduler.run_job(job_name => 'JOB_1');
end;
 
ORA-27370: job slave failed to launch a job of type EXECUTABLE
ORA-27300: OS system dependent operation:accessing job scheduler service failed with status: 2
ORA-27301: OS failure message: ？？？????？???？？???
ORA-27302: failure occurred at: sjsec 6a
ORA-27303: additional information: ？？？????？???？？???
ORA-06512: at "SYS.DBMS_ISCHED", line 185
ORA-06512: at "SYS.DBMS_SCHEDULER", line 486
ORA-06512: at line 3

 

---檢視scheduler機制下的job
SQL> select * from user_scheduler_jobs usj;
 
JOB_NAME                       JOB_SUBNAME                    JOB_STYLE   JOB_CREATOR                    CLIENT_ID                                                        GLOBAL_UID                       PROGRAM_OWNER                                                                    PROGRAM_NAME                                                                     JOB_TYPE         JOB_ACTION                                                                       NUMBER_OF_ARGUMENTS SCHEDULE_OWNER                                                                   SCHEDULE_NAME                                                                    SCHEDULE_TYPE START_DATE                                                                       REPEAT_INTERVAL                                                                  EVENT_QUEUE_OWNER              EVENT_QUEUE_NAME               EVENT_QUEUE_AGENT                                                                EVENT_CONDITION                                                                  EVENT_RULE                                                        FILE_WATCHER_OWNER                                                               FILE_WATCHER_NAME                                                                END_DATE                                                                         JOB_CLASS                      ENABLED AUTO_DROP RESTARTABLE STATE           JOB_PRIORITY  RUN_COUNT   MAX_RUNS FAILURE_COUNT MAX_FAILURES RETRY_COUNT LAST_START_DATE                                                                  LAST_RUN_DURATION                                                               NEXT_RUN_DATE                                                                    SCHEDULE_LIMIT                                                                  MAX_RUN_DURATION                                                                LOGGING_LEVEL STOP_ON_WINDOW_CLOSE INSTANCE_STICKINESS RAISE_EVENTS                                                                     SYSTEM JOB_WEIGHT NLS_ENV                                                                          SOURCE                                                                           NUMBER_OF_DESTINATIONS DESTINATION_OWNER                                                                DESTINATION                                                                      CREDENTIAL_OWNER               CREDENTIAL_NAME                INSTANCE_ID DEFERRED_DROP ALLOW_RUNS_IN_RESTRICTED_MODE COMMENTS                                                                              FLAGS
------------------------------ ------------------------------ ----------- ------------------------------ ---------------------------------------------------------------- -------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ---------------- -------------------------------------------------------------------------------- ------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ------------------------------ ------------------------------ -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ----------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ------------------------------ ------- --------- ----------- --------------- ------------ ---------- ---------- ------------- ------------ ----------- -------------------------------------------------------------------------------- ------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ------------------------------------------------------------------------------- ------------------------------------------------------------------------------- ------------- -------------------- ------------------- -------------------------------------------------------------------------------- ------ ---------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ---------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ------------------------------ ------------------------------ ----------- ------------- ----------------------------- -------------------------------------------------------------------------------- ----------
JOB_1                                                         REGULAR     SCOTT                                                                                                                                                                                                                                                                                              EXECUTABLE       exp file=c:\t_exp.dmp tables=t_exp;                                       0                                                                                                                                                                   CALENDAR      31-JAN-13 09.38.31.000000 PM +08:00                                              freq=daily;interval=1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 DEFAULT_JOB_CLASS              TRUE    TRUE      FALSE       SCHEDULED                  3          0                        0                        0                                                                                                                                                                  01-FEB-13 09.38.31.200000 PM +08:00                                                                                                                                                                                                              OFF           FALSE                TRUE                                                                                                 FALSE           1 NLS_LANGUAGE='AMERICAN' NLS_TERRITORY='AMERICA' NLS_CURRENCY='$' NLS_ISO_CURRENC                                                                                                       1                                                                                                                                                                                                                                             FALSE         FALSE                                                                                                             2099248

---檢視job的相關引數,竟無記錄,怪怪
SQL> select * from user_scheduler_job_args;
 
JOB_NAME                       ARGUMENT_NAME                  ARGUMENT_POSITION ARGUMENT_TYPE                                                 VALUE                                                                            ANYDATA_VALUE OUT_ARGUMENT
------------------------------ ------------------------------ ----------------- ------------------------------------------------------------- -------------------------------------------------------------------------------- ------------- ------------

---刪除job
SQL> begin
  2  dbms_scheduler.drop_job(job_name => 'JOB_1',force => true);
  3  end;
  4  /
 
PL/SQL procedure successfully completed
 

---查詢無值
SQL> select * from user_scheduler_jobs usj;