PL/SQL developer 8.0 questions about Oracle Jobs Creation

Questions about creating Oracle jobs through PL/SQL developer 8.0 [transfer] test environment: oracle9i, PL/SQL developer 8.0, PL/SQL developer 7.0

After a colleague created Oracle jobs through PL/SQL developer 8.0, he could not see the jobs folder, but under the dbms_jobs folder. After creating Oracle jobs through PL/SQL developer 7.0, you can see it in the jobs folder. This is confusing.

1: After connecting to oralce through PL/SQL developer 7.0, you can see the Custom jobs under the dbms_jobs folder (PL/SQL developer 8.0 comes with, PL/SQL developer 7.0 does not) in the jobs folder. Therefore, using PL/SQL developer 8.0 to create Oracle jobs in the dbms_jobs folder is the same as using PL/SQL developer 8.0 to create Oracle jobs in the jobs folder.

2: Why is Oracle jobs created through PL/SQL developer 8.0 not displayed in the jobs folder?

Right-click jobs in PL/SQL developer 8.0 to_do_list and choose new from the new jobs window, which is very different from PL/SQL developer 7.0. It seems that the jobs of PL/SQL developer 8.0 do not support oralce 9i. Because some options do not support Oracle 9i

Error: text. The table or view does not exist. Select owner, program_name from all_scheduler_programs. Some similar errors. Oralce9i does not support the method of creating a new job by right-clicking "new" in PL/SQL developer 8.0 jobs.

　　　　　　

The following text comes from the network information:

PL/SQL developer 8.0 only supports new jobs created using the Oracle 10 DBMS_Scheduler package (enhanced scheduler tasks)

Moderate Resolution of Oracle 10g DBMS_Scheduler

The usage of DBMS_Scheduler is described in detail. For more information, see the following:
DBMS_Scheduler is a new package in Oracle 10 Gb. Compared with the old version of dbms_job package, DBMS_Scheduler has many new features. I will introduce how to use this package through a series of articles.
1. Create a job
What is a job? Simply put, schedule is added with the task description. There are also some required parameters.
The "task" mentioned here can be a stored procedure in the database, an anonymous PL/SQL block, or an operating system-level script.
You can define a "plan" in two ways ":
1) Use dbms_schduler.create_schedule to define a plan;
2) Call the dbms_schduler.create_jobe process and specify it directly (details will be given below)
When creating a plan, you must specify at least the following attributes, which are required for job running:
Start Time (start_time );
Repeat_interval );
End Time (end_time)
In addition, there are many additional parameters for a job:
Job_class
Job_priority
Auto_drop
Restartable
Max_runs
Max_failures
Schedule_limit
Logging_level
Next, I will explain it in the form of Q &.
Q1: How can I query job attributes in a database?
A1: There are two methods:
1) query (DBA | all | user) _ scheduler_jobs View
(Tip: Select DBA | all | user view based on different user permissions)
2) Call the get_attribute process in the DBMS_Scheduler package
Q2: how to set these attributes?
A2: There are two methods
1) specify
2) Call the set_attribute process in the DBMS_Scheduler package
Q3: "What permissions do I need to create a job "?
It can create jobs with the owner as any user (except sys users.
By default, a job is created in the current schema and is not activated.
The enabled attribute needs to be explicitly set to true. Let's look at an example:
Begin
Dbms_scheduler.create_job
(
Job_name => 'arc _ move ',
Schedule_name => 'every _ 60_mins ',
Job_type => 'executable ',
Job_action => '/home/dbtools/move_arcs.sh ',
Enabled => true,
Comments => 'move archived logs to a different directory'
);
End;
/
Q4: Can you elaborate on the parameters used in the above process?
A4:
Job_name: As the name implies, each job must have a name.
Schedule_name: If a scheduler is defined, specify the scheduler name here.
Job_type: Currently, three types are supported:
PL/SQL block: plsql_block,
Stored Procedure: stored_procedure
External program: executable (an external program can be a shell script or an operating system-level command ).
Job_action: job_action has different meanings depending on job_type.
If job_type specifies a stored procedure, you must specify the name of the stored procedure;
If job_type specifies PL/SQL blocks, you must enter the complete PL/SQL code;
If the external program specified by job_type is used, enter the script name or the operating system command name.
Enabled: As mentioned above, it specifies whether the job is automatically activated after creation.
Comments: a brief description of the job
2. Specify the execution frequency of the job
If we create a job and want it to run according to the specified date and time, we need to define the repetition frequency of the job. for example, run every day, run at on every Sunday, run on every Monday, Wednesday, and run on the last Sunday of each year.
(Note: In versions earlier than 10 Gb, the interaction with the operating system is not very good. For example, to implement a regular RMAN backup task, you must use the OS command to implement it. You can use crontab in UNIX and at in windows)
10 Gb has been greatly enhanced in this regard, because the operating system commands or scripts can be directly specified during job creation, and then the execution frequency of the job can be reasonably defined, complex scheduling tasks can be easily completed.
10g supports two repeat_interval modes, one is the PL/SQL expression, which is also used in the dbms_job package, such as sysdate + 1, sysdate + 30/24*60; the other is the calendar expression.
For example, mon indicates Monday, Sun indicates Sunday, Day indicates everyday, week indicates weekly, and so on. Here are several examples using calendar expressions:
Repeat_interval => 'freq = hourly; interval = 2'
Run a job every two hours.
Repeat_interval => 'freq = daily'
Run a job once a day
Repeat_interval => 'freq = weekly; byday = Mon, wed, Fri"
1, 3, and 5 jobs per week
Repeat_interval => 'freq = yearly; bymonth = Mar, Jun, SEP, Dec; bymonthday = 30'
Run the job on the 30th, every year.
People who have used crontab should feel familiar with each other.
The following describes the rules for using calendar expressions:
The calendar expression is basically divided into three parts: The first part is the frequency, that is, the keyword "freq", which must be specified; the second part is the time interval, that is, the keyword "interval, value Range: 1-999. it is an optional parameter. The last part is an additional parameter that can be used to specify the exact date and time. It is also an optional parameter. For example, the following values are valid:
Bymonth, byweekno, byyearday, bymonthday, byday
Byhour, byminute, bysecond
For detailed parameter descriptions, see DBMS_Scheduler instructions.
Now that repeat_interval is mentioned, you may ask: "Is there a simple way to get it, or evaluate the running time of a job and the next running time? "
The DBMS_Scheduler package provides a evaluate_calendar_string process, which can easily meet this requirement. See the following example:
SQL> set serveroutput on size 999999
SQL> declare
Rochelle start_date timestamp;
Rochelle next_date timestamp;
Rochelle return_date timestamp;
Begin
Rochelle start_date: = trunc (systimestamp );
Rochelle return_date: = l_start_date;
For CTR in 1 .. 10 Loop
Dbms_scheduler.evaluate_calendar_string (
'Freq = daily; byday = Mon, Tue, wed, Thu, Fri; byhour = 7,15 ',
Rochelle start_date, Rochelle return_date, Rochelle next_date
);
Dbms_output.put_line ('Next run on: '|
To_char (l_next_date, 'Mm/DD/YYYY hh24: MI: ss ')
);
Rochelle return_date: = l_next_date;
End loop;
End;
/
The output result is as follows:
Next run on: 03/22/2004 07:00:00
Next run on: 03/22/2004 15:00:00
Next run on: 03/23/2004 07:00:00
Next run on: 03/23/2004 15:00:00
Next run on: 03/24/2004 07:00:00
Next run on: 03/24/2004 15:00:00
Next run on: 03/25/2004 07:00:00
Next run on: 03/25/2004 15:00:00
Next run on: 03/26/2004 07:00:00
Next run on: 03/26/2004 15:00:00

To support jobs, you can only use SQL command lines to manage traditional jobs.

Job Scheduling from Oracle 10g with DBMS_Scheduler
In Oracle 10g the dbms_job package is replaced by the DBMS_Scheduler package. the dbms_job package is now depricated and in Oracle 10g it's only provided for backward compatibility. from Oracle 10g the dbms_job package shocould not be used any more, because is cocould not exist in a future version of oracle.

With DBMS_Scheduler Oracle procedures and functions can be executed. Also binary and shell-scripts can be scheduled.

Rights
If you have DBA rights you can do all the scheduling. for administering job scheduling you need the priviliges belonging to the scheduler_admin role. to create and run jobs in your own schedule you need the 'create job' privilege.

With dbms_job you needed to set an initialization parameter to start a job Coordinator background process. With Oracle 10g DBMS_Scheduler this is not needed any more.

If you want to user resource plans and/or consumer groups you need to set a system parameter:
Alter system set resource_limit = true;
Getting started quickly
To quickly get a job running, you can use code like this:

Begin
Dbms_scheduler.create_job (
Job_name => 'demo _ job_schedule'
, Job_type => 'plsql _ Block'
, Job_action => 'in in package. Procedure (''param _ value'); end ;'
, Start_date => '2014/1/0 am'
, Repeat_interval => 'freq = daily'
, Enabled => true
, Comments => 'demo for Job Schedule .');
End;
/
This schedules a PL/SQL block to be executed daily starting 1/1/2006 0:00 AM.
You can schedule things like this, but DBMS_Scheduler can reuse components.

You can build a schedule using components like program, schedule, job, job class and window. We will now discuss these components in detail.

Program
The program component represents Program-code that can be executed. This program code can have parameters. code example

Begin
Dbms_scheduler.create_program (
Program_name => 'demo _ job_schedule'
, Program_type => 'stored _ procedure'
, Program_action => 'package. Procedure'
, Number_of_arguments => 1
, Enabled => false
, Comments => 'demo for Job Schedule .');

Dbms_scheduler.define_program_argument (
Program_name => 'demo _ job_schedule'
, Argument_position => 1
, Argument_name => 'kol1'
, Argument_type => 'varchar2'
, Default_value => 'default'
);
Dbms_scheduler.enable (name => 'demo _ job_schedule ');
End;
/
The parameter program_type can have one of the following values: 'plsql _ Block', 'stored _ procedure ', 'executable '.
DBMS_Scheduler also allows to execute shell scripts (Windows: *. BAT files) and executables.

Schedule
A schedule defines the frequence and date/time specifics of the START-time for the job.
Example code

Begin
Dbms_scheduler.create_schedule (
Schedule_name => 'demo _ schedule'
, Start_date => '2014/1/0 22:00:00'
, Repeat_interval => 'freq = weekly'
, Comments => 'Weekly ');
End;
/

To drop the schedule:
Begin
Dbms_scheduler.drop_schedule (
Schedule_name => 'demo _ schedule'
, Force => true );
End;
/
Calendar expresions can have one of these values: 'early ', 'monthly', 'Weekly ', 'daily', 'hourly ', 'minutely', 'secondely'
Job
A job defines when a specific task will be started. this can be done by assigning a program to one or more schedules (or to a specific date/time ). A job can belong to only 1 job class. code example

Begin
Dbms_scheduler.create_job (
Job_name => 'demo _ job1'
, Program_name => 'demo _ job_schedule'
, Schedule_name => 'demo _ schedule'
, Enabled => false
, Comments => 'run demo program every week at 22:00 ');

Dbms_scheduler.set_job_argument_value (
Job_name => 'demo _ job1'
, Argument_position => 1
, Argument_value => 'param1 ');

Dbms_scheduler.enable ('demo _ job1 ');

Commit;
End;
/

Or start shell script

Begin
Dbms_scheduler.create_job
(
Job_name => 'run _ shell1 ',
Schedule_name => 'demo _ schedule ',
Job_type => 'executable ',
Job_action => '/home/test/run_script.sh ',
Enabled => true,
Comments => 'run shell-script'
);
End;
/
Monitoring job-Scheduling
Jobs can be monitored using Oracle Enterprise Manager 10G. It's also possible to use a number of views that have been created in Oracle 10g. We will discuss some of these views here.

To Show Details on job run:
Select log_date
, Job_name
, Status
, Req_start_date
, Actual_start_date
, Run_duration
From dba_scheduler_job_run_details

To show running jobs:
Select job_name
, Session_id
, Running_instance
, Elapsed_time
, Cpu_used
From dba_scheduler_running_jobs;

To show job history:
Select log_date
, Job_name
, Status
From dba_scheduler_job_log;

Show all schedules:
Select schedule_name, schedule_type, start_date, repeat_interval
From dba_scheduler_schedules;

Show all jobs and their attributes:
Select *
From dba_scheduler_jobs

Show all program-objects and their attributes
Select *
From dba_scheduler_programs;

Show all program-arguments:
Select *
From dba_scheduler_program_args;