CREATE_JOB|V4.4.2| docs|Distributed Database

CREATE_JOB

Last Updated：2026-03-06 07:02:42 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 it is 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 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 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 a 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, which can be prefixed with a string. The resulting job name is then used in the `CREATE_JOB` procedure.
job_type	The type of the job to create. 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 you do not specify `job_action` for an inline procedure, an error is returned when the job is created. `job_action` is executed within autonomous transactions and follows all autonomous transaction guidelines and limitations. For stored procedures, `job_action` is the name of the stored procedure. If the procedure is in another schema, you must specify the schema. 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 value of this parameter ranges from [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 left empty, an error is returned, but the job can still be used. This attribute can take the following values: `FREQ`: specifies 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`: specifies a positive integer indicating the frequency of repetition. The default value is `1`, indicating a repetition every second. Notice When used with `BY`, `INTERVAL` must come before `BY`, for example, `freq=YEARLY;INTERVAL=2;BYMONTH=JUL` `BYMONTH`: specifies 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. Valid range: [1, 12]. `BYYEARDAY`: specifies a day of the year in numeric form. Valid values: [1, 366]. `BYMONTHDAY`: specifies the day of the month. Valid values are numbers or a list of these values. Values in a list are separated by commas. Valid range: [1, 31]. `BYDAY`: specifies 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 the last Friday of each month. If specified, `OB_PASSER_ERROR` is returned. `BYHOUR`: specifies the hour when the job runs. Valid values are numbers or a list of these values. Values in a list are separated by commas. Valid range: [0, 23]. `BYMINUTE`: specifies the minute when the job runs. Valid values are numbers or a list of these values. Values in a list are separated by commas. Valid range: [0, 59]. `BYSECOND`: specifies the second when the job runs. Valid values are numbers or a list of these values. 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 following table describes the combination conditions: When `FREQ = YEARLY`, you can use `BYMONTH + BYMONTHDAY` or `BYYEARDAY`. You can also use `BYHOUR + BYMINUTE + BYSECONDY` after `BYYEARDAY`. 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 deleted. If `auto_drop` is `FALSE`, the job is disabled, and the `STATE` of the job is set to `COMPLETED`. If you do not specify a value for `end_date`, the job will continue to run until it reaches the maximum number of runs or failures specified by `max_runs` or `max_failures`, at which point it will stop. The value of `end_date` must be after the value of `start_date`, otherwise an error is returned. If the value of `end_date` is the same as the value of `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 attribute 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 a disabled job, you can set this parameter to `TRUE` or use the `ENABLE` procedure to enable the job.
auto_drop	Specifies whether the job is automatically dropped when it completes or is disabled. This attribute can be set to `TRUE` or `FALSE`. If it is set to `TRUE`, the job is automatically dropped when it completes or is disabled. A completed job is one that meets any of the following conditions: The job's end date (or the end date of its 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, non-repeating job. A job is disabled when it fails `max_failures` times. You must use `SET_ATTRIBUTE` to set `max_failures`. If `auto_drop` is set to `FALSE`, the job is not dropped and its metadata is retained until you explicitly drop the job by using the `DROP_JOB` procedure. By default, `auto_drop` is set to `TRUE` when you create a job.
comments	Adds comments to the job. By default, this attribute is `NULL`.
job_style	Specifies the style of the job being created. Valid values are as follows: `'REGULAR'`: Creates a regular job, which is the default. `'LIGHTWEIGHT'`: Creates a lightweight job. This value is allowed only when 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. 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 then discarded. They store all job information in memory and do not back it up to disk. This means the information is lost when the instance is restarted. These jobs are designed to execute operations that must be performed immediately with minimal overhead.
credential_name	Specifies the default credentials 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) credentials are 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 following 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 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 the job_priority and max_runs parameters.

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;
       /

--View the job status in the business tenant:
SELECT JOB_NAME,enabled,NEXT_RUN_DATE,state,last_start_date FROM DBA_SCHEDULER_JOBS;

Here is an example of the return result:

+---------------------------+---------+-------------------------------------+-----------+-------------------------------------+
| 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