During Import(IMPDP) packages in oracle caused ORA-31684: Object type PACKAGE_BODY

During Datapump import process IMPDP, We are getting the following error due to package is already exists in schema. So, to overcome the problem, we need to drop the package from the schema which is already exists then import the backup again with IMPDP Utility.

ORA-31684: Object type PACKAGE_BODY

Following steps is need to perform when any changes is done under packages by development team. Then DBA need to refresh all the packages from development Server to Production Server. Then following steps will help to refresh all the packages of the database.

Note:
1. If you have one package to refresh then copy the coding from development to production and execute.
2. Take backup of completed database including packages coding.

Do the following steps carefully on the production box (take full backup of the database).

Important Note: Keep in mind the following script having user_objects. so if you login with SYS user then it drop all packages of SYS user. Please take care of it dangerous coding

1. The following script drop the all the packages of the login user

Note: Login with user name which need to impdp packages from other database


SET SERVEROUTPUT ON SIZE 1000000
BEGIN
FOR cur_rec IN (SELECT object_name, object_type
FROM user_objects
WHERE object_type IN ('PACKAGE' ))
LOOP
BEGIN
IF cur_rec.object_type = 'TABLE' THEN
EXECUTE IMMEDIATE 'DROP ' || cur_rec.object_type || ' "' || cur_rec.object_name || '" CASCADE CONSTRAINTS';
ELSE
EXECUTE IMMEDIATE 'DROP ' || cur_rec.object_type || ' "' || cur_rec.object_name || '"';
END IF;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.put_line('FAILED: DROP ' || cur_rec.object_type || ' "' || cur_rec.object_name || '"');
END;
END LOOP;
END;
/

2. Upload the old packages into the databases

impdp directory=dbbackup1 INCLUDE=PACKAGE dumpfile=PROD_DUMP.DMP logfile=cmtuatmetadata.log remap_schema=PROD:TEST

Advertisements

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 )

Google+ photo

You are commenting using your Google+ 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

This site uses Akismet to reduce spam. Learn how your comment data is processed.