The DBMS_JOB package offered by PolarDB provides the capabilities to schedule and manage scheduled jobs. This topic describes how to create, manage, and delete a scheduled job.

Prerequisites

  • A ticket is submitted to enable the DBMS_JOB package. You cannot use command lines or the console to enable the DBMS_JOB package. If you want to enable the package,Submit a ticket to contact technical support.
  • The kernel version of your PolarDB cluster is V1.1.7 or later. For more information about how to view the kernel version of a cluster, see Release notes.

Notes

  • Only privileged accounts can create and use the DBMS_JOB package. For more information about how to create a privileged account, see Create database accounts.
  • If you have installed an extension that is related to the DBMS_JOB package, delete and reinstall it by following the instructions described in this topic. Use the following syntax to delete the extension:
    drop extension dbms_job;
    Warning If you delete the extension, the scheduled jobs that are related to the extension are deleted. Back up your data before you delete the extension.
  • You can create the dbms_job extension in only the postgres database. If you want to configure scheduled jobs for the other databases, configure cross-database scheduled jobs in the postgres database. For more information, see Run a scheduled job across databases.

Prepare test data

Note The test data applies only to the examples used in this topic.

Create a table named jobrun for testing, as shown in the following example:

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

Create a stored procedure named job_proc, as shown in the following example:

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 or delete the dbms_job extension

You can use the DBMS_JOB package only after you create the dbms_job extension. The following list describes the syntax that you can use to create or delete the extension:

  • Create the extension
    CREATE EXTENSION IF NOT EXISTS dbms_job;
  • Delete the extension
    DROP EXTENSION dbms_job CASCADE;
    Warning If you delete the extension, all the existing user jobs that are related to the extension are deleted.

Create a scheduled job

Use the following syntax:

SUBMIT(job OUT BINARY_INTEGER, what VARCHAR2
[, next_date DATE [, interval VARCHAR2 ]])
Parameter Description
job The jobid value. Pass the jobid value. After you pass the jobid value, the ID of the submitted job is returned. The job ID is automatically generated and is unique for each job.
what The name of the stored procedure that you want to call. You must specify this parameter. The job_proc stored procedure is used in the examples in this topic.
next_date The start time of the scheduled job. If you do not specify the parameter, the current time is automatically used.
interval The interval at which the scheduled job is run. For more information, see Table 1.
Table 1. INTERVAL description
Execution interval Example
Every minute
TRUNC(sysdate,'mi') + 1/(24*60)
Every day The system runs the job at 01:00:00 every day:
TRUNC(sysdate) + 1 + 1/(24)
Every week The system runs the job at 01:00:00 on every Monday:
TRUNC(next_day(sysdate,'monday')) + 1/24
Every month The system runs the job at 01:00:00 on the first day of every month:
TRUNC(LAST_DAY(SYSDATE)) + 1 + 1/24
Every quarter The system runs the job at 01:00:00 on the first day of every quarter:
TRUNC(ADD_MONTHS(SYSDATE,3),'Q') + 1/24
Every year The system runs the job at 01:00:00 on January 1 every year:
ADD_MONTHS(trunc(sysdate,'yyyy'), 12) + 1/24
Fixed point in time The system runs the job at 08:10:00 every morning:
Trunc(Sysdate+1) + (8*60+10)/24*60
Fixed interval The system runs the job at the fifteenth minute of every hour, such as 08:15:00, 09:15:00, and 10:15:00.
Trunc(sysdate,'hh') + (60+15)/(24*60)

Call the job_proc stored procedure to create a scheduled job, as shown in the following example:

DECLARE
   jobid           INTEGER;
BEGIN
   DBMS_JOB.SUBMIT(jobid,'job_proc;', SYSDATE, 'SYSDATE + 1/(24 * 60)');
END;
Note If you use single quotation marks for the time of a scheduled job, a nested quotation occurs. This leads to a syntax error. The following code provides an example:
DBMS_JOB.SUBMIT(jobid,'job_proc;', SYSDATE, 'TRUNC(sysdate,'mi') + 1/(24*60)');

You must enclose the corresponding parameter values in $$ symbols to prevent this error, as shown in the following example:

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

Modify the content, start time, and execution interval of a scheduled job

Use the following syntax:

CHANGE(job BINARY_INTEGER what VARCHAR2, next_date DATE,interval VARCHAR2)
Parameter Description
job The job ID. For more information about how to view the job ID, see Query scheduled jobs.
what The name of the stored procedure that you want to call.
Note If you want to use the current value, set this parameter to NULL.
next_date The start time of the scheduled job.
Note If you want to use the current value, set this parameter to NULL.
interval The interval at which the scheduled job is run. For more information, see Table 1.
Note If you want to use the current value, set this parameter to NULL.

Assume that you want to modify the scheduled job whose job ID is 1. You want the stored procedure to remain unchanged. You also want to change the start time of the scheduled job to December 29, 2020 and run the job at the fifteenth minute of every hour. In this case, you can run the following sample code:

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 job

Use the following syntax:

INTERVAL(job BINARY_INTEGER, interval VARCHAR2)
Parameter Description
job The job ID. For more information about how to view the job ID, see Query scheduled jobs.
interval The interval at which the scheduled job is run. For more information, see Table 1.

Modify the scheduled job whose job ID is 1, and set the interval to run the job at 01:00:00 every day, as shown in the following example:

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

Modify the start time of a scheduled job

Use the following syntax:

NEXT_DATE(job BINARY_INTEGER, next_date DATE)
Parameter Description
job The job ID. For more information about how to view the job ID, see Query scheduled jobs.
next_date The start time of the scheduled job.

Modify the scheduled job whose job ID is 1, and set the start time of the scheduled job to December 30, 2020, as shown in the following example:

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

Modify the content of a scheduled job

Use the following syntax:

WHAT(job BINARY_INTEGER, what VARCHAR2)
Parameter Description
job The job ID. For more information about how to view the job ID, see Query scheduled jobs.
what The name of the stored procedure that you want to call.

Modify the scheduled job whose job ID is 1, and change the stored procedure to job_proc2, as shown in the following example:

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

Stop and start a scheduled job

Use the following syntax:

BROKEN(job BINARY_INTEGER, broken BOOLEAN [, next_date DATE ])
Parameter Description
job The job ID. For more information about how to view the job ID, see Query scheduled jobs.
broken The status of the scheduled job. Valid values:
  • true: The status of the scheduled job is abnormal. If the status of a scheduled job is abnormal, the scheduled job is stopped. You can run the dbms_job.run command to force a scheduled job whose status is abnormal to run.
  • false: The status of the scheduled job is normal.
next_date The start time of the scheduled job. If you do not specify the parameter, the current time is automatically used.

Specify the status of the scheduled job whose job ID is 1 as abnormal, as shown in the following example:

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

Specify the status of the scheduled job whose job ID is 1 as normal, as shown in the following example:

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

Force a scheduled job to run

Use the following syntax:

RUN(job BINARY_INTEGER)
Parameter Description
job The job ID. For more information about how to view the job ID, see Query scheduled jobs.

Force the scheduled job whose job ID is 1 to run, as shown in the following example:

BEGIN
    DBMS_JOB.RUN(1);
END;

Delete a scheduled job

Use the following syntax:

REMOVE(job BINARY_INTEGER)
Parameter Description
job The job ID. For more information about how to view the job ID, see Query scheduled jobs.

Delete the scheduled job whose job ID is 1, as shown in the following example:

BEGIN
    DBMS_JOB.REMOVE(1);
END;

Query scheduled jobs

You can execute the following statement to query your scheduled jobs:

select * from sys.dba_jobs;

Query job execution records

You can execute the following statement to query the execution records of your scheduled jobs:

select * from dbmsjob.pga_joblog;

Run a scheduled job across databases

The DBMS_JOB package applies only to the postgres database. If you want to configure scheduled jobs for the other databases, configure cross-database scheduled jobs in the postgres database.

An example is used to describe how to configure a cross-database scheduled job. In this example, the DBMS_JOB package is configured in the postgres database. A scheduled job is configured to run in a database named test. In this example, you want to insert one data record per minute into a table in the test database. For more information about how to create a database, see Create a database.

  1. Create a table named jobrun and a stored procedure named job_proc in the test database.
    • Execute the following statement to create a table named jobrun:
      CREATE TABLE jobrun (
          id              serial               NOT NULL PRIMARY KEY,
          runtime         VARCHAR2(40)
      );
    • Execute the following statement to create a stored procedure named job_proc:
      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;
  2. Create a scheduled job in the postgres database.

    You must add the database where you want to run the scheduled job to the DBMS_JOB.SUBMIT() function. In this example, the database where the scheduled job runs is named test. For more information, see Create a scheduled job.

    The following code provides an example:

    DECLARE
       jobid           INTEGER;
    BEGIN
       DBMS_JOB.SUBMIT(jobid,'job_proc;', SYSDATE, 'SYSDATE + 1/(24 * 60)','test');
    END;
  3. Query the status and execution records of the scheduled job in the postgres database.
    • Query the scheduled job:
      select * from sys.dba_jobs;

      The following result is returned:

       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
    • Query the execution records:
      select * from dbmsjob.pga_joblog;

      The following result is returned:

       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. Query the data in the jobrun table in the test database.

    Execute the following query statement:

    select * from jobrun;

    The following query result is returned:

     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
Note If you want to modify the information about your cross-database scheduled job, you must add the corresponding database name when you modify the function. For example, if you want to change the execution interval from every minute in the preceding example to every two minutes, run the following code:
BEGIN
    DBMS_JOB.CHANGE(1,NULL,SYSDATE,'SYSDATE + 1/(24 * 30)','test');
END;