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.
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
SET SERVEROUTPUT ON SIZE 1000000
FOR cur_rec IN (SELECT object_name, object_type
WHERE object_type IN ('PACKAGE' ))
IF cur_rec.object_type = 'TABLE' THEN
EXECUTE IMMEDIATE 'DROP ' || cur_rec.object_type || ' "' || cur_rec.object_name || '" CASCADE CONSTRAINTS';
EXECUTE IMMEDIATE 'DROP ' || cur_rec.object_type || ' "' || cur_rec.object_name || '"';
WHEN OTHERS THEN
DBMS_OUTPUT.put_line('FAILED: DROP ' || cur_rec.object_type || ' "' || cur_rec.object_name || '"');
2. Upload the old packages into the databases
impdp directory=dbbackup1 INCLUDE=PACKAGE dumpfile=PROD_DUMP.DMP logfile=cmtuatmetadata.log remap_schema=PROD:TEST