CREATE_JOB|V4.6.0|OceanBase Database| docs|Distributed Database

CREATE_JOB

Last Updated：2026-05-07 11:26:25 Updated

The CREATE_JOB procedure is used to create a job.

If the enabled attribute is set to TRUE, the Scheduler will automatically run the job according to its schedule. If the created job is disabled, it will not run unless the SET_ATTRIBUTE procedure is used to enable it.

Applicability

This topic applies only to OceanBase Database Enterprise Edition.

Syntax

Syntax for creating a job without using an existing program or plan

DBMS_SCHEDULER.CREATE_JOB (
job_name            IN  VARCHAR2,
job_type            IN  VARCHAR2,
job_action          IN  VARCHAR2,
number_of_argument  IN  BINARY_INTEGER DEFAULT 0,
start_date          IN  TIMESTAMP_UNCONSTRAINED DEFAULT NULL,
repeat_interval     IN  VARCHAR2 DEFAULT 'null',
end_date            IN  TIMESTAMP_UNCONSTRAINED DEFAULT NULL,
job_class           IN  VARCHAR2 DEFAULT 'DEFAULT_JOB_CLASS',
enabled             IN  BOOLEAN DEFAULT FALSE,
auto_drop           IN  BOOLEAN DEFAULT TRUE,
comments            IN  VARCHAR2 DEFAULT 'null',
credential_name     IN  VARCHAR2 DEFAULT 'null',
destination_name    IN  VARCHAR2 DEFAULT 'null',
max_run_duration    IN  BINARY_INTEGER DEFAULT 0);

Syntax for creating a job by using a named program object and an internal plan

DBMS_SCHEDULER.CREATE_JOB  (
job_name            IN  VARCHAR2,
program_name        IN  VARCHAR2,
start_date          IN  TIMESTAMP_UNCONSTRAINED DEFAULT NULL,
repeat_interval     IN  VARCHAR2 DEFAULT 'null',
end_date            IN  TIMESTAMP_UNCONSTRAINED DEFAULT NULL,
job_class           IN  VARCHAR2 DEFAULT 'DEFAULT_JOB_CLASS',
enabled             IN  BOOLEAN DEFAULT FALSE,
auto_drop           IN  BOOLEAN DEFAULT TRUE,
comments            IN  VARCHAR2 DEFAULT 'null',
job_style           IN  VARCHAR2 DEFAULT 'REGULER',
credential_name     IN  VARCHAR2 DEFAULT 'null',
destination_name    IN  VARCHAR2 DEFAULT 'null',
max_run_duration    IN  BINARY_INTEGER DEFAULT 0);

Parameters

Parameter	Description
job_name	The name of the job. This name must be unique in the SQL namespace. If the job belongs to another schema, you must qualify the name with the schema name. If you do not specify the job name, an error is returned. You can also use `GENERATE_JOB_NAME` to let the Scheduler generate a name. The generated name is a sequence number that can be prefixed with a string. The resulting string is used as the job name in the `CREATE_JOB` procedure.
job_type	The type of the job to be created. If this parameter is not specified, an error is returned. The only supported value in the current version is `STORED_PROCEDURE`. This parameter supports only procedures and not functions that return values.
job_action	The action of the job. If you do not specify `job_action` for an inline procedure, an error is returned when you create the job. `job_action` is executed within autonomous transactions and follows all autonomous transaction rules and limitations. For a stored procedure, `job_action` is the name of the stored procedure. If the stored procedure is in a different schema, you must specify the schema name. If case-sensitive names are required, enclose the schema name and stored procedure name in double quotation marks. For example, `job_action_proc=>'"Schema"."myProc "'`. When the job or procedure type is `STORED_PROCEDURE`, `job_action` does not support PL procedures with `INOUT` or `OUT` parameters.
number_of_argument	The number of parameters required by the job. The valid range is [0,255], with a default value of 0.
program_name	The name of the program associated with the job.
start_date	The date and time when the job plan is first executed. If both `start_date` and `repeat_interval` are empty, the job runs immediately after it is enabled.
repeat_interval	The frequency at which the job repeats. You can specify the repeat interval using a calendar expression or a PL expression. The next execution time of the job is determined by evaluating the specified expression. Note If this parameter is set to an empty string, an error is returned, but the feature remains available. The value of this attribute can be set as follows: `FREQ`: The type of repetition. Required. Valid predefined frequency values are `YEARLY`, `MONTHLY`, `WEEKLY`, `DAILY`, `HOURLY`, `MINUTELY`, and `SECONDLY`. You can also specify an existing schedule as a user-defined frequency. `INTERVAL`: A positive integer indicating the frequency of repetition. The default value is `1`, indicating one repetition per second. Notice When used with `BY`, `INTERVAL` must come before `BY`, for example, `freq=YEARLY;INTERVAL=2;BYMONTH=JUL` `BYMONTH`: The month in which the job is executed. Valid values are the abbreviations of months, numbers, or a list of these values. Values in a list are separated by commas. Valid range: [1, 12]. `BYYEARDAY`: The day of the year. Valid values are numbers in the range [1, 366]. `BYMONTHDAY`: The day of the month. Valid values are numbers or a list of these numbers. Values in a list are separated by commas. Valid range: [1, 31]. `BYDAY`: A list of dates. Valid values are the abbreviations of days of the week, such as `MON`, or a list of these abbreviations. Values in a list are separated by commas. The first day of the week is Monday. Note Specifying negative values for `BYDAY` is not supported. For example, `FREQ=MONTHLY;BYDAY=-1FRI` specifies that the job runs on the last Friday of each month. If you specify this, `OB_PASSER_ERROR` is returned. `BYHOUR`: The hour when the job runs. Valid values are numbers or a list of these numbers. Values in a list are separated by commas. Valid range: [0, 23]. `BYMINUTE`: The minute when the job runs. Valid values are numbers or a list of these numbers. Values in a list are separated by commas. Valid range: [0, 59]. `BYSECOND`: The second when the job runs. Valid values are numbers or a list of these numbers. Values in a list are separated by commas. Valid range: [0, 59]. In addition to specifying the repetition frequency separately, you can also combine specifications. The combination conditions are as follows: When `FREQ = YEARLY`, you can use `BYMONTH + BYMONTHDAY` or `BYYEARDAY`, followed by `BYHOUR + BYMINUTE + BYSECONDY`. When `FREQ = MONTHLY`, you can use `BYMONTHDAY + BYHOUR + BYMINUTE + BYSECOND`. When `FREQ = WEEKLY`, you can use `BYDAY + BYHOUR + BYMINUTE + BYSECOND`. When `FREQ = DAILY`, you can use `BYHOUR + BYMINUTE + BYSECOND`. When `FREQ = HOURLY`, you can use `BYMINUTE + BYSECOND`. When `FREQ = MINUTELY`, you can use `BYSECOND`. When `FREQ = SECONDLY`, you cannot use `BYXXX`.
end_date	The date and time when the job expires, that is, when it no longer runs. After `end_date`, if `auto_drop` is `TRUE`, the job is dropped. If `auto_drop` is `FALSE`, the job is disabled, and the `STATE` of the job is set to `COMPLETED`. If you do not specify the value of `end_date`, the job will continue to run unless you set `max_runs` or `max_failures`. In that case, the job stops when either of these values is reached. The value of `end_date` must be later than the value of `start_date`; otherwise, an error is returned. If the value of `end_date` is the same as that of `start_date`, the job is not executed, and no error is returned.
job_class	The class associated with the job.
enabled	Specifies whether to enable the created job. This attribute can be set to `TRUE` or `FALSE`, with a default value of `FALSE`, indicating that the created job is disabled. For disabled jobs, although their metadata is captured and they are considered database objects, the Scheduler ignores and does not process them. To process a disabled job, you can set this parameter to `TRUE` or use the `ENABLE` procedure.
auto_drop	Specifies whether the job is automatically dropped after it completes or is automatically disabled. This attribute can be set to `TRUE` or `FALSE`. If set to `TRUE`, the job is automatically dropped after it completes or is automatically disabled. A job is considered completed under the following conditions: The job end date (or the end date of the job schedule) has passed. The job has run `max_runs` times. You must use `SET_ATTRIBUTE` to set `max_runs`. The job is a one-time job that has run only once. If the job fails `max_failures` times, it is automatically disabled. `max_failures` is also set using `SET_ATTRIBUTE`. If `auto_drop` is set to `FALSE`, the job is not dropped and its metadata is retained until the job is explicitly dropped using the `DROP_JOB` procedure. By default, `auto_drop` is set to `TRUE` when a job is created.
comments	Adds comments to the job. By default, this attribute is `NULL`.
job_style	Specifies the style of the job being created. The possible values are as follows: `'REGULAR'`: creates a regular job, which is the default setting. `'LIGHTWEIGHT'`: creates a lightweight job. This value is allowed only if the job references a program object. Lightweight jobs are recommended for frequent execution of many short-lived jobs. In some cases, lightweight jobs can provide a slight performance improvement. `'IN_MEMORY_RUNTIME'`: creates an in-memory runtime job. These jobs are based on the lightweight job structure and further improve performance by maintaining an in-memory cache. This minimizes disk access for operations before and after job execution. `'IN_MEMORY_FULL'`: creates an in-memory full job. In-memory full jobs require a program and cannot have a schedule or repeat interval. When enabled, they automatically run and are discarded after execution. They store all job information in memory and do not back it up to disk, so this information is lost when the instance restarts. Their purpose is to execute operations that must be performed immediately with minimal overhead.
credential_name	Specifies the default credential used with the job. For local database jobs, its value must be `NULL`. For local external jobs, if this attribute is `NULL` (the default), the preferred (default) credential is selected.
destination_name	Specifies the database or external destination of the job. If the `credential_name` parameter of `CREATE_JOB` is `NULL`, each destination must be prefixed with a credential, in the format `sql credential.destination`.
max_run_duration	Specifies the maximum duration of the job.

Considerations

By default, jobs created by users are disabled. You must explicitly enable a job before you can run it as scheduled.

To associate a job with a specified class or program, you must have the EXECUTE privilege on the class or program.

Not all possible job attributes can be set by using the CREATE_JOB procedure. Some parameters must be set after the job is created. For example, you must use the SET_ATTRIBUTE procedure to set parameters such as job_priority and max_runs.

Examples

DECLARE
    i INTEGER;
BEGIN
    SELECT COUNT(*) INTO i FROM user_tables WHERE table_name = 'DBMS_SCHEDULER_T1';
    IF i = 0 THEN
        EXECUTE IMMEDIATE  'CREATE TABLE DBMS_SCHEDULER_T1(col DATE)';
    END IF;
    SELECT COUNT(*) INTO i FROM user_tables WHERE table_name = 'DBMS_SCHEDULER_T2';
    IF i = 0 THEN
        EXECUTE IMMEDIATE  'CREATE TABLE DBMS_SCHEDULER_T2(col DATE, job VARCHAR(128), job_name VARCHAR(128))';
    END IF;
END;
/

CREATE OR REPLACE PACKAGE JOB_UTILS IS
  PROCEDURE insert_date;
  PROCEDURE insert_date_program ( job IN VARCHAR2, job_name IN VARCHAR2);
END;
/

CREATE OR REPLACE PACKAGE BODY JOB_UTILS IS
  PROCEDURE insert_date IS
  BEGIN
    INSERT INTO DBMS_SCHEDULER_T1 VALUES(TO_CHAR(sysdate));
    COMMIT;
  END;
  PROCEDURE insert_date_program ( job IN VARCHAR2, job_name IN VARCHAR2) IS
  BEGIN
    INSERT INTO DBMS_SCHEDULER_T2 VALUES(sysdate, job, job_name);
    COMMIT;
  END;
END;
/

 BEGIN
     DBMS_SCHEDULER.CREATE_JOB(
             job_name => dbms_scheduler.generate_job_name,
             job_type => 'STORED_PROCEDURE',
             job_action => 'JOB_UTILS.insert_date',
            number_of_argument  => 5,
            start_date  => sysdate ,
             repeat_interval => 'FREQ=SECONDLY; INTERVAL=8',
            end_date  =>sysdate + 100 ,
            job_class   =>'DEFAULT_JOB_CLASS',
            enabled => true,
            auto_drop => true,
            comments =>'test' ,
           credential_name => 'null',
            destination_name => 'null');
           COMMIT;
       END;
       /

--In a business tenant, view the job status:
SELECT JOB_NAME,enabled,NEXT_RUN_DATE,state,last_start_date FROM DBA_SCHEDULER_JOBS;

The return result is as follows:

+---------------------------+---------+-------------------------------------+-----------+-------------------------------------+
| JOB_NAME                  | ENABLED | NEXT_RUN_DATE                       | STATE     | LAST_START_DATE                     |
+---------------------------+---------+-------------------------------------+-----------+-------------------------------------+
| FRIDAY_WINDOW             | 1       | 13-SEP-24 10.00.00.000000 PM +08:00 | NULL      | NULL                                |
| JOB$_1125899907865012     | 1       | 13-SEP-24 11.09.49.000000 AM +08:00 | SCHEDULED | 13-SEP-24 11.09.41.000965 AM +08:00 |
| MONDAY_WINDOW             | 1       | 16-SEP-24 10.00.00.000000 PM +08:00 | SCHEDULED | 09-SEP-24 10.00.00.020702 PM +08:00 |
| OPT_STATS_HISTORY_MANAGER | 1       | 14-SEP-24 10.50.02.365883 AM +08:00 | SCHEDULED | 13-SEP-24 10.50.02.366764 AM +08:00 |
| SATURDAY_WINDOW           | 1       | 14-SEP-24 06.00.00.000000 AM +08:00 | NULL      | NULL                                |
| SUNDAY_WINDOW             | 1       | 15-SEP-24 06.00.00.000000 AM +08:00 | NULL      | NULL                                |
| THURSDAY_WINDOW           | 1       | 19-SEP-24 10.00.00.000000 PM +08:00 | SCHEDULED | 12-SEP-24 10.00.00.012722 PM +08:00 |
| TUESDAY_WINDOW            | 1       | 17-SEP-24 10.00.00.000000 PM +08:00 | SCHEDULED | 10-SEP-24 10.00.00.012928 PM +08:00 |
| WEDNESDAY_WINDOW          | 1       | 18-SEP-24 10.00.00.000000 PM +08:00 | SCHEDULED | 11-SEP-24 10.00.00.015659 PM +08:00 |
+---------------------------+---------+-------------------------------------+-----------+-------------------------------------+
9 rows in set

OceanBase

Customer Stories

Documentation