CREATE_JOB|V4.3.5| docs|Distributed Database

CREATE_JOB

Last Updated：2026-02-02 07:28:28 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 job is created in a disabled state, it will only run after being enabled using the SET_ATTRIBUTE procedure.

Applicability

This topic applies only to OceanBase Database Enterprise Edition.

Syntax

Syntax for creating a job without using an existing procedure 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 procedure 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, the schema name must be specified. If no job name is specified, 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, which can be prefixed with a string. The resulting job name is used 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. In the current version, only `STORED_PROCEDURE` is supported. This parameter supports only procedures and not functions with return values.
job_action	The action of the job. If `job_action` is not specified for an inline procedure, an error is returned when the job is created. `job_action` is executed within an autonomous transaction 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, the schema name must be specified. 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 is scheduled to run for the first time. 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 run 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 functionality remains unaffected. The value of this property can be specified as follows: `FREQ`: The type of repetition. Required. Valid predefined frequency values are `YEARLY`, `MONTHLY`, `WEEKLY`, `DAILY`, `HOURLY`, `MINUTELY`, and `SECONDLY`. Alternatively, you can 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 `INTERVAL` is used with `BY`, `INTERVAL` must appear before `BY`. For example, `freq=YEARLY;INTERVAL=2;BYMONTH=JUL`. `BYMONTH`: The month in which the job runs. Valid values are the abbreviations of months, numbers, or a list of these values. Values in a list are separated by commas. The valid range is [1, 12]. `BYYEARDAY`: The day of the year when the job runs. Valid values are numbers in the range [1, 366]. `BYMONTHDAY`: The day of the month when the job runs. Valid values are numbers or a list of these values. Values in a list are separated by commas. The valid range is [1, 31]. `BYDAY`: A list of days of the week. 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` indicates that the job runs on the last Friday of each month. If specified, `OB_PASSER_ERROR` is returned. `BYHOUR`: The hour when the job runs. Valid values are numbers or a list of these values. Values in a list are separated by commas. The valid range is [0, 23]. `BYMINUTE`: The minute when the job runs. Valid values are numbers or a list of these values. Values in a list are separated by commas. The valid range is [0, 59]. `BYSECOND`: The second when the job runs. Valid values are numbers or a list of these values. Values in a list are separated by commas. The valid range is [0, 59]. In addition to specifying the repetition frequency separately, you can also specify it in combination. 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, meaning it will no longer run. After `end_date`, if `auto_drop` is `TRUE`, the job will be deleted. If `auto_drop` is `FALSE`, the job will be disabled, and its `STATE` will be set to `COMPLETED`. If `end_date` is not specified, the job will continue to run until `max_runs` or `max_failures` is reached. `end_date` must be after `start_date`; otherwise, an error is returned. If `end_date` is the same as `start_date`, the job will not run, and no error will be returned.
job_class	The class associated with the job.
enabled	Specifies whether to enable the created job. This property can be set to `TRUE` or `FALSE`, with a default value of `FALSE`, meaning the created job will be disabled. For disabled jobs, although their metadata is captured and they are considered database objects, the scheduler will ignore and not process them. To process the 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 will be automatically dropped after it completes or is automatically disabled. A job is considered completed under the following conditions: The job's 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 and has been executed once. If a job fails `max_failures` times, it will be disabled. `max_failures` is also set using `SET_ATTRIBUTE`. If `auto_drop` is set to `FALSE`, the job will not be dropped and its metadata will be retained until it 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. Possible values are: `'REGULAR'`: Creates a regular job, which is the default. `'LIGHTWEIGHT'`: Creates a lightweight job. This value is allowed only if the job references program objects. 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, thus minimizing disk access for operations before and after execution. `'IN_MEMORY_FULL'`: Creates a fully in-memory job. Fully in-memory 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, meaning the information is lost upon instance restart. 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, this value must be `NULL`. For local external jobs, if this attribute is `NULL` (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 for which the job can run.

Considerations

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

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

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

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