How to schedule a job using DBMS_SCHEDULER in Oracle?
Use DBMS_SCHEDULER.create_job to create a scheduled task, and define the task name, type, and operation content through parameters such as job_name, job_type, and job_action. For example, set repeat_interval to execute PL/SQL blocks or call stored procedures at 2 a.m. every day, manage task status through ENABLE, DISABLE, and DROP, and monitor tasks combined with the USER_SCHEDULER_JOBS view.
To schedule a job using DBMS_SCHEDULER in Oracle, you use the CREATE_JOB procedure. This built-in package provides a powerful and flexible way to automate tasks such as running PL/SQL blocks, executing shell scripts (via external programs), or calling stored procedures on a defined schedule.
Create a Basic Scheduled Job
The simplest way to schedule a job is by defining a named job, specifying what it should do, and when it should run.
BEGIN DBMS_SCHEDULER.create_job( job_name => 'my_daily_job', job_type => 'PLSQL_BLOCK', job_action => 'BEGIN my_procedure; END;', start_date => SYSTIMESTAMP, repeat_interval => 'FREQ=DAILY; BYHOUR=2; BYMINUTE=0; BYSECOND=0', -- Every day at 2:00 AM enabled => TRUE, comments => 'Runs daily at 2 AM' ); END; /- job_name : A unique identifier for the job.
- job_type : Type of action. Common values are PLSQL_BLOCK , STORED_PROCEDURE , EXECUTABLE .
- job_action : The code or program to execute.
- start_date : When the schedule starts.
- repeat_interval : Uses calendaring syntax to define frequency (similar to iCalendar).
- enabled : If TRUE, the job starts immediately after creation.
Common Scheduling Examples
You can customize the repeat_interval to fit various needs:
- Every Monday at 6 AM: FREQ=WEEKLY; BYDAY=MON; BYHOUR=6; BYMINUTE=0
- Every hour: FREQ=HOURLY; INTERVAL=1
- Every 5 minutes: FREQ=MINUTELY; INTERVAL=5
- Last day of each month: FREQ=MONTHLY; BYMONTHDAY=-1
Scheduling a Stored Procedure
If your job calls a stored procedure, set job_type to STORED_PROCEDURE and pass the procedure name in job_action .
BEGIN DBMS_SCHEDULER.create_job( job_name => 'run_stats_job', job_type => 'STORED_PROCEDURE', job_action => 'DBMS_STATS.GATHER_SCHEMA_STATS', start_date => SYSTIMESTAMP, repeat_interval => 'FREQ=DAILY; BYHOUR=1; BYMINUTE=0', enabled => TRUE, number_of_arguments => 1 );-- Pass arguments if needed DBMS_SCHEDULER.set_job_argument_value( job_name => 'run_stats_job', argument_position => 1, argument_value => 'SCOTT' );
DBMS_SCHEDULER.enable('run_stats_job'); END; /
Managing Jobs
After creating jobs, you can view, alter, or drop them.
- View jobs: SELECT job_name, enabled, state FROM user_scheduler_jobs;
- Disable a job: DBMS_SCHEDULER.disable('my_daily_job');
- Enable a job: DBMS_SCHEDULER.enable('my_daily_job');
- Drop a job: DBMS_SCHEDULER.drop_job('my_daily_job');
- Run job manually: DBMS_SCHEDULER.run_job('my_daily_job');
Basically, DBMS_SCHEDULER gives full control over timing, dependencies, logging, and error handling. Just define the job, set the schedule, and monitor via dictionary views like USER_SCHEDULER_JOBS and USER_SCHEDULER_JOB_LOG .
The above is the detailed content of How to schedule a job using DBMS_SCHEDULER in Oracle?. For more information, please follow other related articles on the PHP Chinese website!
Hot AI Tools
Undress AI Tool
Undress images for free
AI Clothes Remover
Online AI tool for removing clothes from photos.
Undresser.AI Undress
AI-powered app for creating realistic nude photos
ArtGPT
AI image generator for creative art from text prompts.
Stock Market GPT
AI powered investment research for smarter decisions
Hot Article
Popular tool
Notepad++7.3.1
Easy-to-use and free code editor
SublimeText3 Chinese version
Chinese version, very easy to use
Zend Studio 13.0.1
Powerful PHP integrated development environment
Dreamweaver CS6
Visual web development tools
SublimeText3 Mac version
God-level code editing software (SublimeText3)
Hot Topics
20518
7
13631
4
How to troubleshoot the Oracle Listener startup? (Network Services)
Mar 10, 2026 am 12:58 AM
Oraclelistenerstartupfailuresstemfromsilentlistener.oraparsingerrors,hostnameresolutionissues,orpermissionproblems—notbinariesorports;validatesyntaxwithreload,checkownership,verifyactualconfigpath,testDNS,useexplicitIPs,confirmADR_BASE,enabletracingp
How to patch Oracle Grid Infrastructure? (System Maintenance)
Mar 10, 2026 am 01:00 AM
Three things must be confirmed before applying the GI patch: 1. The opatchlsinventory-detail output of each node is consistent; 2. OCR and VoteDisk are online and crsctlcheckcluster-all and ocrcheck both return SUCCESS; 3. $GRID_HOME/crs/install/rootcrs.sh-prepatch has been successfully executed.
How to use Sequences in Oracle to generate IDs? (Auto-increment)
Mar 06, 2026 am 01:16 AM
ID auto-increment in Oracle requires the cooperation of SEQUENCE and BEFOREINSERT triggers, and the trigger must check: NEW.IDISNULL; 12c supports IDENTITY but is not compatible with older versions and disables explicit insertion.
How to use Oracle APEX to build a low-code app? (Rapid Development)
Mar 13, 2026 am 12:48 AM
OracleAPEXislow-glue,notno-code:itskipsinfrastructurebutrequiresSQL,PL/SQL,anddeclarativelogic;ApplicationProcesseshandleserver-sidevalidationandsideeffects,DynamicActionsmanageclient-sideinteractivity;InteractiveGridneedskey-preservedsourcesforediti
How to implement Transparent Data Encryption (TDE) in Oracle? (Data Security)
Mar 13, 2026 am 12:14 AM
OracleTDE must first enable and open the encrypted wallet (Wallet), otherwise ORA-28365 will be reported when executing ALTERTABLESPACE...ENCRYPTION; Wallet needs to be created, opened and managed through the ADMINISTERKEYMANAGEMENT command, and the path must be explicitly configured in sqlnet.ora and permissions must be ensured.
How to manage Flashback Data Archive_Flashback Data Archive table space allocation
Mar 28, 2026 pm 04:06 PM
The reason why the FlashbackDataArchive table space is full is that the hidden history table (SYS_FBA_HIST_XXXXXX) occupies the table space where the main table is located and does not go through ASSM cleaning; you need to use ALTERFLASHBACKARCHIVE...MODIFYTABLESPACE to migrate to the local management automatic segment space table space, and manually clean up the orphan history table.
How to use JSON data types in Oracle Database? (NoSQL Features)
Mar 08, 2026 am 01:03 AM
In Oracle's JSON scenario, you should select VARCHAR2 (4000CHAR) plus ISJSON constraints (small documents) or BLOB plus ISJSON constraints (large documents), and disable CLOB; ISJSON is a column-level constraint syntax, not a function call; the JSON_VALUE path must be a string literal; JSON_EXISTS needs to be speeded up with the JSON_VALUE function index.
How to grant SYSDBA permissions_sysdba management of password files and OS authentication
Apr 03, 2026 am 08:54 AM
Ordinary users can be authorized through GRANTSYSDBATOusername; provided that the database enables password file authentication (REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE) and has logged in with SYS; there is no need to restart after authorization, but the connection needs to explicitly specify assysdba, and the user credentials must exist in the V$PWFILE_USERS view.
