We use IMP and EXP often to refresh the SDE schema and other user schemas for our 11g test databases. I think you have the syntax right, and it looks like you were able to export and import fine. The error looks to occur in ArcGIS Desktop. I would first suggest that you try recompiling any invalid objects then try again to connect. Run this in SQLplus as SYS user:
EXEC DBMS_UTILITY.compile_schema(schema => 'SDE');
Post back with your results.
grant CREATE SESSION to sde; grant CREATE VIEW to sde; grant CREATE ANY INDEX to sde; grant CREATE TABLE to sde; grant CREATE TRIGGER to sde; grant CREATE PROCEDURE to sde; grant CREATE SEQUENCE to sde; grant CREATE INDEXTYPE to sde; grant CREATE LIBRARY to sde; grant CREATE PUBLIC SYNONYM to sde; grant CREATE OPERATOR to sde; grant DROP PUBLIC SYNONYM to sde; grant ADMINISTER DATABASE TRIGGER to sde; GRANT EXECUTE ON dbms_pipe TO public; GRANT EXECUTE ON dbms_lock TO public; GRANT EXECUTE ON dbms_lob TO public; GRANT EXECUTE ON dbms_utility TO public; GRANT EXECUTE ON dbms_sql TO public; GRANT EXECUTE ON utl_raw TO public;
alter package sde.pinfo_util compile; alter package sde.lock_util compile; alter package sde.version_user_ddl compile;
Yes, you need to import the packages along with the data from the dump file before you can compile them. The privileges above for the SDE user are system privileges and can be granted before the dump file is imported, with the exception of the GRANT EXECUTE lines which need to be run after the import completes but before the compiles are done. I looked at your export command that you provided earlier and it seemed like you exported the entire SDE schema; but can you confirm that is the case?
Yes, we were exporting the entire SDE Schema
The process you described is something we follow as well, and it works fine for us. The error you showed suggests the absence or invalidity of a particular package which is part of the SDE schema. I am not sure why that is happening. However, one thing to note is that Oracle 11g R2 (126.96.36.199) is no longer supported beginning at ArcGIS 10.2.1. The 10.2 release of ArcGIS was the last supported release with your version of Oracle database. What is the ArcGIS version of your geodatabase? From what you posted initially, it looks like it could also be 10.2.1 in addition to your desktop software. Are both Oracle databases at the same Oracle release?