• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar
  • Skip to footer navigation

The Geek Diary

  • OS
    • Linux
    • CentOS/RHEL
    • Solaris
    • Oracle Linux
    • VCS
  • Interview Questions
  • Database
    • oracle
    • oracle 12c
    • ASM
    • mysql
    • MariaDB
  • DevOps
    • Docker
    • Shell Scripting
  • Big Data
    • Hadoop
    • Cloudera
    • Hortonworks HDP

Oracle Database 12c : Creating a Scheduler Job in a Multitenant Database

by admin

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
Note: Same information can be obtained from dba_scheduler_jobs/all_scheduler_jobs/user_scheduler_jobs views inside the CDB.

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.
Note: Generally, there should not be any requirement to create scheduler jobs inside a CDB apart from a few exceptions such as backup jobs, monitoring jobs etc.

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.
Note: Same information can be obtained from dba_scheduler_jobs/all_scheduler_jobs/user_scheduler_jobs views inside the PDB.

Filed Under: oracle, oracle 12c

Some more articles you might also be interested in …

  1. Script To Get Tablespace Utilization In Oracle Database 12c
  2. RMAN Restore/Duplicate Performs Implicit Crosschecking and Cataloging
  3. How to Change SYS and SYSTEM Passwords in Oracle Database
  4. Oracle Database : Startup basics (How to start Oracle Database)
  5. How to check the status of OMS and Agent in OEM 13cR2
  6. Oracle 19c New Feature – SQL_DIAGNOSE_AND_REPAIR (New Automatic Diagnostics and Repair Function)
  7. Oracle Data Guard 12c New Feature: Far Sync Standby
  8. ORA-01666: control file is for a standby database – failover over standby as primary
  9. Operators in PL/SQL
  10. How to pass a value from shell script to GoldenGate replicat for mapping to target table column

You May Also Like

Primary Sidebar

Recent Posts

  • What are /dev/zero and /dev/null files in Linux
  • grpck command – Remove corrupt or duplicate entries in the /etc/group and /etc/gshadow files.
  • xxd command – Expressed in hexadecimal form
  • sesearch: command not found

© 2022 · The Geek Diary

  • Archives
  • Contact Us
  • Copyright