ORA-00942

Re-create Grants And Synonyms For APPS Schema Fails With ORA-00942 For SYSTEM.AD_DDL [ID 438194.1]

Applies to:

Oracle Application Install - Version 11.5.10.2 to 11.5.10.2 [Release 11.5.10]
Information in this document applies to any platform.
***Checked for relevance on 06-Dec-2012***

Symptoms

Using adadmin to Re-generate Grants and Synonyms but get errors. This occured after doing upgrade to Database 10gR2.

ERROR
command that fails:

sqlplus -s APPS/***** @/$AD_TOP/patch/115/sql/adgrnctx4.sql &systempwd &un_fnd
CTXSYS &un_fnd FND

ERROR at line 1:
ORA-00942: table or view does not exist
ORA-06512: at "SYSTEM.AD_DDL", line 160
ORA-06512: at line 26

Cause

Some objects are missing. This SQL should provide the following result:

select owner, object_type, status from dba_objects where upper(object_name)=upper('ad_ctx_ddl');

OWNER                                 OBJECT_TYPE         STATUS
------------------------------  -------------------       -------
CTXSYS                                 PACKAGE                VALID
CTXSYS                                 PACKAGE BODY    VALID
APPS                                       SYNONYM              VALID
ICX                                         SYNONYM               VALID
APPLSYS                               SYNONYM               VALID
AMV                                       SYNONYM               VALID
JTF                                          SYNONYM               VALID
CS                                           SYNONYM               VALID
AR                                           SYNONYM               VALID
EGO                                        SYNONYM               VALID
PA                                           SYNONYM               VALID
OKC                                       SYNONYM               VALID

In this situation the AD_CTX_DDL package and package body for CTXSYS were missing.

Solution

To implement the solution, please execute the following steps:

1. cd $AD_TOP/patch/115/sql
2. Run sqlplus as system user
3. run script adctxpkg.sql:
   @adctxpkg.sql <SYSTEM password> <INTERMEDIA username> <apps username>
   eg:  @adctxpkg.sql manager CTXSYS apps