PL and SQL implements Oracle database task scheduling
- 2020-06-01 11:12:41
- OfStack
The ORACLE tutorial you are looking at is :PL/SQL implements Oracle database task scheduling. Abstract: this paper is mainly on the database recovery and system task scheduling, in combination with the experience of 1 kind of database background processing, put forward a more practical and novel solution, broaden the idea of database background development.
Key words: data recovery, task scheduling, ORACLE, PL/SQL
In the database operation will often have such a situation, due to the negligence of 1 when 1 mistakenly deleted or changed 1 some important data, in addition to 1 some important tasks need to run periodically. Obviously, the first one is mainly about data backup and recovery, while the second one is about task scheduling. This article will provide a solution from the perspective of application development for these two types of problems.
1. Technology base
Since this article USES PL/SQL as the development platform to provide the solution, first get the background.
PL/SQL itself is just a supplement to the SQL statement, enhancing the database processing power by introducing the concept of procedural. However, the processing capabilities of PL/SQL are still not as strong as those of procedural languages such as C,C++ and JAVA. To this end, the Oracle database provides a number of application development packages to enhance application development capabilities. Based on the topic of this article, the following two development packages are introduced: DBMS_FLASHBACK and DBMS_JOB.
1. The DBMS_FLASHBACK package is primarily used for flashback queries by setting the query time to determine the query results at that time. 1 in general, the query we usually use is the data under the current time (sysdate). Using the DBMS_FLASHBACK package, you can query the state of the previous data, which is extremely important in the case of misprocessing. Here are the two main functions in the package:
· Enable and disable: start and stop the flashback query function, respectively. It should be noted that the flashback mode should be turned off before starting a flashback query each time.
· Enable_at_time: sets the time point of the query, which is set from the current time.
2. The DBMS_JOB package is a utility package for scheduling PL/SQL blocks, which allows the PL/SQL blocks to run automatically for a specified time, similar to timers like Settimer in VC. To facilitate the running of the package, two parameters of init.ora need to be set first:
· JOB_QUEUE_PROCESS specifies the number of background processes to start. If it is 0 or not set, there will be no background processing into the job and they will not run.
· JOB_QUEUE_INTERVAL specifies in seconds how long each procedure must wait before checking a new job. A job can run no more than once in the time specified by JOB_QUEUE_INTERVA.
After setting these two parameters, the program can be scheduled. This package mainly USES the SUBMIT function for scheduling. The prototype of this function is:
submit(return job number, procedure name,sysdate, next run time);
2. Data recovery
Data recovery is an extremely important function of the database itself, usually important data can be achieved through the data backup function of the system, so in the actual development, important data are often easy to recover, but 1 some common data because of the wrong operation and cause 1 some trouble.
For experienced developers, it is common to make a backup of the base table (that is, the data table that provides the data source) that is needed for development. In this way, even if some data misoperation occurs later, it will not lead to a major accident.
A more practical and rarely used method for developers is to use a flashback query, and now that you have a certain technical setup, you can use the DBMS_FLASHBACK package to recover the data. For ease of reference, assume a base table emp_table, whose table records are as follows:
In other words, there are only 3 records in this table, so the first record is deleted due to the wrong operation on the database, then execute the following SQL statement:
The implementation results are as follows:
Since the commit operation (COMMIT) has been performed, the rollback (ROLLBACK) cannot be performed, so the original data cannot be recovered in the normal way. However, since the error occurred not too long ago (let's say 5 minutes ago), in this case, you can use the DBMS_FLASHBACK package to recover the data. You can type the following code in SQL*PLUS:
execute dbms_flashback.enable_at_time(sysdate-5/1440);
At this point, adjust the database to the state it was 5 minutes ago. If you execute the query table command again, the result will be as follows:
Then it can back up its data to emp_table_bk at this time, that is:
In this way, the previous error operation of the data to restore back.
As you can see from the above results, the ENABLE_AT_TIME function of the DBMS_FLASHBACK package is called to adjust the current query time of the database back to the previous time, which helps with data recovery.
The following points should also be noted when using the DBMS_FLASHBACK package:
· flashback queries are based on the premise that the database must have undo management capabilities. To do this, DBA should set up a undo tablespace, start automatic undo management, and set up an undo retention time window. Thus, Oracle will maintain enough undo information in undo tablespaces to support flashback queries for the duration of the reservation.
· the size of the undo table space directly determines the success or failure of the flashback query execution. In other words, the larger the undo table space is, the earlier the query time can be. Then, for the size of 1-like undo table space, in order to ensure the success of the flashback query, try to query the data within 5 days, so the probability of success is higher.
3. Task scheduling
In the UNIX system, the concept of task and process is equivalent, that is, when the system executes a piece of program code, it will automatically assign it a process number and a task number, so that the process number and the task number can be used to operate on the task (such as suspend, stop, start, etc.). Task scheduling also exists within the Oracle database, for example, a 1 operation needs to be executed periodically or only when an event occurs. The general approach is to use a trigger, in which all actions are encapsulated, and then wait for execution by specifying the trigger event. In addition, it can also be realized directly by using the operating system. For example, Windows script can be written on Windows platform and implemented in combination with "task plan". If you are on the Unix platform, you can write Shell to implement periodic execution of tasks.
The DBMS_JOB package of the ORACLE database is used to implement the DBMS_JOB package.
For example, the emp_table table needs to be updated because employees are evaluated each month to adjust their salaries. Update processing code is as follows:
To run the above program on a regular monthly basis, execute the following code:
The submit execution causes the salary_upt procedure to execute immediately. In the above code, v_jobNum is the job number (task number) returned by the job, and the next two times are the start time and the end time, respectively, so the salary_upt procedure will execute the salary_upt program once every 30 days, thus achieving the purpose of regular updates.
To disable the continuation of the job, execute the following command:
Scheduling tasks using the DBMS_JOB package is easy to integrate with the application, and sometimes easier to process.
4. summary
In many cases, the function of the database can be extended through the application program, for the background database development operation of the user, in addition to the overall database architecture is familiar with, master 1 set of application development ability is very necessary. The system through this article can give readers 1 definite inspiration.
The development environment of this paper is:
Server: UNIX+ ORACLE9.2
Client: WINDOWS2000 PRO+TOAD (or SQL*PLUS)
The code in this article has been debugged in the above environment.
Key words: data recovery, task scheduling, ORACLE, PL/SQL
In the database operation will often have such a situation, due to the negligence of 1 when 1 mistakenly deleted or changed 1 some important data, in addition to 1 some important tasks need to run periodically. Obviously, the first one is mainly about data backup and recovery, while the second one is about task scheduling. This article will provide a solution from the perspective of application development for these two types of problems.
1. Technology base
Since this article USES PL/SQL as the development platform to provide the solution, first get the background.
PL/SQL itself is just a supplement to the SQL statement, enhancing the database processing power by introducing the concept of procedural. However, the processing capabilities of PL/SQL are still not as strong as those of procedural languages such as C,C++ and JAVA. To this end, the Oracle database provides a number of application development packages to enhance application development capabilities. Based on the topic of this article, the following two development packages are introduced: DBMS_FLASHBACK and DBMS_JOB.
1. The DBMS_FLASHBACK package is primarily used for flashback queries by setting the query time to determine the query results at that time. 1 in general, the query we usually use is the data under the current time (sysdate). Using the DBMS_FLASHBACK package, you can query the state of the previous data, which is extremely important in the case of misprocessing. Here are the two main functions in the package:
· Enable and disable: start and stop the flashback query function, respectively. It should be noted that the flashback mode should be turned off before starting a flashback query each time.
· Enable_at_time: sets the time point of the query, which is set from the current time.
2. The DBMS_JOB package is a utility package for scheduling PL/SQL blocks, which allows the PL/SQL blocks to run automatically for a specified time, similar to timers like Settimer in VC. To facilitate the running of the package, two parameters of init.ora need to be set first:
· JOB_QUEUE_PROCESS specifies the number of background processes to start. If it is 0 or not set, there will be no background processing into the job and they will not run.
· JOB_QUEUE_INTERVAL specifies in seconds how long each procedure must wait before checking a new job. A job can run no more than once in the time specified by JOB_QUEUE_INTERVA.
After setting these two parameters, the program can be scheduled. This package mainly USES the SUBMIT function for scheduling. The prototype of this function is:
submit(return job number, procedure name,sysdate, next run time);
2. Data recovery
Data recovery is an extremely important function of the database itself, usually important data can be achieved through the data backup function of the system, so in the actual development, important data are often easy to recover, but 1 some common data because of the wrong operation and cause 1 some trouble.
For experienced developers, it is common to make a backup of the base table (that is, the data table that provides the data source) that is needed for development. In this way, even if some data misoperation occurs later, it will not lead to a major accident.
A more practical and rarely used method for developers is to use a flashback query, and now that you have a certain technical setup, you can use the DBMS_FLASHBACK package to recover the data. For ease of reference, assume a base table emp_table, whose table records are as follows:
In other words, there are only 3 records in this table, so the first record is deleted due to the wrong operation on the database, then execute the following SQL statement:
The implementation results are as follows:
Since the commit operation (COMMIT) has been performed, the rollback (ROLLBACK) cannot be performed, so the original data cannot be recovered in the normal way. However, since the error occurred not too long ago (let's say 5 minutes ago), in this case, you can use the DBMS_FLASHBACK package to recover the data. You can type the following code in SQL*PLUS:
execute dbms_flashback.enable_at_time(sysdate-5/1440);
At this point, adjust the database to the state it was 5 minutes ago. If you execute the query table command again, the result will be as follows:
Then it can back up its data to emp_table_bk at this time, that is:
In this way, the previous error operation of the data to restore back.
As you can see from the above results, the ENABLE_AT_TIME function of the DBMS_FLASHBACK package is called to adjust the current query time of the database back to the previous time, which helps with data recovery.
The following points should also be noted when using the DBMS_FLASHBACK package:
· flashback queries are based on the premise that the database must have undo management capabilities. To do this, DBA should set up a undo tablespace, start automatic undo management, and set up an undo retention time window. Thus, Oracle will maintain enough undo information in undo tablespaces to support flashback queries for the duration of the reservation.
· the size of the undo table space directly determines the success or failure of the flashback query execution. In other words, the larger the undo table space is, the earlier the query time can be. Then, for the size of 1-like undo table space, in order to ensure the success of the flashback query, try to query the data within 5 days, so the probability of success is higher.
3. Task scheduling
In the UNIX system, the concept of task and process is equivalent, that is, when the system executes a piece of program code, it will automatically assign it a process number and a task number, so that the process number and the task number can be used to operate on the task (such as suspend, stop, start, etc.). Task scheduling also exists within the Oracle database, for example, a 1 operation needs to be executed periodically or only when an event occurs. The general approach is to use a trigger, in which all actions are encapsulated, and then wait for execution by specifying the trigger event. In addition, it can also be realized directly by using the operating system. For example, Windows script can be written on Windows platform and implemented in combination with "task plan". If you are on the Unix platform, you can write Shell to implement periodic execution of tasks.
The DBMS_JOB package of the ORACLE database is used to implement the DBMS_JOB package.
For example, the emp_table table needs to be updated because employees are evaluated each month to adjust their salaries. Update processing code is as follows:
To run the above program on a regular monthly basis, execute the following code:
The submit execution causes the salary_upt procedure to execute immediately. In the above code, v_jobNum is the job number (task number) returned by the job, and the next two times are the start time and the end time, respectively, so the salary_upt procedure will execute the salary_upt program once every 30 days, thus achieving the purpose of regular updates.
To disable the continuation of the job, execute the following command:
Scheduling tasks using the DBMS_JOB package is easy to integrate with the application, and sometimes easier to process.
4. summary
In many cases, the function of the database can be extended through the application program, for the background database development operation of the user, in addition to the overall database architecture is familiar with, master 1 set of application development ability is very necessary. The system through this article can give readers 1 definite inspiration.
The development environment of this paper is:
Server: UNIX+ ORACLE9.2
Client: WINDOWS2000 PRO+TOAD (or SQL*PLUS)
The code in this article has been debugged in the above environment.