Advance troubleshooting for compile SYS.DBMS_XPLAN in Oracle 10g

In Oracle 10g, if full import is performed from Oracle 9i, it will cause following DBMS_STATS related package failed to compile, due to conflict name

alter package sys.dbms_xplan compile body;

Errors for PACKAGE BODY DBMS_XPLAN:

show error

LINE/COL ERROR
-------- -----------------------------------------------------------------
1566/9 PL/SQL: SQL Statement ignored
1570/34 PL/SQL: ORA-00980: synonym translation is no longer valid
1577/9 PL/SQL: SQL Statement ignored
1579/34 PL/SQL: ORA-00980: synonym translation is no longer valid
1585/9 PL/SQL: SQL Statement ignored
1599/27 PL/SQL: ORA-00980: synonym translation is no longer valid

Solution:
2 public synonyms which used by the package are the root cause. Drop them to resolve name resolution

drop public synonym existsnode;
drop public synonym extract;

catalog.sql cannot resolve this error. If it is executed, it will cause utlrp.sql to throw following error

BEGIN dbms_registry_sys.validate_components; END;

*
ERROR at line 1:
ORA-04063: package body "SYS.DBMS_REGISTRY_SYS" has errors
ORA-06508: PL/SQL: could not find program unit being called:
"SYS.DBMS_REGISTRY_SYS"
ORA-06512: at line 1

Resolve it by re-create it:
@?/rdbms/admin/prvtcr.plb
@?/rdbms/admin/utlrp

Please use following PayPal donate if my post helped