Blog dedicated to Oracle Applications (E-Business Suite) Technology; covers Apps Architecture, Administration and third party bolt-ons to Apps

Thursday, November 29, 2007

adadmin errors out with ORA-00942: TABLE OR VIEW DOES NOT EXIST

After a clone in one of the environments, whenever adadmin was run the following error appeared:

AD Administration error:
The following ORACLE error:
ORA-00942: table or view does not exist
occurred while executing the SQL statement:
SELECT to_char(APPLSYS.AD_SESSIONS_S.NEXTVAL) FROM SYS.DUAL

Unable to get the current value of sequence APPLSYS.AD_SESSIONS_S

We tried to check for the ownership and existance of the sequence AD_SESSIONS_S:

SQL> SELECT SUBSTR(OWNER,1,12)
OWNER,SUBSTR(OBJECT_NAME,1,45) NAME, SUBSTR(OBJECT_TYPE,1,12) TYPE
FROM DBA_OBJECTS where object_name = 'AD_SESSIONS_S' ;


OWNER NAME TYPE
------------ --------------------------------------------- ------------
APPLSYS AD_SESSIONS_S SEQUENCE
APPS AD_SESSIONS_S SYNONYM
WAVESET AD_SESSIONS_S SYNONYM
APPS_WEB AD_SESSIONS_S SYNONYM

Also tried to execute the query manually:

SELECT to_char(APPLSYS.AD_SESSIONS_S.NEXTVAL) FROM SYS.DUAL;
TO_CHAR(APPLSYS.AD_SESSIONS_S.NEXTVAL)
----------------------------------------
1852

We tried recreating grants and synonyms but it did not work. So we followed note 149987.1 and recreated AD_SESSIONS_S:

Run adodfcmp adutil.odf to re-create the AD_SESSIONs_S
Run adodfcmp for both tables and sequences.

Navigate to $AD_TOP/admin/odf and run the adodfcmp command similar to the
following

$ adodfcmp mode=tables touser=apps/apps priv_schema=system/manager \
> odffile=adutil.odf userid=applsys/apps changedb=yes

$ adodfcmp mode=sequences touser=apps/apps priv_schema=system/manager \
> odffile=adutil.odf userid=applsys/apps changedb=yes

That fixed the error.

No comments: