Oracle say that you should be using impdp for all 11g import operations.
If you’re using impdp to import a dump file which contains some packages, you need to be aware that when you run an import with impdp, the package source is not automatically recreated i.e. your export will have exported the package create header with “CREATE PACKAGE …” and not “CREATE OR REPLACE PACKAGE …”.
Consequently, when you run the import of the package(s), if they already exist in the database, they will not be overwritten or replaced.
There is no command line option for impdp to change this feature (like “table_exists_action=replace” for TABLES).
You have to manually extract the package source from the exported dump file using the impdp “SQLFILE” command line option, which will create a separate SQL file containing the package sources.
Then modify the generated script file to change the “CREATE PACKAGE …” to “CREATE OR REPLACE PACKAGE …”.
If you’re running on UNIX, you can use the power of SED to accomplish this task:
> impdp <user>/<password> DIRECTORY=dpump_dir1 DUMPFILE=expfull.dmp SQLFILE=scriptfile.sql
> cat scriptfile.sql | sed ‘s/^CREATE PACKAGE/CREATE OR REPLACE PACKAGE/’ > scriptfile2.sql
NOTE: You need the “^” in the command so that it only replaces the first occurrence of “CREATE PACKAGE” on a line, otherwise you might replace something you need.
