oracle scheduler job scheduled task practice

Oracle scheduler JOB scheduled task

Article directory

Oracle scheduler JOB scheduled tasks
- scheduled tasks
- basic grammar
- - Create job
  - Check job running status
  - JOB stop
  - JOB startup and deletion
  - timed expression
- Actual combat
- - Create stored procedure
  - Create JOB
- Step on the trap
- - View database system time zone
  - View session time zone
  - Modify database time zone
  - Modify session time zone
- Restart database
- - refer to

Scheduled tasks

Job is Oracle’s scheduled task, also called timer, scheduled job. The job automatically executes some scripts regularly, or performs data backup, data refining, database performance optimization, index rebuilding, etc. It needs Use job.

A job is a task that is scheduled for execution. Job can be a PL/SQL block, a SQL statement, an external script or program, etc. They can be scheduled for execution regularly or manually started for execution.

There is another way to implement scheduled job tasks, scheduler. The latter is recommended because jobs submitted using dbms_job have been abandoned in Oracle 10g and above. It is recommended to use the dbms_scheduler package to submit jobs.

Basic syntax

Create job

BEGIN
  DBMS_SCHEDULER.CREATE_JOB (
    job_name => 'job_name', -- the name of the job
    job_type => 'PLSQL_BLOCK', --The type of job, which can be PLSQL_BLOCK, STORED_PROCEDURE, etc.
    job_action => 'begin my_proc(); end;', -- the script or stored procedure executed by the job
    start_date => SYSTIMESTAMP, -- the time when the job starts execution
    repeat_interval => 'FREQ=DAILY; INTERVAL=1', -- the interval between job executions
    enabled => TRUE -- whether to enable the job
  );
END;

View job running status

SELECT job_name, job_type, enabled, state, last_start_date, next_run_date
FROM dba_scheduler_jobs;


SELECT *
FROM dba_scheduler_jobs
WHERE job_name = 'JOB_NAME';

SELECT job_name, state, last_start_date, next_run_date,enabled
FROM dba_scheduler_jobs
WHERE job_name = 'JOB_NAME';

JOB stopped

BEGIN
  DBMS_SCHEDULER.STOP_JOB (
    job_name => 'job_name',
    force_option => 'IMMEDIATE',
    commit_semantics=> 'ABORT');
END;

JOB startup and deletion

start up

BEGIN
  DBMS_SCHEDULER.RUN_JOB (
    job_name => 'JOB_NAME',
    use_current_session => FALSE);
END;

delete

BEGIN
  DBMS_SCHEDULER.DROP_JOB (
    job_name => 'CLEAN_REALTIME_ETCEXIT_DATA_JOB', -- the name of the job
    force => FALSE -- whether to forcefully delete the job
  );
END;

Timing expression

--Run once a day
    'SYSDATE + 1'
 
--Run once every hour
    'SYSDATE + 1/24'
 
--Run every 10 minutes
    'SYSDATE + 10/(60*24)'
 
--Run every 30 seconds
    'SYSDATE + 30/(60*24*60)'
 
--Run every other week
    'SYSDATE + 7'
 
--Run once on the last day of every month
    'TRUNC(LAST_DAY(ADD_MONTHS(SYSDATE,1))) + 23/24'
 
--0:00 on January 1st every year
    'TRUNC(LAST_DAY(TO_DATE(EXTRACT(YEAR FROM SYSDATE)||'12'||'01','YYYY-MM-DD')) + 1)'
 
--12 midnight every day
    'TRUNC(SYSDATE + 1)'
 
--Every morning at 8:30
    'TRUNC(SYSDATE + 1) + (8*60 + 30)/(24*60)'
 
--Every Tuesday at 12 noon
    'NEXT_DAY(TRUNC(SYSDATE ), ''TUESDAY'' ) + 12/24'
 
--12 midnight on the first day of every month
    'TRUNC(LAST_DAY(SYSDATE ) + 1)'
 
--23:00 on the last day of every month
    'TRUNC (LAST_DAY (SYSDATE)) + 23 / 24'
 
--11pm on the last day of each quarter
    'TRUNC(ADD_MONTHS(SYSDATE + 2/24, 3 ), 'Q' ) -1/24'
 
--Every Saturday and Sunday at 6:10 a.m.
    'TRUNC(LEAST(NEXT_DAY(SYSDATE, ''SATURDAY"), NEXT_DAY(SYSDATE, "SUNDAY"))) + (6*60 + 10)/(24*60)'

Practical combat

Create a stored procedure

create or replace procedure REALTIME_ETCEXIT_DELETE as
begin
  DELETE FROM "REALTIME_ETCTS_EXIT";
  commit;
end;

Create JOB

BEGIN
  DBMS_SCHEDULER.CREATE_JOB (
    job_name => 'clean_realtime_etcexit_data_job',
    job_type => 'STORED_PROCEDURE',
    job_action => 'REALTIME_ETCEXIT_DELETE',
    start_date => SYSDATE,
    repeat_interval => 'FREQ=DAILY; BYHOUR=0; BYMINUTE=0; BYSECOND=0',
    enabled => TRUE,
    comments => 'Clean expired data');
END;

Step into the trap

Oracle system time and session time may be different from your local time

In actual practice, it is found that what affects the time of scheduled tasks is the session time zone. If you are using DataGrip to connect to the database, you can set the time zone when configuring the connection properties.

View database system time zone

SELECT DBTIMEZONE FROM DUAL;

View session time zone

select sessiontimezone from dual;

Modify database time zone

alter database set time_zone=' + 8:00';

Then restart the database

Modify session time zone

ALTER SESSION SET TIME_ZONE=' + 08:00';

Restart the database

Login to server
Switch oracle user

su-root

Enter the sqlplus console

sqlplus /nolog

conn / as sysdba

Close database

shutdown immediate

Restart database

startup

Exit the console

exit

If you still cannot connect to the database after performing the above operations (ORA-01109: database not open)

Enter the sqlplus console

SQL> select con_id,name,open_mode from V$pdbs;

    CON_ID NAME OPEN_MODE
---------- ------------------------------- ----------
         3 ORCLPDB1 MOUNTED

Open this mounted container

SQL> alter pluggable database ORCLPDB1 open;

Pluggable database altered.

This will allow you to connect to the database

Reference

Detailed explanation of Oracle scheduled jobs

Scheduled tasks in Oracle – very good

Oracle12 error: ERROR at line 1: ORA-01109: database not open