Scheduled operations using Oracle command lines

Oracle command line provides many functions. The following describes how to implement scheduled operations through Oracle command line. If you are interested in this, take a look.

As we all know, generally, the operating system provides a method to regularly execute tasks. For example, the Unix platform provides the Crontab command for the system to periodically execute tasks. However, for some requirements, such as database table operations, the most typical is the settlement after the stock exchange closes every day. It involves a large number of database table operations, if you still use the operating system for scheduled execution, not only requires a lot of programming work, but also causes user inconsistency and other running errors, and even causes program execution to fail.

I. analyze problems

In fact, for the above requirements, we can use the function Job Queue task Queue manager of the database itself) to implement. The task queue manager allows you to schedule and schedule a task in advance so that it can be automatically executed once or multiple times at a specified time point or period. because the task is executed in the database, therefore, the execution efficiency is very high.

The task queue manager allows us to customize the task execution time and provides a flexible processing method. You can also configure and Schedule Tasks to be executed within a time period with a low volume of user traffic, this greatly improves work efficiency. For example, for routine backup, update, deletion, and replication of databases that are time-consuming and repetitive, we can use the task queue manager for automatic execution to reduce the workload.

Currently, there are many databases with this function, most representative of which are SQL Server 7.0 and Oracle 8.0.5. However, to make the task queue manager work, we need to configure it. The function configuration of SQL Server is implemented in a GUI. With the OEM client management tool, Oracle configuration can also be completed in a graphical interface. However, most users prefer the command line method to manipulate databases. This article describes how to implement this configuration through the command line.

II. Implementation steps

1. Make sure that the Oracle working mode allows start of the task queue manager

Oracle regularly executes the "Job Queue" background program as a SNP process. To start the SNP process, first ensure that the whole system mode can start the SNP process, the following command must be executed as a DBA:

Svrmgrl> alter system enable restricted session;
Or SQL> alter system disenable restricted session;

Use the above command to change the system session mode to disenable restricted, which creates conditions for the initiation of SNP.

2. Make sure that the startup parameters of the task queue manager have been configured in the Oracle system.

The launch parameter of SNP is located in the Oracle initialization file, which is placed in the $ ORACLE_HOME/dbs path. If the Oracle SID is myora8, the initialization file is initmyora8.ora, the description of the SNP startup parameters in the file is as follows:

Job_queue_process = n
Job_queue_interval = N

The first line defines the number of initiation of the SNP process as n. The default value is 0, and the normal definition range is 0 ~ 36. You can configure different values based on the number of tasks.

The second line defines the system to wake up the process every N seconds. The default value is 60 seconds. The normal range is 1 ~ 3600 seconds. In fact, the process enters the sleep state after the current task is executed. After a period of sleep, the general control of the system will wake it up.

If the file does not contain the above two lines, add them according to the above configuration. After the configuration is complete, you need to restart the database to make it take effect. Note: If the task requires a short execution interval, the configuration of N should be smaller accordingly.

3. Add the task to the task queue of the database.

Call the stored procedure in the dbms_job package of Oracle to add the task to the task queue:

Dbms_job.submit (job out binary_integer,
What in archar2,
Next_date in date,
Interval in varchar2,
No_parse in boolean)

Where:

● Job: output variable, which is the number of the task in the task queue;

● What: the name of the task to be executed and its input parameters;

● Next_date: the time when the task is executed;

● Interval: The interval between tasks.

The interval parameter in dbms_job.submit is discussed in detail below. Strictly speaking, interval refers to the interval from the last execution to the next execution. When interval is set to null, the job is deleted from the queue after the execution is completed. If we need to periodically execute the job, we need to use 'sysdate + M.

Before adding a task to the task queue, determine the database user who executes the task. If the user is scott, make sure that the user has the permission to run the dbms_job package. If not, grant permissions to scott as a DBA:

Svrmgrl> grant execute on dbms_job to scott;

4. Write the tasks to be executed as stored procedures or pl/SQL program segments that can be executed by other databases

For example, we have created a stored procedure named my_job. Log On As scott in SQL/plus and run the following command:

SQL> variable n number;
SQL> begin
Dbms_job.submit (: n'my _ job; ', sysdate,
'Sysdate + 100 ');
Commit;
End;
/

The system prompts that the execution is successful.

SQL> print: n;

The system prints the ID of the task. For example, the result is 300.

As shown above, we have created a task with the task number 300 executed every 4 minutes. You can use the data dictionary user_jobs provided by Oracle to view the execution of this task:

SQL> select job, next_date, next_sec, failures, broken from user_jobs;

The execution result is as follows:

Job next_date next_sec failures broken
300 2000/10/10 11:45:15 0 N

This indicates that the task with the task number 300 will be executed at 11:45:15 next time, and the execution failure record of this task is 0 times. Note: When an error occurs during job execution, Oracle records it in the log. The number of failures is automatically increased by 1 each time. When the number of failed tasks reaches 16, Oracle marks the job as a broken. Since then, Oracle will not continue to execute it until dbms_job.broken is re-set to not broken during the User call process, or forcibly call dbms_job.run to re-execute it.

In addition to the submit stored procedure discussed above, Oracle also provides many other stored procedures to operate tasks. Example: dbms_job.change, dbms_job.what, dbms_job.interval
 

Syntax for creating views in Oracle

Create tables and indexes in Oracle

Implementation of oracle re-Indexing

Complete Oracle File System Parsing

How to add tablespaces in ORACLE