Explain how to create and monitor Oracle scheduled jobs, and explain oraclejob

Explain how to create and monitor Oracle scheduled jobs, and explain oraclejob

I. How to Create a scheduled JOB by version)

Method 1: sys. dbms_job.submit () before 10 GB ()

Method 2: sys. Dbms_Scheduler.create_job () after 10 GB ()

Ii. JOB operation monitoring:

If you need to monitor job running before 10 Gb, you need to create a job running log table by yourself, and perform operations on the log table before the JOB is executed to record the necessary information, after 10 Gb, you can use the self-built job running log summary table and detail table.

-- View the information of the created JOB

SELECT * FROM User_Scheduler_Jobs;

-- Query the JOB running log, which is recorded no matter whether the JOB succeeds or fails, and can be queried with different permissions, for example, DBA. The example is as follows:

SELECT * FROM user_scheduler_job_log; -- the current user's

SELECT * FROM dba_scheduler_job_log; -- dba role

-- View detailed JOB running logs

SELECT * FROM user_scheduler_job_run_details;

3. delete a JOB

Begin

DBMS_JOB.remove ('job ');

End;

If you do not know the job number, you can query the value of what field in the table user_jobs or dba_jobs, and find the corresponding job number based on the name of the execution package Body Process pointed to when you created the job.

Complete example:

BEGIN sys. dbms_Scheduler.create_job (JOB_NAME => 'Update _ demot', -- JOB name, custom JOB_TYPE => 'stored _ PROCEDURE ', -- PL/SQL block, package process, operating system commands, and shell script files can be used. For details, see JOB_ACTION => 'adddemot'. -- the specified content for running a JOB, different JOB_CLASS => 'default _ JOB_CLASS 'based on JOB_TYPE, -- class START_DATE => SYSDATE associated with the task, -- END_DATE => NULL, -- specifies the JOB expiration time. If this parameter is not set, the JOB is executed repeatedly according to the cycle set by the REPEAT_INTERVAL parameter until the set MAX_RUNS or MAX_FAILURES value is reached. REPEAT_INTERVAL => 'freq = SECONDLY; INTERVAL = 5', -- JOB execution cycle. The FREQ keyword is used to specify the INTERVAL. Optional parameters include: YEARLY, MONTHLY, WEEKLY, DAILY, HOURLY, MINUTELY, and SECONDLY, respectively, indicating the unit of year, month, week, day, hour, minute, second, and so on. ENABLED => TRUE, -- whether the JOB is activated and self-started. The default value is false, indicating that AUTO_DROP => FALSE is executed only manually. -- when a JOB reaches certain conditions, it is automatically deleted (the JOB has expired; the maximum number of running tasks has reached the value set by MAX_RUNS. The REPEAT_INTERVAL parameter is not specified for the task and only runs once.) COMMENTS => 'new JOB creation test'); END;

PS:

JOB_TYPE is like a subordinate choice: the corresponding JOB_ACTION is different.

PLSQL_BLOCK: indicates that the task runs a PL/SQL anonymous block.

STORED_PROCEDURE: indicates that the task executes the Oracle process (including PL/SQL PROCEDURE and Java PROCEDURE). This parameter value is specified in this example.

EXECUTABLE: indicates that the task runs an external program, such as an operating system command.

CHAIN: indicates that the task runs a CHAIN.