PolarDB:DBMS_JOB

Prepare test data

CREATE TABLE jobrun (
    id              serial               NOT NULL PRIMARY KEY,
    runtime         VARCHAR2(40)
);

CREATE PROCEDURE job_proc
IS
BEGIN
    INSERT INTO jobrun(runtime) VALUES ('job_proc run at ' || TO_CHAR(SYSDATE, 'yyyy-mm-dd hh24:mi:ss'));
END;

Create a scheduled task

SUBMIT(job OUT BINARY_INTEGER, what VARCHAR2
[, next_date DATE [, interval VARCHAR2 ]])

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

TRUNC(sysdate) + 1 + 1/(24)

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

TRUNC(LAST_DAY(SYSDATE)) + 1 + 1/24

TRUNC(ADD_MONTHS(SYSDATE,3),'Q') + 1/24

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

Trunc(Sysdate+1) + (8*60+10)/24*60

Trunc(sysdate,'hh') + (60+15)/(24*60)

DECLARE
   jobid           INTEGER;
BEGIN
   DBMS_JOB.SUBMIT(jobid,'job_proc;', SYSDATE, 'SYSDATE + 1/(24 * 60)');
END;

DBMS_JOB.SUBMIT(jobid,'job_proc;', SYSDATE, 'TRUNC(sysdate,'mi') + 1/(24*60)');

DBMS_JOB.SUBMIT(jobid,'job_proc;', SYSDATE, $$TRUNC(sysdate,'mi') + 1/(24*60)$$);

Modify the content, execution time, and execution interval of a scheduled task

CHANGE(job BINARY_INTEGER what VARCHAR2, next_date DATE,interval VARCHAR2)

BEGIN
    DBMS_JOB.CHANGE(1,NULL,TO_DATE('29-DEC-20','DD-MON-YY'),$$Trunc(sysdate,'hh') + (60+15)/(24*60)$$);
END;

Modify the execution interval of a scheduled task

INTERVAL(job BINARY_INTEGER, interval VARCHAR2)

BEGIN
    DBMS_JOB.INTERVAL(1,'TRUNC(sysdate) + 1 + 1/(24)');
END;

Modify the execution time of a scheduled task

NEXT_DATE(job BINARY_INTEGER, next_date DATE)

BEGIN
    DBMS_JOB.NEXT_DATE(1, TO_DATE('30-DEC-20','DD-MON-YY'));
END;

Modify the content of a scheduled task

WHAT(job BINARY_INTEGER, what VARCHAR2)

BEGIN
    DBMS_JOB.WHAT(1,'job_proc2');
END;

Stop and start a scheduled task

BROKEN(job BINARY_INTEGER, broken BOOLEAN [, next_date DATE ])

BEGIN
    DBMS_JOB.BROKEN(1,true);
END;

BEGIN
    DBMS_JOB.BROKEN(1,false);
END;

Force a scheduled task to start

RUN(job BINARY_INTEGER)

BEGIN
    DBMS_JOB.RUN(1);
END;

Delete a scheduled task

REMOVE(job BINARY_INTEGER)

BEGIN
    DBMS_JOB.REMOVE(1);
END;

View scheduled tasks

SELECT * FROM sys.user_jobs;

View task execution records

SELECT * FROM all_scheduler_job_run_details;

Run a scheduled task across databases

The DBMS_JOB extension can be configured only in the postgres database. To set scheduled tasks for other databases, you must configure cross-database tasks.

In the following example, the DBMS_JOB extension is configured in the postgres database and runs in the test database. The task is required to insert one data record per minute into a table in the test database. For more information about how to create a database, see Database management.

1. In the test database, create a table named jobrun and a stored procedure named job_proc.

   • Create a table named jobrun. The following command provides an example:

     CREATE TABLE public.jobrun (
         id              serial               NOT NULL PRIMARY KEY,
         runtime         VARCHAR2(40)
     );

   • Create a stored procedure named job_proc. The following command provides an example:

     CREATE PROCEDURE public.job_proc
     IS
     BEGIN
         INSERT INTO jobrun(runtime) VALUES ('job_proc run at ' || TO_CHAR(SYSDATE, 'yyyy-mm-dd hh24:mi:ss'));
     END;

2. Switch to the postgres database and create a scheduled task.

   You must add the destination database to the DBMS_JOB.SUBMIT() function. In this example, the destination database is test. For more information about other parameters, see Create a scheduled task.

   Example:

   DECLARE
      jobid           INTEGER;
   BEGIN
     DBMS_JOB.SUBMIT(jobid,'job_proc;', SYSDATE, 'SYSDATE + 1/(24 * 60)', false, 'test');
   END;

3. In the postgres database, you can view the status and execution records of the scheduled task.

   • View the scheduled task:

     SELECT * FROM sys.user_jobs;

     The command returns the following result:

      job | jobloguser | job_user | database |           job_created            |           job_changed            |            last_date             | last_sec |         next_date         | next_sec |   total_time    | broken |                 interval                 | failures |                                                      what                                                       | instance
     -----+------------+----------+----------+----------------------------------+----------------------------------+----------------------------------+----------+---------------------------+----------+-----------------+--------+------------------------------------------+----------+-----------------------------------------------------------------------------------------------------------------+----------
        1 | DBUSER     | dbuser   | postgres | 29-OCT-20 02:38:49.478494 +00:00 | 29-OCT-20 02:38:49.478494 +00:00 | 29-OCT-20 02:51:12.025001 +00:00 | 02:51:12 | 29-OCT-20 02:53:12 +00:00 | 02:53:12 | 00:00:00.243224 | N      | BEGIN return SYSDATE + 1/(24 * 30); END; |        0 |  BEGIN EXECUTE IMMEDIATE 'SELECT dbmsjob.dbms_job_internal_job_link(''BEGIN job_proc; END;'', ''test'');' ; END |        0

   • View the execution records:

     SELECT * FROM all_scheduler_job_run_details;

     The command returns the following result:

      jlgid | jlgjobid | jlgstatus |             jlgstart             |   jlgduration   
     -------+----------+-----------+----------------------------------+-----------------
          1 |        1 | s         | 29-OCT-20 02:38:49.762995 +00:00 | 00:00:00.017495
          2 |        1 | s         | 29-OCT-20 02:39:50.061113 +00:00 | 00:00:00.016463
          3 |        1 | s         | 29-OCT-20 02:40:50.062331 +00:00 | 00:00:00.016244

4. Switch to the test database and query the data in the table.

   The following is the query command:

   SELECT * FROM jobrun;

   The command returns the following result:

    id |               runtime               
   ----+-------------------------------------
     1 | job_proc run at 2020-10-29 02:38:50
     2 | job_proc run at 2020-10-29 02:39:50
     3 | job_proc run at 2020-10-29 02:40:50

BEGIN
    DBMS_JOB.CHANGE(1,NULL,SYSDATE,'SYSDATE + 1/(24 * 30)','test');
END;