Oracle job + Stored Procedure learning notes

Guidance:

Part 1: The following steps fully demonstrate the job creation process:

Part 2: job-related knowledge:

Part 3: Practical Application

We found that the job owner must be used to delete jobs, and sys cannot delete other jobs! (First understand this, and then correct it later. Hey hey ·)

Body:

Part 1: The following steps fully demonstrate the job creation process:

1. Create a pig table with the Date Format

SQL> create table pig (a date );

Table created

SQL> commit;

Commit complete

2. Create a stored procedure. bb inserts data into the pig table.

SQL> create or replace procedure bb

2 begin

3 insert into pig values (sysdate );

4 end;

5/

Procedure created

 

3. Create a job named job2009 to execute the Stored Procedure bb every minute (60 × 24 = 1440.

SQL> variable job2009 number;

SQL> begin

2 dbms_job.submit (: job2009, 'bb; ', sysdate, 'sysdate + 1/1440 ');

3 end;

4/

Note: here the system automatically generates a job id of 41

PL/SQL procedure successfully completed

Job2009

---------

41

4. Run job2009.

SQL> begin

2 dbms_job.run (: job2009 );

3 end;

4/

PL/SQL procedure successfully completed

Job2009

---------

41

5. Delete job2009

SQL> begin

2 dbms_job.remove (: job2009 );

3 end;

4/

6. query jobs-related views

Selectjob, last_date, last_sec, broken, failures, interval, whatfromdba_jobs

Several important fields in dba_jobs

Job: the ID of a job. For example, the above 41

Failures: number of failed job execution times. If the number of failed jobs exceeds 15, the broken column is marked as Y, and the job will not run in the future.

Broken: The default value is N. If it is Y, the job is not executed!

Interval: The interval between job execution.

What: the actual work of the job.

 

Part 2: job-related knowledge:

1, DBA_JOBS

DBA_JOBS

========================================================== ===

Field (column) type description

Unique ID of a JOB NUMBER

LOG_USER VARCHAR2 (30) user who submits the task

PRIV_USER VARCHAR2 (30) user with task Permissions

SCHEMA_USER VARCHAR2 (30) User Mode for Syntactic Analysis of tasks

LAST_DATE DATE the time when the last task was successfully run

LAST_SEC VARCHAR2 (8) such as HH24: MM: hour, minute, and second of the SS format last_date

The start time of the task being run by THIS_DATE. If no task is running, the value is null.

THIS_SEC VARCHAR2 (8) such as HH24: MM: the hour, minute, and second of the this_date date in SS format

NEXT_DATE DATE the next scheduled task running time

NEXT_SEC VARCHAR2 (8) such as HH24: MM: the hour, minute, and second of the SS format next_date

TOTAL_TIME NUMBER the total time required to run the task, in seconds

The BROKEN VARCHAR2 (1) Flag parameter. Y indicates that the task is interrupted and will not run later.

INTERVAL VARCHAR2 (200) is the expression used to calculate the next line time.

Failures number the NUMBER of times the task failed to run continuously

WHAT VARCHAR2 (2000) PL/SQL block for task execution

CURRENT_SESSION_LABEL raw mlslabel trust Oracle session character of the task

CLEARANCE_HI raw mlslabel maximum gap between Oracle databases trusted by this task

CLEARANCE_LO raw mlslabel minimum Oracle gap trusted by this task

NLS session settings for running the NLS_ENV VARCHAR2 (2000) task

Other session parameters of the MISC_ENV RAW (32) task

--------------------------

2. Description of the INTERVAL parameter value

'Trunc (SYSDATE + 1) 'At midnight every day )'

Every morning 08:30 'trunc (SYSDATE + 1) + (8*60 + 30)/(24*60 )'

NEXT _ DAY (TRUNC (SYSDATE), ''tuesday'') + 12/24 at every TUESDAY'

 

'Trunc (LAST_DAY (SYSDATE) + 1) 'at midnight on the first day of each month )'

'Trunc (ADD_MONTHS (SYSDATE + 2/24, 3), 'q')-100' at on the last day of each quarter'

'Trunc (LEAST (NEXT_DAY (SYSDATE, ''saturday "), NEXT_DAY (SYSDATE," SUNDAY ") + (6 × 60 + 10) /(24 × 60 )'

--------------------------

 

1: executed per minute

Interval => TRUNC (sysdate, 'mi') + 1/(24*60)

Or

Interval = & gt; sysdate + 1/1440

 

2: daily scheduled execution

Example: Execute at every day

Interval => TRUNC (sysdate) + 1 + 1/(24)

 

3: scheduled weekly execution

Example: Execute at every Monday

Interval => TRUNC (next_day (sysdate, 'monday') + 1/24

 

4: scheduled monthly execution

Example: Execute at on the first day of every month

Interval => TRUNC (LAST_DAY (SYSDATE) + 1 + 1/24

 

5: Periodical execution on a quarterly basis

For example, the statement is executed at on the first day of each quarter.

Interval => TRUNC (ADD_MONTHS (SYSDATE, 3), 'q') + 1/24

 

6: scheduled execution every six months

For example, at a.m. on January 1, July 1 and January 1, January 1

Interval => ADD_MONTHS (trunc (sysdate, 'yyyy'), 6) + 1/24

 

7: scheduled execution every year

Example: Execute at on January 1, January 1 every year.

Interval => ADD_MONTHS (trunc (sysdate, 'yyyy'), 12) + 1/24

 

Part 3: Practical Application

--> </STYLE. <P>

Step 1: Create a job and execute the following SQL statement on the 25 th day of each month.

SQL> variable job2010 number;

SQL> begin

SQL> dbms_job.submit (: job2010, 'delete from region where trunc (sysdate-mtime)> 90and ib_qty = 0; commit; ', sysdate, 'trunc (LAST_DAY (SYSDATE) + 25) '); <= execution at every month

SQL> end;

SQL>/

 

Step 2: query the generated job id

SQL> select job, what from dba_jobs;

Job what

61 ..........

 

Step 3: run the created job

SQL> begin

SQL> dbms_job.run (61 );

SQL> end;

SQL>/

 

Step 4: query the next execution time of the job

SQL> select job, next_date, what from dba_jobs

SQL> where job = '61 ';

 

Step 5: Delete the job

SQL> begin

SQL> dbms_job.remove (61 );

SQL> end;

SQL>/