########sample 2

APPLIES TO:
Oracle Database - Standard Edition - Version 12.2.0.1 and later
Information in this document applies to any platform.
SYMPTOMS
Statspack schema import in 19C failing with following error:
IMP-00017: following statement failed with ORACLE error 27486
"BEGIN DBMS_JOB.ISUBMIT(JOB=>1,WHAT=>'statspack.snap;',NEXT_DATE=>TO_DATE("
"'2020-07-26:07:10:00','YYYY-MM-DD:HH24:MI:SS'),INTERVAL=>'TRUNC(SYSDATE+30/"
"1440,''MI'')',NO_PARSE=>TRUE); END;"
IMP-00003: ORACLE error 27486 encountered
ORA-27486: insufficient privileges
ORA-06512: at "SYS.DBMS_ISCHED", line 9396

CHANGES
No changes

CAUSE
Missing privilege on DBMS_JOB.

SOLUTION
In 19c Privilege on DBMS_JOB need to be explicitly granted to the importing user:

Grant Create Job To "<IMPORTING SCHEMA>

#####sample 1
IF: An Example to Convert from DBMS_JOB Jobs to DBMS_SCHEDULER Jobs (Doc ID 2117140.1) To BottomTo Bottom

In this Document
Goal
Solution
References
APPLIES TO:
Oracle Database - Enterprise Edition - Version 10.2.0.1 and later
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Information in this document applies to any platform.
GOAL
This document summarizes the steps to convert a job created using DBMS_JOB to a DBMS_SCHEDULER job with the help of an example.

SOLUTION
1. Obtain the DDL for DBMS_JOB job.

The definition of a job submitted via DBMS_JOB can be obtained by using the dbms_job.user_export procedure.

set serveroutput on
DECLARE
callstr VARCHAR2(500);
BEGIN
dbms_job.user_export(23, callstr);
dbms_output.put_line(callstr);
END;
/

dbms_job.isubmit(job=>23,what=>'sample_procedure;',next_date=>to_date('2016-03-1
6:17:00:00','YYYY-MM-DD:HH24:MI:SS'),interval=>'SYSDATE + 1',no_parse=>TRUE);

Looking at the DDL, this job executes the stored procedure sample_procedure at 5 PM every day. This can be confirmed from the output of dba_jobs as well.

SQL> select log_user, schema_user, job,next_date,what,interval from dba_jobs where log_user='TEST';
LOG_USER SCHEMA_USE JOB NEXT_DATE WHAT INTERVAL
---------- ---------- ---------- -------------------- ------------------------------ ------------------------------
TEST TEST 23 16-MAR-16 sample_procedure; SYSDATE + 1

2. Create a DBMS_SCHEDULER job similar to above DBMS_JOB

A scheduler job has to be created such that it satisfies all the conditions of the DBMS_JOB job. In this example the job should execute the stored procedure sample_procedure at 5 PM every day.

BEGIN
DBMS_SCHEDULER.create_job (
job_name => 'sample_procedure_job', -- provide a name for the job
job_type => 'STORED_PROCEDURE', -- job executes a stored procedure
job_action => 'sample_procedure',
start_date => TRUNC(SYSDATE) + 17/24, -- start today at 5 PM
repeat_interval => 'freq=daily; byhour=17; byminute=0', -- repeat at 5 PM everyday
end_date => NULL,
enabled => TRUE, -- job is enabled
comments => 'Job created using the CREATE JOB procedure.');
End;
/

3. Ensure that the scheduler job is created as per the requirements

select JOB_NAME,JOB_TYPE,JOB_ACTION,STATE,NEXT_RUN_DATE, REPEAT_INTERVAL from dba_scheduler_jobs where job_name='SAMPLE_PROCEDURE_JOB';
JOB_NAME JOB_TYPE JOB_ACTION ENABL STATE NEXT_RUN_DATE REPEAT_INTERVAL
-------------------- ---------------- -------------------- ----- ------------ ---------------------------------------- ----------------------------------------
SAMPLE_PROCEDURE_JOB STORED_PROCEDURE sample_procedure TRUE SCHEDULED 16-MAR-16 05.00.00.000000 PM +00:00 freq=daily; byhour=17; byminute=0

4. Drop the DBMS_JOB job

exec dbms_job.remove(23);

REFERENCES
NOTE:270256.1 - How to Create a Job Using DBMS_SCHEDULER - 10g Job Scheduling Feature
NOTE:2109399.1 - How to Schedule a Job using DBMS_JOB