The Oracle tutorial being looked at is: PL/SQL implementation of Oracle database task scheduling. Absrtact: This article mainly on the database recovery and system task scheduling, combined with the general database background processing experience, put forward a more practical and innovative solutions, broaden the background of the development of the database of ideas.
Keywords: data recovery, task scheduling, Oracle,pl/sql
In the database operation is often the case occurs, due to a momentary negligence and mistakenly deleted or mistakenly changed some important data, in addition to some important tasks need to run periodically. Obviously, the first kind of problem is mainly data backup and recovery, the latter is mainly the task scheduling of the system. In this paper, we will give a solution to these two types of problems from the perspective of application development.
A Technical Basics
Since this article uses Pl/sql as a development platform to provide solutions, first understand the relevant background knowledge.
The pl/sql itself is only a supplement to the SQL statement, which enhances the database processing capability by introducing the concept of the process. However, compared to C,c++,java and other process languages, Pl/sql's processing function is still not strong enough. To this end, Oracle databases provide a large number of application development packages to enhance application development capabilities. According to the subject of this article, the following two development packages are described: Dbms_flashback and dbms_job.
1. The Dbms_flashback package is used primarily for flashback queries, that is, by setting the query time to determine the results of the query at that moment. In general, the query we use normally is to query the current time (sysdate) data. Using the Dbms_flashback package allows you to query the state of previous data, which is extremely important for situations where the error is handled. The following is a description of the two main functions in the package:
· Enable and disable: Start and turn off the Flashback query function. It should be noted that flashback mode should be turned off every time a flashback query is started.
· Enable_at_time: Sets the time point for the query, which is set at the current time as the starting point.
2. The Dbms_job package is a utility package that is used to schedule the Pl/sql block, allowing the Pl/sql block to run automatically within a specified time, similar to a timer such as SetTimer in VC. To facilitate the operation of the package, you need to first set up two Init.ora parameters:
· JOB_QUEUE_PROCESS Specifies the number of background processes that are 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 specifies, in seconds, how long each process waits before checking for a new job. Within the time specified by Job_queue_interva, a job can only run at most once.
Set these two parameters, you can schedule the program, the package is mainly using the Submit function for scheduling, the function of the prototype:
Submit (Return job number, program process name, Sysdate, Next run time);
Two Data recovery
Data recovery is a very important function of the database itself, usually important data can be realized by the data backup function of its system, so in the actual development, the important data is easy to recover, but some ordinary data is caused some trouble because of the mistake operation.
For experienced developers, there is often a backup of the base tables that are needed for development (the base table is the data table that provides the data source). In this way, even after some data misoperation will not cause major accidents.
More practical and rarely used by developers is the use of flashbacks query, given that there is a certain technical groundwork, now can be used Dbms_flashback package to restore the data. For ease of narration, suppose a base table emp_table, whose table records are as follows:
That is, this table has only 3 records, so because of the misuse of the database, causing the first record to be deleted, execute the following SQL statement:
The results of the execution are:
The rollback (ROLLBACK) cannot be done because the commit operation has been performed, so that the original data cannot be recovered in the normal way. However, since the time of the misoperation was not long ago (assuming it was 5 minutes ago), in this case, you can use the Dbms_flashback package to recover the data, and you can type the following code in Sql*plus:
Execute Dbms_flashback.enable_at_time (sysdate-5/1440);
At this point, the database is adjusted to a state that was 5 minutes ago, and if the command to execute the query table again would be the following:
Then you can back up its data to EMP_TABLE_BK at this time, that is:
In this way, the previous misoperation of the data to restore back.
From the results above, the call to the Enable_at_time function of the Dbms_flashback package can adjust the current query time of the database to the previous, which is helpful for data recovery.
The following points should also be noted when using the Dbms_flashback package:
• Flashback queries are conditional, that is, the database must have an undo management function. The practice is that the DBA should establish an undo tablespace and initiate automatic undo management and establish an undo retention window. In this way, Oracle maintains sufficient undo information in the undo Tablespace to support flashback queries during retention time.
• Because the size of the undo table space directly determines the success or failure of the flashback query execution. That is, the larger the undo table space, then can query the time can be earlier, then for the general undo the size of the table space, in order to ensure the success of the flashback query, as far as possible to query the data within 5 days, so the likelihood of a higher degree of success.
Three Task scheduling
In a UNIX system, the concept of a task is equivalent to a process that is automatically assigned a process number and a task number when the system executes a piece of code, so that the task can be manipulated using the process number and the task number (for example, suspend, stop, start, etc.). There are also tasks within the Oracle database, such as the need for periodic execution of an operation, or the execution of an event when it occurs. The general practice is to use triggers to encapsulate all operations in triggers and then wait for execution by specifying the triggering event. In addition, you can also use the operating system directly, such as the Windows platform can write Windows Script and combined with "task scheduling" to implement, if on the UNIX platform, you can write a shell to achieve the task of periodic execution.
And this is mainly through the Oracle database Dbms_job package to achieve.
For example, you need to update the Emp_table table because you need to evaluate your employees for salary adjustments every month. The update processing code is as follows:
To run the above program periodically every month, you can execute the following code:
Submit execution will enable the Salary_upt process to execute immediately. In the above code, V_jobnum is the job number that the job returns (the task number), followed by the start and end times, so the salary_upt process will perform a salary_upt program every 30 days to achieve the purpose of periodic updates.
If you want to prevent the job from continuing, you can execute the following command:
The use of Dbms_job packages to implement task scheduling is easy to integrate with applications, and is sometimes easier to handle.
Four Summary
Many times, the functions of the database can be extended through the application, for the users of the background database development operations, in addition to the overall database structure is familiar with, the ability to master certain application development is very necessary. The system can give readers some inspiration through this article.
The development environment for this article is:
Server side: unix+oracle9.2
Client: WINDOWS2000 pro+toad (or Sql*plus)
The code in this article has been debugged in the above environment.
