Oracle custom scheduled task execution

This section briefs how to use jobs provided by Oracle to implement scheduled execution of Oracle tasks.

 

1. Introduction

You can customize scheduled tasks at the system and database levels,

At the operating system level,

In Windows, we can use the task plan to implement it,

For the WINXP system, follow these steps: Start --- set --- Control Panel --- task plan, add the task plan, and complete the configuration according to the wizard;

For Windows 7, follow these steps: Click Start and searchProgramAnd enter [Task Scheduler] in the file box, click the scheduler, and create a basic task;

In Linux, we can use the crontab command for implementation,

For how to use the crontab command, see the previous blog (http://www.cnblogs.com/java-pan/archive/2012/07/23/linux.html );

At the database level, we can use database jobs;

This section describes how to customize a simple scheduled task through the Oracle Database Job. This section uses Oracle to customize a regular insert statement to a table.

Here we will talk about the reason for doing this oracle scheduled task:

The project needs to synchronize data from some tables in a database to another database at a fixed time. Obviously, I cannot do this myself every time,

So how can I do it? At this time, Oracle's scheduled task execution job is undoubtedly my best choice. I put the synchronized script in a stored procedure,

Then you can execute the stored procedure at a fixed time.

Note: All of the following operations are performed under the sytem user. Client Login using PL/SQL.

 

2. Project Environment

Install Oracle10g, then install PL/SQL, and then log on to the system as the administrator,

Open the SQL window and enter the SQL statement in the figure. The detailed information about the operating environment is as follows:

 

3. Prepare the environment

(1) create a new table with the command test. The table creation statement is as follows:

Test0916

1 Create TableSystem. test09162 (3SeqNumber(2)Primary Key,4 Seqtime date5)
 

Execute the preceding statements in the SQL window;

(2) create a stored procedure named test_proc. The statement of the stored procedure is as follows:

Test_proc

 1   Create  Or   Replace   Procedure Test_proc Is  2   Begin  3     Insert   Into System. test0916 (SEQ, seqtime) Values (Nvl (( Select   Max (SEQ) From System. test0916) +   1 , 0  ), Sysdate );  4     Commit  ;  5   Exception  6     When Others Then  7 Dbms_output.put_line ( '  Exception happened, data was rollback  '  );  8      Rollback  ;  9   End ;

Run the preceding stored procedure in the SQL window. After the execution is complete, view the procedures menu under objects to view the newly created stored procedure.

The icon in front of test_proc is not marked in red, indicating that the stored procedure is normal;

(3) Check the Oracle job running environment

All I encountered here is to check the number of processes in the Oracle Job Queue. If it is 0, the job is not run,

Although the default value is 10 on the internet, I do not know why it is 0 after installing oracle.

View the number of processes:

Modify the number of processes (If you are 0, you must follow the following statement to modify):

I have changed it to 10 here. If I want to change it to 11, I will simulate the following operations;

In the command window of PL/SQL, enter the following statement:

Modify process count

AlterSystemSetJob_queue_processes=10Scope=Both;
 

Run the following command:

After the modification, let's take a look:

I saw it. I changed it to 11.

Additional instructions:

Some online users say that modifying the number of processes can modify the Oracle initial file. At first, I found the location of the initial file, and then opened it with notepad ++ to modify it,

After saving it, I restarted the Oracle service and computer, but found that when I used PL/SQL to log on to Oracle, I always reported that the specified string could not be recognized. This operation is not feasible here.

Here is a question: how can we find the location of the Oracle initial file? Run the following statement in the Command window of PL/SQL:

I used notepad ++ to modify the shadow content:

*. Job_queue_processes = 11

 

4. Custom scheduled task execution

Suppose we want to insert a record to the table test0916 every two minutes. Here we only need to execute the Stored Procedure test_proc every two minutes.

Enter the following statement in command window of PL/SQL:

Job

 
1 VaRJob_numNumber;2 Begin3Dbms_job.submit (: job_num,'Test_proc;', Sysdate,'Sysdate + 2/24/60');4 End;
 

 

The execution process is as follows:

After the execution is complete, click Submit for PL/SQL;

 

5. View execution results

(1) check whether the newly added job is successful.

You can view what field in the dba_jobs table to see the last record even if the newly created scheduled task is created;

(2) Check whether data is inserted in Table test0916

 

6. other instructions

(1) how to delete a job?

Execute the following statement in command window of PL/SQL:

Exec dbms_job.remove (81 );

Note: The number 81 corresponds to the value of the job field in the row of the job record to be deleted in the dba_jobs table;

 

(2) What do submit parameters mean?

 

When using the submit () process, the work is properly planned.
This process has five parameters: job, what, next_date, interval, and no_parse.
Procedure submit (job out binary_ineger,
What in varchar2,
Next_date in date,
Interval in varchar2,
No_parse in booean: = false)
The job parameter is the binary_ineger returned by the submit () process. This value uniquely identifies a job. There is a double quotation mark that is not displayed in SQL windwo. Copy it to the Command window to display it;
What parameter is the PL/SQL to be executedCodeBlock. Here is the name of the stored procedure, followed by a semicolon in English;
The next_date parameter specifies when the job will be run.
The interval parameter indicates when the job will be re-executed. The plus signs and parentheses must be entered in English;
The no_parse parameter indicates whether the job should perform syntax analysis at the time of submission or execution -- true indicates that the PL/SQL code should perform syntax analysis during its first execution, false indicates that the PL/SQL code should be analyzed immediately.

 

(3) How do I set the scheduled execution interval?

Here you can go to Baidu and search online to find the trunc function that meets your needs.