Example of using scheduler in oracle

Source: Internet
Author: User
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;

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.