The post provides steps to create scheduler jobs in a multitenant database. It covers only DBMS_SCHEDULER jobs. The jobs created using DBMS_JOB jobs have not been covered here since DBMS_JOB has been retained in later versions of Oracle mainly for backward compatibility. It is highly recommended to migrate from DBMS_JOB to DBMS_SCHEDULER jobs.
Scheduler job in a CDB
Verify that this is a container DB and check the container IDs.
SQL> select cdb from v$database; CDB --- YES
SQL> select name, con_id, dbid, con_uid, guid from v$containers order by con_id; NAME CON_ID DBID CON_UID GUID -------- ---------- ---------- ---------- -------------------------------- CDB$ROOT 1 1433275616 1 2240F9ACD1841AC2E053E0A9E80AB8D1 PDB$SEED 2 2596338085 2596338085 2D201264743546D7E053DAA1400ADBB8 PDBORCL 3 3679952629 3679952629 2D20764F5D535B1DE053DAA1400AFF25
Creating a job
1. Connect to the CDB as the owner of the scheduler job and ensure that the con_id is correct. Create the scheduler job.
SQL> show con_id con_name user Connected. CON_ID ------------------------------ 1 CON_NAME ------------------------------ CDB$ROOT USER is "C##TEST"
---Create the scheduler job
BEGIN
DBMS_SCHEDULER.create_job (
job_name => 'testjob_12',
job_type => 'PLSQL_BLOCK',
job_action => 'dbms_output.put_line(''This is a sample 12c job'');',
start_date => SYSTIMESTAMP,
repeat_interval => 'freq=hourly; byminute=0',
end_date => NULL,
enabled => TRUE,
comments => 'Job created using the CREATE JOB procedure.');
End;
/
PL/SQL procedure successfully completed.
2. Check the job details :
sql> select CON_ID, JOB_NAME,JOB_TYPE,ENABLED, STATE,NEXT_RUN_DATE, REPEAT_INTERVAL from cdb_scheduler_jobs where job_name='TESTJOB_12' and owner='C##TEST'; CON_ID JOB_NAME JOB_TYPE ENABL STATE NEXT_RUN_DATE REPEAT_INTERVAL ---------- ------------ ------------ ----- ------------ ---------------------------------------- -------------------------------- 1 TESTJOB_12 PLSQL_BLOCK TRUE SCHEDULED 17-MAR-16 05.00.12.401442 AM +00:00 freq=hourly; byminute=0
Dropping a job
Connect to the CDB either as the owner of the scheduler job or as sys user and ensure that the con_id is correct. Drop the scheduler job.
--As sys user: SQL> show con_id con_name user CON_ID ------------------------------ 1 CON_NAME ------------------------------ CDB$ROOT USER is "SYS"
SQL> exec dbms_scheduler.drop_job('C##TEST.TESTJOB_12');
PL/SQL procedure successfully completed.
Or, as job owner:
connect c##test/test123 SQL> show con_id con_name user CON_ID ------------------------------ 1 CON_NAME ------------------------------ CDB$ROOT USER is "C##TEST"
SQL> exec dbms_scheduler.drop_job('TESTJOB_12');
PL/SQL procedure successfully completed.
Scheduler job in a PDB
Creating a job
Connect to the PDB as the owner of the scheduler job and ensure that the con_id is correct. Create the scheduler job.
SQL> sqlplus /nolog SQL> connect test/test123@PDBORCL SQL> show con_id con_name user CON_ID ------------------------------ 3 CON_NAME ------------------------------ PDBORCL USER is "TEST"
BEGIN
DBMS_SCHEDULER.create_job (
job_name => 'testjob_12',
job_type => 'PLSQL_BLOCK',
job_action => 'dbms_output.put_line(''This is a sample 12c job'');',
start_date => SYSTIMESTAMP,
repeat_interval => 'freq=hourly; byminute=0',
end_date => NULL,
enabled => TRUE,
comments => 'Job created using the CREATE JOB procedure.');
End;
/
PL/SQL procedure successfully completed.
Check the job details :
SQL> select CON_ID, JOB_NAME,JOB_TYPE,ENABLED, STATE,NEXT_RUN_DATE, REPEAT_INTERVAL from cdb_scheduler_jobs where job_name='TESTJOB_12'; CON_ID JOB_NAME JOB_TYPE ENABL STATE NEXT_RUN_DATE REPEAT_INTERVAL ---------- ------------ ------------ ----- ------------ ---------------------------------------- -------------------------------- 3 TESTJOB_12 PLSQL_BLOCK TRUE SCHEDULED 17-MAR-16 05.00.21.140918 AM +00:00 freq=hourly; byminute=0
Dropping a job
Connect to the PDB either as the owner of the scheduler job or as sys user and ensure that the con_id is correct. Drop the scheduler job.
As sys user:
SQL> connect sys/sys1234$@PDBORCL as sysdba Connected. SQL> show con_id con_name user CON_ID ------------------------------ 3 CON_NAME ------------------------------ PDBORCL USER is "SYS"
SQL> exec dbms_scheduler.drop_job('TEST.TESTJOB_12');
PL/SQL procedure successfully completed.
Or, as job owner
SQL> connect test/test124@PDBORCL Connected. SQL> show con_id con_name user CON_ID ------------------------------ 3 CON_NAME ------------------------------ PDBORCL USER is "TEST"
SQL> exec dbms_scheduler.drop_job('TESTJOB_12');
PL/SQL procedure successfully completed.