Example of using scheduler in oracle

Welcome to the Oracle community forum and interact with 2 million technical staff to enter the example SQL code of using scheduler in oracle -- create a new table createtableT_TEST_JOB (IDLONG, TEST_DATETIMESTAMP (6) tablespaceGBIAPSpctfree10initrans1maxtrans255storage

Welcome to the Oracle community forum and interact with 2 million technical staff> enter the example SQL code for using scheduler in oracle -- create table T_TEST_JOB (ID LONG, TEST_DATE TIMESTAMP (6 )) tablespace GBIAPS pctfree 10 initrans 1 maxtrans 255 storage (initia

Welcome to the Oracle community forum and interact with 2 million technical staff> enter

Example of using scheduler in oracle

SQL code

-- Create a new table

Create table T_TEST_JOB

(

Id long,

TEST_DATE TIMESTAMP (6)

)

Tablespace GBIAPS

Pctfree 10

Initrans 1

Maxtrans 255

Storage

(

Initial 64

Minextents 1

Maxextents unlimited

);

-- Create Sequence

Create sequence SEQ_TEST_JOB

Minvalue 1

Max value 9999999999999999999999999999

Start with 1

Increment by 1

Cache 10;

-- Create a stored procedure

Create or replace procedure P_TEST_JOB

Begin

Insert into t_test_job (id, test_date) values (SEQ_TEST_JOB.NEXTVAL, sysdate );

End;

/

/*

Before using create_job or create_schedule, check NLS_DATE_LANGUAGE, NLS_DATE_FORMAT,

Use the alter session command to modify the values of parameters such as NLS_TIMESTAMP_TZ_FORMAT and NLS_TIMESTAMP_TZ_FORMAT.

Alter session set NLS_DATE_FORMAT = 'yyyy-MM-dd ';

Alter session set NLS_TIMESTAMP_FORMAT = 'yyyy-MM-dd hh24: mi: ss ';

Alter session set NLS_TIMESTAMP_TZ_FORMAT = 'yyyy-MM-dd HH: MI: SS. ff am tzr ';

*/

Select * from nls_session_parameters where parameter like '% _ DATE _ %' or parameter like '% _ TIMESTAMP _ % ';

-- Create a job

-- FREQ is used to specify the interval. Optional parameters include: YEARLY, MONTHLY, WEEKLY, DAILY, HOURLY, MINUTELY, and SECONDLY.

-- INTERVAL is used to specify the INTERVAL frequency. The value range of the specified value ranges from 1 to 999.

-- Additional parameters include BYMONTH, BYWEEKNO, BYYEARDAY, BYMONTHDAY, BYDAY, BYHOUR, BYMINUTE, and BYSECOND.

/*

Job FREQ = WEEKLY; BYDAY = MON, WED, FRI

Job FREQ = YEARLY; BYMONTH = MAR, JUN, SEP, DEC; BYMONTHDAY = 30

Execute once at on the first day of every month

Freq = Monthly; BYMONTHDAY = 1; BYHOUR = 1; BYMINUTE = 0; BYSECOND = 0; Interval = 1

*/

Begin

Sys. dbms_scheduler.create_job (job_name => 'job _ testjob ',

Job_type => 'stored _ PROCEDURE ',

Job_action => 'P _ TEST_JOB ',

Start_date => sysdate,

Repeat_interval => 'freq = MINUTELY; BYSECOND = 0; Interval = 1 ',

End_date => to_date (null ),

Job_class => 'default _ JOB_CLASS ',

Enabled => true,

Auto_drop => false,

Comments => 'Weather Yun Airport Web site ');

End;

/

-- Start job

Begin

Dbms_scheduler.enable ('job _ testjob ');

End;

/

-- Run job

Begin

Dbms_scheduler.run_job ('job _ testjob', TRUE); -- true indicates synchronous execution

End;

/

-- Stop a job

Begin

Dbms_scheduler.stop_job (job_name => 'job _ testjob', force => TRUE );

End;

/

-- Delete a job

Begin

Dbms_scheduler.drop_job (job_name => 'job _ testjob', force => TRUE );

End;

/

-- Query a job

Select * from user_scheduler_jobs;

Select * from dba_scheduler_jobs;

-- View data

Select * from t_test_job;

-- Delete data

Delete from t_test_job;