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

Leave a reply

During the Datapump import process (IMPDP), we encounter an error because the package already exists in the schema. To fix this, we need to drop the existing package from the schema and then re-import the backup using the IMPDP utility.

ORA-31684: Object type PACKAGE_BODY

To update packages made by the development team, the DBA must refresh all packages from the development server to the production server. The steps below will guide you through refreshing the database packages.

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

Leave a Reply