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:
job_type=>’STORED_PROCEDURE’, job_action=>
, number_of_arguments=>0,
start_date=>’29-FEB-12 AM EUROPE/LONDON’, repeat_interval=>
, 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

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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s