PL/SQL for Oracle Database Task Scheduling

The ORACLE tutorial is PL/SQL to schedule Oracle database tasks. Abstract: This article focuses on database recovery and system task scheduling, and puts forward more practical and novel solutions based on the general experience of database background processing, broaden the idea of database background development.

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

This often happens in database operations. Due to temporary negligence, some important data is deleted or mistakenly modified. In addition, some important tasks need to be run periodically. Obviously, the first type of problems mainly involve data backup and recovery, while the latter is mainly system task scheduling. This article provides a solution for these two problems from the perspective of application development.

I. Technical Basis

As this article uses PL/SQL as a development platform to provide solutions, first of all, learn the relevant background knowledge.

PL/SQL itself is only a supplement to SQL statements. It introduces a procedural concept to enhance the database processing capability. However, compared with C, C ++, JAVA and other procedural languages, PL/SQL processing functions are still not powerful enough. Therefore, Oracle database provides a large number of Application Development kits to enhance application development capabilities. Based on the topic of this article, we will introduce the following two Development Kits: DBMS_FLASHBACK and DBMS_JOB.

1. The DBMS_FLASHBACK package is mainly used for reverse query, that is, the query results at this time point are determined by setting the query time. In general, we usually use the query to query data at the current time (sysdate. You can use the DBMS_FLASHBACK package to query the status of previous data. This function is extremely important for troubleshooting. The following are two main functions in the package:

· Enable and disable: Enable and disable the reverse query function. It should be noted that the flashback mode should be disabled before each flashback query is started.

· Enable _ at_time: Set the query time point, which is set starting from the current time.

2. The DBMS_JOB package is a utility package used to schedule PL/SQL blocks. It allows PL/SQL blocks to run automatically at a specified time, similar to a timer such as Settimer in VC. To facilitate the running of this package, you must first set two init. ora parameters:

· JOB _ QUEUE_PROCESS specifies the number of backend processes started. If it is 0 or not set, there will be no background processing to enter the job, and they will not run.

· JOB _ QUEUE_INTERVAL, in seconds, specifies the waiting time for each process before checking the new JOB. During the time specified by JOB_QUEUE_INTERVA, a job can only run once at most.

After these two parameters are set, you can schedule the program. This package mainly uses the SUBMIT function for scheduling. The prototype of this function is:

Submit (returned job number, program process name, sysdate, next running time );

Ii. Data Recovery

Data Recovery is an extremely important function of the database. Usually important data can be achieved through the data backup function of its system. Therefore, in actual development, important data is often easy to recover, on the contrary, some common data may cause some troubles due to misoperations.

Experienced developers often back up the base tables required by the Development (the base table is the data table that provides the data source. In this way, even some data misoperations will not cause major accidents.

The more practical and rarely used method by developers is to use reverse query. Given that some technical preparations have been made before, you can use the DBMS_FLASHBACK package to restore data. For ease of description, assume that a base table emp_table has the following table records:

That is to say, this table has only three records, so the first record is deleted due to database misoperations. Then, execute the following SQL statement:

The execution result is:

Because the COMMIT operation has been executed, ROLLBACK cannot be performed, so the original data cannot be restored using the normal method. However, due to misoperations not long ago (assumed 5 minutes ago), you can use the DBMS_FLASHBACK package to restore data. You can enter the following code in SQL * PLUS:

Execute dbms_flashback.enable_at_time (sysdate-5/1440 );

At this time, the database is changed to the status 5 minutes ago. If you run the query table command again, the result is as follows:

In this case, you can back up the data to emp_table_bk, that is:

In this way, the data from previous misoperations will be restored.

As shown in the preceding results, the ENABLE_AT_TIME function of the DBMS_FLASHBACK package can be called to adjust the current query time of the database to the previous one, which provides help for data recovery.

Pay attention to the following points when using the DBMS_FLASHBACK package:

· Flashback query is a prerequisite, that is, the database must have the Undo management function. Specifically, DBA should establish an undo tablespace, enable automatic undo management, and establish a undo retained time window. In this way, Oracle maintains sufficient undo information in the Undo tablespace to support reverse queries within the retention period.

· The undo tablespace size directly determines the success or failure of reverse queries. That is to say, the larger the tablespace to be withdrawn, the earlier the query time can be. For the general tablespace size to be withdrawn, to ensure that the reverse query is successful, try to query data within five days, this is more likely to succeed.

Iii. Task Scheduling

In UNIX systems, a task is equivalent to a process, that is, when the system executes a program code, it will automatically assign a process number and a task number to it, in this way, you can use the process number and task number to operate the task (such as suspending, stopping, and starting ). Task Scheduling also exists in the Oracle database. For example, a certain operation must be executed periodically or only when an event occurs. The general practice is to use a trigger to encapsulate all operations in the trigger, and then wait for the operation to be executed by specifying the trigger event. In addition, the operating system can be directly used for implementation. For example, you can compile Windows scripts on the Windows platform and implement them in combination with the "Task Plan, you can write a Shell to execute tasks cyclically.

Here we mainly use the DBMS_JOB package of the ORACLE database.

For example, because employees need to be evaluated every month for salary adjustment, the emp_table table needs to be updated. The update code is as follows:

To regularly run the above program every month, you can execute the following code:

After submit is executed, the salary_upt process will be executed immediately. In the code above, v_jobNum is the job number (Task Number) returned by the job, and the last two times are the start time and end time, so the salary_upt process will execute the salary_upt program every 30 days, in this way, regular updates are achieved.

To disable the execution of the job, run the following command:

You can use the DBMS_JOB package to schedule tasks to facilitate integration with applications. Sometimes this process is more convenient.

Iv. Summary

In many cases, database functions can be extended through applications. For users who develop background databases, they are not familiar with the overall database architecture, it is necessary to master certain application development capabilities. The system will be inspired by this article.

The development environment in this article is:

Server: UNIX + ORACLE9.2

Client: WINDOWS2000 PRO + TOAD (or SQL * PLUS)

The code in this article has been debugged in the above environment.