Are watching the ORACLE tutorial is: PL/SQL implementation ORACLE database task scheduling. Abstract: this article mainly database recovery and system task scheduler, in combination with general database background processing experience, put forward a practical and innovative solutions, to broaden the database backend development train of thought.

key words: data recovery, task scheduling, ORACLE, PL/SQL

in database operations there are always such a case, due to the negligence of a mistakenly deleted or changed by mistake some important data, there are some important tasks need to be run periodically. Obviously, the first class of problems is primarily data backup and recovery, and then type mainly is the task scheduling system. This article will focus on these two types of problems, from the perspective of application development is given a solution.

a. Technology of

because this article is to use PL/SQL as a development platform to provide a solution, so first of all know related background knowledge.

PL/SQL itself is only as a supplement of the SQL statement, by introducing the concept of procedural to enhance database processing ability. However, compared with C, C + +, procedural languages such as JAVA, PL/SQL function is still not strong enough. To this end, the Oracle database development kit offers a wide range of applications, to enhance ability of application development. According to the topic of this article, introduces two development kit is as follows: DBMS_FLASHBACK and DBMS_JOB.

1. DBMS_FLASHBACK package is primarily used to carry out a flashback queries, namely by setting the query time to determine the time of the query results. In general, we usually use the current query is a query time (sysdate) data. Use DBMS_FLASHBACK package can query the state of the data before, this function for error handling situation becomes extremely important. Below is the package of the two main function introduction:

the Enable and disable: respectively is the startup and shutdown flashback queries. It should be noted that, every time start flashback queries before should first shutdown mode of flashback.

Enable_at_time: set of the query point in time, it was based on the current time as a starting point for setting.

2. The DBMS_JOB package is used to schedule a PL/SQL block utility package, it allows the PL/SQL block to run automatically, within a specified time is similar to the Settimer VC this timer. To facilitate the operation of the package, need to first set up two init. Ora parameters:

· JOB_QUEUE_PROCESS specify the starting background processing. If it is zero or is not set, there will be no background processing into the job, they wouldn't run.

JOB_QUEUE_INTERVAL, in seconds, to specify each process waiting time before check the new assignment. JOB_QUEUE_INTERVA in the specified period of time, a job can only be run once at most.

after these two parameters have been set, can the right procedure for scheduling, scheduling, the package is mainly use the SUBMIT function the function prototypes for:

SUBMIT (returned assignments, program process, sysdate, next time you run time);

2. Data recovery

data recovery is an extremely important function of the database itself, usually important data can be done by the data backup function of the system, so in the actual development, important data are often easy to recover, but is some common data because of wrong operation and cause some trouble.

for experienced developers tend to the development need of base table (base table is to provide the data source of the data table) do some backup. Even if later some data wrong operation will not lead to major accidents.

more practical and less for the method is used by developers using flashback queries, in view of the front has a certain technology, you can now use DBMS_FLASHBACK package to restore data. About to go to the lavatory, assume that a base table emp_table, its table record is as follows:

that is only 3 records in this table, then due to the wrong operation of the database, lead to the first record to be deleted, then execute the following SQL statement:

the execution results for:

having carried out the COMMIT operation (COMMIT), so could not be rolled back (ROLLBACK), so that the original data will not be able to recover in normal way. However, due to wrong operation in not long ago when (assuming it is 5 minutes ago), in this case, you can use DBMS_FLASHBACK package to restore data, can enter the following code in the SQL * PLUS:

the execute DBMS_FLASHBACK. Enable_at_time (sysdate - 5/1440);

at this point, the database is adjusted to 5 minutes before, if you follow the orders of the lookup table will be for the following results:

you can at this point the data backup to emp_table_bk, namely:

in this way, the previous data to restore back the wrong operation.

from the above results out, call package DBMS_FLASHBACK ENABLE_AT_TIME function, can adjust the current query time of the database to the past, it provides help for data recovery.

when using DBMS_FLASHBACK package should also pay attention to the following:

flashback queries is a premise, that is, the database must have undo management functions. Particular way is, the DBA should establish an undo tablespace, and start the automatic undo management, and establish a undo retention time window. Thus, Oracle will maintain sufficient undo information in the undo tablespace in order to support retention time flashback queries.

due to the size of the undo tablespace, directly determine the success or failure of flashback query execution. The undo table space is larger, you can query the time can be earlier, so for the average of the size of the undo table space, in order to assure the success of flashback query, query within five days of data as far as possible, so that the likelihood of success is higher.

3. Task scheduling

on UNIX systems, tasks and processes is the concept of equivalent, namely when the system performs a program code automatically assign it a process and task, so using processes, and tasks, can for the task to operate (such as hanging, stop, start, etc.). And within the Oracle database also exist in task scheduling, for example, you need to periodically perform an operation, or is executed when a certain event occurs. General practice is to use the trigger, the packages all operations in the trigger, then by specifying a triggering event will wait this operation is carried out. In addition, you can directly use the operating system to implement, such as the script, you can write the Windows and the Windows platform combined with "task plan" to implement; If on a Unix platform, you can write Shell periodically perform operations to achieve the task.

here are mainly using DBMS_JOB package of ORACLE database.

for example, due to need each month for examination and assessment of the employees for salary adjustment, then you need to update the emp_table table. Update handling code is as follows:

to running the above procedure every month regularly, you can perform the following code:

submit will allow salary_upt is performed immediately after execution. In the code above, v_jobNum is this homework returned assignments (tasks), behind two time for the start time and end time, so salary_upt process will be executed once every 30 days salary_upt procedures, in order to achieve the purpose of the regular updates.

if you want to ban continue to perform the job, you can perform the following command:

using DBMS_JOB package to realize task scheduling facilitate with application integration, sometimes this kind of treatment is more convenient. Nodule


most of the time, the function of the database can be extend through the application, database development for the background operation of the user, in addition to the familiar with architecture of database as a whole, to master certain application development ability is necessary. System in this article can give readers some inspiration.

in this paper, the development environment for:

server: UNIX + ORACLE9.2

client: Windows PRO + TOAD (or SQL * PLUS)

the code in this article on the environment has been debugging through.

This concludes the body part