Compute
Elastic Cloud Server
Huawei Cloud Flexus
Bare Metal Server
Auto Scaling
Image Management Service
Dedicated Host
FunctionGraph
Cloud Phone Host
Huawei Cloud EulerOS
Networking
Virtual Private Cloud
Elastic IP
Elastic Load Balance
NAT Gateway
Direct Connect
Virtual Private Network
VPC Endpoint
Cloud Connect
Enterprise Router
Enterprise Switch
Global Accelerator
Management & Governance
Cloud Eye
Identity and Access Management
Cloud Trace Service
Resource Formation Service
Tag Management Service
Log Tank Service
Config
OneAccess
Resource Access Manager
Simple Message Notification
Application Performance Management
Application Operations Management
Organizations
Optimization Advisor
IAM Identity Center
Cloud Operations Center
Resource Governance Center
Migration
Server Migration Service
Object Storage Migration Service
Cloud Data Migration
Migration Center
Cloud Ecosystem
KooGallery
Partner Center
User Support
My Account
Billing Center
Cost Center
Resource Center
Enterprise Management
Service Tickets
HUAWEI CLOUD (International) FAQs
ICP Filing
Support Plans
My Credentials
Customer Operation Capabilities
Partner Support Plans
Professional Services
Analytics
MapReduce Service
Data Lake Insight
CloudTable Service
Cloud Search Service
Data Lake Visualization
Data Ingestion Service
GaussDB(DWS)
DataArts Studio
Data Lake Factory
DataArts Lake Formation
IoT
IoT Device Access
Others
Product Pricing Details
System Permissions
Console Quick Start
Common FAQs
Instructions for Associating with a HUAWEI CLOUD Partner
Message Center
Security & Compliance
Security Technologies and Applications
Web Application Firewall
Host Security Service
Cloud Firewall
SecMaster
Anti-DDoS Service
Data Encryption Workshop
Database Security Service
Cloud Bastion Host
Data Security Center
Cloud Certificate Manager
Edge Security
Managed Threat Detection
Blockchain
Blockchain Service
Web3 Node Engine Service
Media Services
Media Processing Center
Video On Demand
Live
SparkRTC
MetaStudio
Storage
Object Storage Service
Elastic Volume Service
Cloud Backup and Recovery
Storage Disaster Recovery Service
Scalable File Service Turbo
Scalable File Service
Volume Backup Service
Cloud Server Backup Service
Data Express Service
Dedicated Distributed Storage Service
Containers
Cloud Container Engine
SoftWare Repository for Container
Application Service Mesh
Ubiquitous Cloud Native Service
Cloud Container Instance
Databases
Relational Database Service
Document Database Service
Data Admin Service
Data Replication Service
GeminiDB
GaussDB
Distributed Database Middleware
Database and Application Migration UGO
TaurusDB
Middleware
Distributed Cache Service
API Gateway
Distributed Message Service for Kafka
Distributed Message Service for RabbitMQ
Distributed Message Service for RocketMQ
Cloud Service Engine
Multi-Site High Availability Service
EventGrid
Dedicated Cloud
Dedicated Computing Cluster
Business Applications
Workspace
ROMA Connect
Message & SMS
Domain Name Service
Edge Data Center Management
Meeting
AI
Face Recognition Service
Graph Engine Service
Content Moderation
Image Recognition
Optical Character Recognition
ModelArts
ImageSearch
Conversational Bot Service
Speech Interaction Service
Huawei HiLens
Video Intelligent Analysis Service
Developer Tools
SDK Developer Guide
API Request Signing Guide
Terraform
Koo Command Line Interface
Content Delivery & Edge Computing
Content Delivery Network
Intelligent EdgeFabric
CloudPond
Intelligent EdgeCloud
Solutions
SAP Cloud
High Performance Computing
Developer Services
ServiceStage
CodeArts
CodeArts PerfTest
CodeArts Req
CodeArts Pipeline
CodeArts Build
CodeArts Deploy
CodeArts Artifact
CodeArts TestPlan
CodeArts Check
CodeArts Repo
Cloud Application Engine
MacroVerse aPaaS
KooMessage
KooPhone
KooDrive
On this page

DBMS_JOB

Updated on 2022-07-29 GMT+08:00

Related Interfaces

Table 1 lists all interfaces supported by the DBMS_JOB package.

Table 1 DBMS_JOB

Interface

Description

DBMS_JOB.SUBMIT

Submits a job to the job queue. The job number is automatically generated by the system.

DBMS_JOB.ISUBMIT

Submits a job to the job queue. The job number is specified by the user.

DBMS_JOB.REMOVE

Removes a job from the job queue by job number.

DBMS_JOB.BROKEN

Disables or enables job execution.

DBMS_JOB.CHANGE

Modifies user-definable attributes of a job, including the job description, next execution time, and execution interval.

DBMS_JOB.WHAT

Modifies the job description of a job.

DBMS_JOB.NEXT_DATE

Modifies the next execution time of a job.

DBMS_JOB.INTERVAL

Modifies the execution interval of a job.

DBMS_JOB.CHANGE_OWNER

Modifies the owner of a job.

  • DBMS_JOB.SUBMIT

    The stored procedure SUBMIT submits a job provided by the system.

    A prototype of the DBMS_JOB.SUBMIT function is as follows:

    1
    2
    3
    4
    5
    DMBS_JOB.SUBMIT(
    what         IN   TEXT,
    next_date    IN   TIMESTAMP DEFAULT sysdate,
    job_interval IN   TEXT  DEFAULT 'null',
    job          OUT  INTEGER);
    
    NOTE:

    When a job is created (using DBMS_JOB), the system binds the current database and the username to the job by default. This function can be invoked by using call or select. If you invoke this function by using select, there is no need to specify output parameters. To invoke this function within a stored procedure, use perform.

    Table 2 DBMS_JOB.SUBMIT interface parameters

    Parameter

    Type

    Input/Output Parameter

    Can Be Empty

    Description

    what

    text

    IN

    No

    SQL statement to be executed. One or multiple DMLs, anonymous blocks, and SQL statements that invoke stored procedures, or all three combined are supported.

    next_date

    timestamp

    IN

    No

    Specifies the next time the job will be executed. The default value is the current system time (sysdate). If the specified time has past, the job is executed at the time it is submitted.

    interval

    text

    IN

    Yes

    Calculates the next time to execute the job. It can be an interval expression, or sysdate followed by a numeric value, for example, sysdate+1.0/24. If this parameter is left blank or set to null, the job will be executed only once, and the job status will change to 'd' afterward.

    job

    integer

    OUT

    No

    Specifies the job number. The value ranges from 1 to 32767. When dbms.submit is invoked using select, this parameter can be skipped.

    For example:

    1
    2
    3
    4
    5
    select DBMS_JOB.SUBMIT('call pro_xxx();', to_date('20180101','yyyymmdd'),'sysdate+1');
    
    select DBMS_JOB.SUBMIT('call pro_xxx();', to_date('20180101','yyyymmdd'),'sysdate+1.0/24');
    
    CALL DBMS_JOB.SUBMIT('INSERT INTO T_JOB  VALUES(1);  call pro_1(); call pro_2();', add_months(to_date('201701','yyyymm'),1), 'date_trunc(''day'',SYSDATE) + 1 +(8*60+30.0)/(24*60)' ,:jobid);
    
  • DBMS_JOB.ISUBMIT

    ISUBMIT has the same syntax function as SUBMIT, but the first parameter of ISUBMIT is an input parameter, that is, a specified job number. In contrast, that last parameter of SUBMIT is an output parameter, indicating the job number automatically generated by the system.

    For example:

    1
    CALL dbms_job.isubmit(101, 'insert_msg_statistic1;', sysdate, 'sysdate+3.0/24');
    
  • DBMS_JOB.REMOVE

    The stored procedure REMOVE deletes a specified job.

    A prototype of the DBMS_JOB.REMOVE function is as follows:

    1
    REMOVE(job  IN  INTEGER);
    
    Table 3 DBMS_JOB.REMOVE interface parameters

    Parameter

    Type

    Input/Output Parameter

    Can Be Empty

    Description

    job

    integer

    IN

    No

    Specifies the job number.

    For example:

    CALL dbms_job.remove(101);
  • DBMS_JOB.BROKEN

    The stored procedure BROKEN sets the broken flag of a job.

    A prototype of the DBMS_JOB.BROKEN function is as follows:

    1
    2
    3
    4
    DMBS_JOB.BROKEN(
    job          IN   INTEGER,
    broken       IN   BOOLEAN,
    next_date    IN   TIMESTAMP  DEFAULT  sysdate);
    
    Table 4 DBMS_JOB.BROKEN interface parameters

    Parameter

    Type

    Input/Output Parameter

    Can Be Empty

    Description

    job

    integer

    IN

    No

    Specifies the job number.

    broken

    boolean

    IN

    No

    Specifies the status flag, true for broken and false for not broken. Setting this parameter to true or false updates the current job. If the parameter is left blank, the job status remains unchanged.

    next_date

    timestamp

    IN

    Yes

    Specifies the next execution time. The default is the current system time. If broken is set to true, next_date is updated to '4000-1-1'. If broken is false and next_date is not empty, next_date is updated for the job. If next_date is empty, it will not be updated. This parameter can be omitted, and its default value will be used in this case.

    For example:

    1
    2
    CALL dbms_job.broken(101, true);
    CALL dbms_job.broken(101, false, sysdate);
    
  • DBMS_JOB.CHANGE

    The stored procedure CHANGE modifies user-definable attributes of a job, including the job content, next-execution time, and execution interval.

    A prototype of the DBMS_JOB.CHANGE function is as follows:

    1
    2
    3
    4
    5
    DMBS_JOB.CHANGE(
    job          IN   INTEGER,
    what         IN   TEXT,
    next_date    IN   TIMESTAMP,
    interval     IN   TEXT);
    
    Table 5 DBMS_JOB.CHANGE interface parameters

    Parameter

    Type

    Input/Output Parameter

    Can Be Empty

    Description

    job

    integer

    IN

    No

    Specifies the job number.

    what

    text

    IN

    Yes

    Specifies the name of the stored procedure or SQL statement block that is executed. If this parameter is left blank, the system does not update the what parameter for the specified job. Otherwise, the system updates the what parameter for the specified job.

    next_date

    timestamp

    IN

    Yes

    Specifies the next execution time. If this parameter is left blank, the system does not update the next_date parameter for the specified job. Otherwise, the system updates the next_date parameter for the specified job.

    interval

    text

    IN

    Yes

    Specifies the time expression for calculating the next time the job will be executed. If this parameter is left blank, the system does not update the interval parameter for the specified job. Otherwise, the system updates the interval parameter for the specified job after necessary validity check. If this parameter is set to null, the job will be executed only once, and the job status will change to 'd' afterward.

    For example:

    1
    2
    CALL dbms_job.change(101, 'call userproc();', sysdate, 'sysdate + 1.0/1440');
    CALL dbms_job.change(101, 'insert into tbl_a values(sysdate);', sysdate, 'sysdate + 1.0/1440');
    
  • DBMS_JOB.WHAT

    The stored procedure WHAT modifies the procedures to be executed by a specified job.

    A prototype of the DBMS_JOB.WHAT function is as follows:

    1
    2
    3
    DMBS_JOB.WHAT(
    job             IN     INTEGER,
    what            IN     TEXT);
    
    Table 6 DBMS_JOB.WHAT interface parameters

    Parameter

    Type

    Input/Output Parameter

    Can Be Empty

    Description

    job

    integer

    IN

    No

    Specifies the job number.

    what

    text

    IN

    No

    Specifies the name of the stored procedure or SQL statement block that is executed.

    NOTE:
    • If the value specified by the what parameter is one or multiple executable SQL statements, program blocks, or stored procedures, this procedure can be executed successfully; otherwise, it will fail to be executed.
    • If the what parameter is a simple statement such as insert and update, a schema name must be added in front of the table name.

    For example:

    1
    2
    CALL dbms_job.what(101, 'call userproc();');
    CALL dbms_job.what(101, 'insert into tbl_a values(sysdate);');
    
  • DBMS_JOB.NEXT_DATE

    The stored procedure NEXT_DATE modifies the next-execution time attribute of a job.

    A prototype of the DBMS_JOB.NEXT_DATE function is as follows:

    1
    2
    3
    DMBS_JOB.NEXT_DATE(
    job          IN    INTEGER,
    next_date    IN    TIMESTAMP);
    
    Table 7 DBMS_JOB.NEXT_DATE interface parameters

    Parameter

    Type

    Input/Output Parameter

    Can Be Empty

    Description

    job

    integer

    IN

    No

    Specifies the job number.

    next_date

    timestamp

    IN

    No

    Specifies the next execution time.

    NOTE:

    If the specified next_date value is earlier than the current date, the job is executed once immediately.

    For example:

    1
    CALL dbms_job.next_date(101, sysdate);
    
  • DBMS_JOB.INTERVAL

    The stored procedure INTERVAL modifies the execution interval attribute of a job.

    A prototype of the DBMS_JOB.INTERVAL function is as follows:

    1
    2
    3
    DMBS_JOB.INTERVAL(
    job              IN   INTEGER,
    interval         IN   TEXT);
    
    Table 8 DBMS_JOB.INTERVAL interface parameters

    Parameter

    Type

    Input/Output Parameter

    Can Be Empty

    Description

    job

    integer

    IN

    No

    Specifies the job number.

    interval

    text

    IN

    Yes

    Specifies the time expression for calculating the next time the job will be executed. If this parameter is left blank or set to null, the job will be executed only once, and the job status will change to 'd' afterward. interval must be a valid time or interval type.

    For example:

    1
    CALL dbms_job.interval(101, 'sysdate + 1.0/1440');
    
    NOTE:

    For a job that is currently running (that is, job_status is 'r'), it is not allowed to use remove, change, next_date, what, or interval to delete or modify job parameters.

  • DBMS_JOB.CHANGE_OWNER

    The stored procedure CHANGE_OWNER modifies the owner of a job.

    A prototype of the DBMS_JOB.CHANGE_OWNER function is as follows:

    1
    2
    3
    DMBS_JOB.CHANGE_OWNER(
    job             IN     INTEGER,
    new_owner       IN     NAME);
    
    Table 9 DBMS_JOB.CHANGE_OWNER interface parameters

    Parameter

    Type

    Input/Output Parameter

    Can Be Empty

    Description

    job

    integer

    IN

    No

    Specifies the job number.

    new_owner

    name

    IN

    No

    Specifies the new username.

    For example:

    1
    CALL dbms_job.change_owner(101, 'alice');
    

Constraints

  1. After a new job is created, this job belongs to the current coordinator only, that is, this job can be scheduled and executed only on the current coordinator. Other coordinators will not schedule or execute this job. All coordinators can query, modify, and delete jobs created on other CNs.
  2. Create, update, and delete jobs only using the procedures provided by the DBMS_JOB package. These procedures synchronize job information between different CNs and associate primary keys between the pg_jobs tables. If you use DML statements to add, delete, or modify records in the pg_jobs table, job information will become inconsistent between CNs and system catalogs may fail to be associated, compromising internal job management.
  3. Each user-created task is bound to a CN. If the automatic migration function is not enabled, task statuses cannot be updated in real time when the CN is faulty during task execution. When a CN fails, all jobs on this CN cannot be scheduled or executed until the CN is restored manually. Enable the automatic migration function on CNs, so that jobs on the faulty CN will be migrated to other CNs for scheduling.
  4. For each job, the hosting CN updates the real-time job information (including the job status, last execution start time, last execution end time, next execution start time, the number of execution failures if any) to the pg_jobs table, and synchronizes the information to other CNs, ensuring consistent job information between different CNs. In the case of CN failures, job information synchronization is reattempted by the hosting CNs, which increases job execution time. Although job information fails to be synchronized between CNs, job information can still be properly updated in the pg_jobs table on the hosting CNs, and jobs can be executed successfully. After a CN recovers, job information such as job execution time and status in its pg_jobs table may be incorrect and will be updated only after the jobs are executed again on related CNs.
  5. For each job, a thread is established to execute it. If multiple jobs are triggered concurrently as scheduled, the system will need some time to start the required threads, resulting in a latency of 0.1 ms in job execution.

We use cookies to improve our site and your experience. By continuing to browse our site you accept our cookie policy. Find out more

Feedback

Feedback

Feedback

0/500

Selected Content

Submit selected content with the feedback