oracle-timed Task-job

The 70057427?locationnum=6&fps=oracle job has a timed execution function that can perform its own tasks at a specified point in time or at a point in time each day. first, query the system job, you can query the view

--Related views Select*From Dba_jobs;select*From All_jobs;select*From User_jobs;--query Field Description/*
field (column) type description The job number task's unique identifier Log_user VARCHAR2 (30) the user who submitted the task Priv_ 　　　　　　 User VARCHAR2 (30) User Schema_user VARCHAR2 (30) To assign task permissions Last_date DATE for the task's parsing The last time the task was successfully run Last_sec VARCHAR2 (8) hours, minutes and seconds this_date date in HH24:MM:SS format last_date 　　　　　　 The start time of the line task, if no task is run, is nullthis_sec VARCHAR2 (8) hours, minutes, and seconds of this_date date in HH24:MM:SS format next_date date The time of the next scheduled run of the task Next_sec VARCHAR2 (8) hours, minutes, and seconds total_time number of next_date dates in HH24:MM:SS format The total time required to run, in seconds Broken VARCHAR2 (1) flag parameter, y indicates the task is interrupted and will not run later interval VARCHAR2 (200) expression to calculate the next run time FA Ilures number task runs without successive successes what VARCHAR2 (2000) PL/SQL block to perform a task Current_session_labelraw M 　　Lslabel the task's trusted Oracle session break Clearance_hi Raw MLSLABEL The task can trust the Oracle maximum Gap Clearance_lo Raw MLSLABEL Oracle minimum gap nls_env that the task can trust 　　　　　　　　　　　   VARCHAR2 (2000) Task run NLS session Settings Misc_env RAW (32) task run some other session parameters */--running Jobselect*from dba_jobs_running; The most important field is the job. The value is the ID number of the operation job, what is the name of the operation stored procedure, next_date execution time, interval execution interval two, Execution interval interval Run frequency
Describes the interval parameter value every day 12 o'clock Midnight TRUNC (Sysdate+ 1) Daily 8:30 A.M. TRUNC (sysdate+ 1) + (8*60+30)/(24*60) Every Tuesday noon 12 o'clock Next_day (TRUNC (sysdate),' Tuesday ') + 12/24midnight of the first day of each month 12 o'clock TRUNC (Last_day (sysdate)+ 1) The last day of each quarter 11 o'clock TRUNC (add_months (sysdate+ 2/24, 3), ' Q ') -1/24every Saturday and Sunday 6:10 A.M. TRUNC (LEAST (Next_day (Sysdate,"SATURDAY"), Next_day (Sysdate, "SUNDAY")) + (6X60+10)/(24x60)perform secondary Interval per second= = sysdate+ 1/(24 * 60 * 60) If you change to Sysdate+ 10/(24 *60 * 60) is 10 seconds to execute Interval per minute=>trunc (sysdate, ' mi ') + 1/(24*60If you change to TRUNC (sysdate),' mi ') + 10/(24*60is executed every 10 minutes, every day.
Example: Daily 1 o'clock in the morning execution
Interval=>trunc (sysdate) + 1 +1/(24) weekly scheduled execution For example: every Monday 1 o'clock in the morning execution Interval=>trunc (Next_day (sysdate, ' Monday ')) +1/24Monthly Scheduled Execution For example: 1st 1 o'clock in the morning monthly execution Interval=>trunc (Last_day (sysdate)) +1+1/24Quarterly scheduled execution for example the first day of the Quarter 1 o'clock in the morning execution Interval=>trunc (Add_months (sysdate,3), ' Q ') + 1/24every half-yearly scheduled execution
For example: Every year July 1 and January 1 1 o'clock in the morning Interval=>add_months (Trunc (sysdate, ' yyyy '), 6) +1/24Yearly Scheduled execution For example: every January 1 1 o'clock in the morning execution Interval=>add_months (Trunc (sysdate, ' yyyy '), 12) +1/24iii. Create Job method Create job, basic syntax: Declare variable job number;begin sys.dbms_job.submit (Job=: Job, what= ' prc_name; ',--The name of the stored procedure executed next_date= = To_date (' 22-11-201309:09:41 ', ' dd-mm-yyyy hh24:mi:ss '), Interval= ' sysdate+1/86400 '); --86,400 seconds a day, that is, one second to run the prc_name process once commit;end;
Using the Dbms_job.submit method process, this process has five parameters: job, what, next_date, interval, and No_parse.
Dbms_job.submit (Job out binary_ineger,what in Varchar2,next_date in Date,interval in Varchar2,no_parse in Bo Oean:=FALSE) The job parameter is an output parameter, which is returned by the submit () procedure to Binary_ineger, which uniquely identifies a job. Generally define a variable to receive, you can go to User_jobs view Query job value. What parameter is the PL that will be executed/SQL code block, stored procedure name, and so on. The Next_date parameter indicates when the job will run. Interval parameter when this work will be re-executed. The No_parse parameter indicates whether this work should be parsed at the time of submission or execution--true, the default value is False. Indicates that this PL/SQL code should parse the first time it executes, while false indicates that the pl/The SQL code should be parsed immediately. Iv. other job-related stored procedures there are other processes in the Dbms_job package: broken, change, interval, isubmit, next_date, remove, run, submit, User_ Export, what, roughly describe these processes:1, the broken () process updates the status of a submitted work, which is typically used to mark a broken job as not broken.          This process has three parameters: job, broken, and next_date. Procedure broken (Job in Binary_integer, broken inBoolean, next_date in date:=sysdate) The job parameter is the work number, which uniquely identifies the work in the problem. The broken parameter indicates whether this work will be marked as broken--true this work will be marked as broken, and false indicates that the work will be marked as not broken. The Next_date parameter indicates when this work will run again. The default value for this parameter is the current date and time.              If the job fails for some reason, Oracle will retry 16 times, fail to execute successfully, be marked as broken, restart the job with broken status, as in the next two ways; A, execute the job immediately with Dbms_job.run () begin Dbms_job.run (: Job)--the job is the Jobnumber returned when the submit process is submitted or go to Dba_jobs to find the corresponding job number end; B, re-mark broken as false by Dbms_job.broken () begin Dbms_job.broken (: Job,false, next_date) end; 2the Change () procedure is used to alter the settings of the specified job.          This process has four parameters: job, what, next_date, interval.              Procedure change (Job in Binary_integer, what in Varchar2, next_date in date, Interval in varchar2) here, the job parameter is an integer value that uniquely identifies the job. What parameter is a piece of pl that is run by this job/A block of SQL code. The Next_date parameter indicates when this job will be executed. The interval parameter indicates the frequency of a job re-execution. 3, the interval () procedure is used to explicitly set the number of intervals between executions of a job.          This process has two parameters: job, Interval. The procedure interval (job in Binary_integer, interval in varchar2) job parameter identifies a specific job. The interval parameter indicates the frequency of a work re-execution. 4, the Isubmit () process is used to submit a job with a specific job number.       This process has five parameters: job, what, Next_date, interval, no_parse. Procedure Isubmit (Job in Binary_ineger, what in Varchar2, next_date in date , interval in varchar2, no_parse in Booean:=FALSE) The only difference between this process and the submit () procedure is that the job parameter is passed as an in parameter and includes a job number provided by the developer. If the supplied job number is already in use, an error is generated. 5, the next_date () procedure is used to explicitly set the execution time of a job.      This process receives two parameters: Job, next_date. Procedure next_date (Job in Binary_ineger, next_date in date) job identifies an existing job. The Next_date parameter indicates the date and time that this job should be executed. 6, remove () procedure to delete a job that is scheduled to run.  This process takes a parameter: procedure remove (Job Inbinary_ineger); The job parameter uniquely identifies a job the value of this parameter is the value of the job parameter that is returned by the submit () procedure for this work call, and the job that is already running cannot be deleted. 7the Run () procedure is used to execute a specified job immediately. This procedure takes only one parameter: The procedure run Inbinary_ineger job parameter identifies the work that will be performed immediately. 8, using the Submit () process, the job is scheduled properly. 9, the User_export () procedure returns a command that is used to schedule an existing job so that the job can be resubmitted. This program has two parameters: job, My_call. Procedure User_export (Job in Binary_ineger, My_call in out varchar2) job parameter identifies an assigned job. The My_call parameter contains the body that is required to resubmit the job in its current state. 10, what () process promises to reset this running command when the job executes.       This process receives two parameters: job, what. Procedure what the job parameter identifies an existing job in the Binary_ineger, in-out varchar2. What parameter indicates the new PL that will be executed/SQL code. Implemented function: Automatically inserts the current system time into the Getsysdate table every minute. V. Examples/*inserts a time every 10 seconds*/--Create a table CREATE TABLE Tab_time (current_time timestamp);--Creating a stored procedure create or replace procedure Pro_job_print as begin--dbms_output.put_line (' System time: ' | | To_char (sysdate, ' dd-mm-yyyy hh24:mi:ss '));    INSERT into tab_time values (sysdate);  End --call the process test begin pro_job_print; End;--select * from dual; --draw the number of seconds in a day--Create job declare JOB1 number; Begin Dbms_job.submit (JOB1,' Pro_job_print; ', sysdate, ' sysdate+10/86400 '); --each 10 inserts a record end;--Related views Select*From Dba_jobs; Select*From All_jobs; Select*fromuser_jobs;--Running Job Select*fromdba_jobs_running;--Run Job begin Dbms_job.run (26); --and select *from User_jobs; the job value in the corresponding, see what the corresponding process end;--query whether to insert data Selectto_char (Current_time,' Dd-mm-yyyy hh24:mi:ss ') Current_time from Tab_time to Current_time;--delete a job begin Dbms_job.remove (26);--and select *Fromuser_jobs, the job value in the corresponding, see what the corresponding process end; Vi. about setting the number of job tasks and controlling concurrency initialization related parameters job_queue_processes alter system setjob_queue_processes= SPFile scope =;//The maximum value cannot exceed;Job_queue_interval= 10;//Schedule Job Refresh frequency in secondsJob_queue_process represents the number of jobs that Oracle can have concurrently, sqlplus can be seen through the statement show parameterjob_queue_process, to see Job_queue_ in Oracle The value of the process. Select*fromv$parameter;select name,description from v$bgprocess; When the job_queue_process value is 0 to stop the Oracle job, you can use the statement alter system Setjob_queue_processes= 10;          To adjust the job that started Oracle. If you set the value of job_queue_processes to 1, it is a serial run, that is, a quick switch to perform a job task. Seven, the job does not run the approximate reason (1), the above explained the job parameters: job-related parameters One is job_queue_processes, this is the number of processes running the job, of course, the system inside the job is larger than this value, there will be queued, the minimum is 0, indicating that the job is not running, The maximum value is 1000, on the OS corresponding to the process snpn,9i after the OS management job is called CJQN. You can use the following SQL to determine the current number of snp/CJQ is running. Select* Fromv$bgprocess, this paddr is not empty snp/The CJQ process is the currently idle process, and some indicate that the process is working. The other one is Job_queue_interval, which ranges from 1--3600, the unit is the second, this is the wake-up job process, because every time the SNP run he rested, need to wake him regularly, this value can not be too small, too small can affect the performance of the database. First determine whether the above two parameters are set correctly, especially the first parameter, set to 0, all jobs will not automatically run. (2), using the following SQL to view the job's broken,last_date and Next_date,last_date is the end time of the most recent job run, Next_date is the next execution time based on the frequency of the setup, Based on this information you can determine whether the job was normal, but also to determine the next time is right, SQL is as follows: Select*from Dba_jobs; sometimes we find that his next_date is January 1, 4000, stating that the job is either in running or the state is a break (broken=y), if the broken value of the job is found to be Y, find the user to understand, determine whether the job can be broken, if not broken, then change the broken value to N, modify and then use the above SQL view to find its last_date has changed, The JOB will work, and the SQL that modifies the broken state is as follows: Begin Dbms_job. Broken (<JOB_ID>, FALSE); end; (3), use the following SQL query to see if the job is still runningselect*fromdba_jobs_running; If you find that the job has run for a long time and it's not over, look for the reason. General jobrunning Lock Related resources, you can view the v$access and v$locked_object the two view. If another process is found to have locked the job-related object, including the package/function/procedure/table and other resources, then it is necessary to delete the other processes, the need to remove the job process, and then re-execute to see the results. (4), if it is normal, but the job is not run, how to do? Then we have to consider restarting the job process to prevent the SNP process from dying and causing the job not to run, instructions are as follows: Alter system setjob_queue_processes= 0; --Shut down the job process and wait for 5--10 seconds alter system setjob_quene_processes= 5; --Restore the original value (5), Oracle bug:oracle9i There is a bug, when the counter to 497 days, just to reach its maximum value, then the count will become-1, the continuation count becomes 0, and the counter will no longer run. If this is the case, you will have to restart the database, but the other Oracle7345 and oracle8i databases do not see the problem. (6), the database on the check basically this much, if the job run still have problems, that need to see whether the program itself, such as a large amount of data processing, or slow network speed and other causes of the operation is too long, it needs specific analysis of specific circumstances. We can do the job manually through the following SQL to see: Begin Dbms_job.run (<job>_id) End; If you find that job execution is not normal, you need to combine the program to analyze the specific.　　Job Lock processing Method: Find out the job number and its session number you are executing SELECT sid,job from dba_jobs_running; Stop execution of the job SELECT sid,serial# from v$session WHERE SID= ' &sid '; ALTER SYSTEM KILL SESSION' &sid,&serial '; EXEC Dbms_job. Broken (&job,true); Example Analysis:1, the query is running the job, through the query there are two, and the process is more occupied by two Oracle process compliant. SQL>SELECT sid,job from dba_jobs_running; SID JOB--------------------12 116 16 117 2, query the information for the running job SQL> SELECT sid,serial# from v$session WHERE sid= ' 12 '; SID serial#--------------------12 4SQL> SELECT sid,serial# from v$session WHERE sid= ' 16 '; SID serial#---------------------16 1 3, using the job information that was queried to end the job with SQL> ALTER SYSTEM KILL SESSION ' 12,4 ';　　System altered. SQL> ALTER SYSTEM KILL SESSION ' 16,1 ';　　System altered. 4, you can set the job to broken if you do not want to run the job described above. EXEC Dbms_job. Broken (116, TRUE); EXEC Dbms_job. Broken (117, TRUE); Based on personal experience, this approach does not immediately disrupt the operation of the job. It is best to find the job corresponding thread to kill. Delete Job Method: Note: The broken value is deactivated if True, False is enabled, and false by default. Begin Dbms_job. Broken (Job number, TRUE); Dbms_job.remove (job number); Commit;end;

oracle-timed Task-job