ORA-39083 ORA-01830 import 10g database using impdp
When I was importing 10g database to 12c , I got the below error
ORA-39083: Object type PROCOBJ:”SCHED_CLIENT”.”SCHED_REFRESH” failed to create with error:
ORA-01830: date format picture ends before converting entire input string
Failing sql is:
BEGIN
dbms_scheduler.create_job(‘”SCHED_REFRESH”‘,
job_type=>’STORED_PROCEDURE’, job_action=>
‘pa_sched.pr_refresh’
, number_of_arguments=>0,
start_date=>’29-FEB-12 10.52.16.089117 AM EUROPE/LONDON’, repeat_interval=>
‘freq=minutely;interval=15’
, end_date=>NULL,
job_class=>'”DEFAULT_JOB_CLASS”‘, enabled=>FALSE, auto_drop=>TRUE
The main reason for this error is , in 10G “start time” is accepted without any conversion using to_timestamp. but in 11G and 12c it do not accept the same, we need to convert that explicitly using conversion functions.
I took the DDL of all jobs using the below command
impdp dumpfile=full_dump_31052016.dmp INCLUDE=PROCOBJ sqlfile=PROCOBJ.sql directory=TEST
After that I changed the sql file manually and add the time stamp function like below
start_date=>TO_TIMESTAMP_TZ(’08-APR-11 11.51.16.341715 AM EUROPE/LONDON’,’DD-MON-RR HH:MI:SS.FF AM TZR’)
After that I executed the sql file manually.
Note : I have only 10 jobs so I edited them manually. Use some shell script to automate if you have more jobs.
Viswamitra's blog 