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;