oracle-databaseinstallationhp-uxsap-basis

oracle table entry does not exist

while installing sap on 3 tiered architecture, I need to install database instance (oracle) and central instance(sap) and two different machines. after completing database install and proceeding with central instance installation, the setup is trying to access a table and fails with following error

SELECT USERID, PASSWD FROM
SAPUSER WHERE USERID IN (:A0, :A1)

OCI-call failed with -1=OCI_ERROR SQL error 942: 'ORA-00942: table or view does not exist'

*** ERROR => ORA-942 when accessing table SAPUSER

so I checked and found out that two cases are possible

  1. Table does not exist or
  2. User has no access rights to this Table

next I checked for table, and found an entry in dba_tables,

SQL> select owner from dba_tables where table_name='SAPUSER';

OWNER
------------------------------
OPS$E64ADM

but when trying to fetch data from it using select query

SQL> select * from SAPUSER;
select * from SAPUSER
              *
ERROR at line 1:
ORA-00942: table or view does not exist

now I am confused, whether the table is available or not. what is the reason for this and how can it be resolved?

Solution

  • It depends on where you are accesing the object from,

    check to see which user you are logged in as

    SQL> SHOW USER

    This will show which user you are logged in as, if you are in OPS$E64ADM, the directly query using

    SQL> select * from SAPUSER;

    if show user show anyother user you need privilege to access it from other users, can ask dba or if you have access then run,

    SQL>  grant select on OPS$E64ADM.SAPUSER to username;  -- the username from which you want to access the table;

    then, you can acces from the other user , using,

    SQL> select * from OPS$E64ADM.SAPUSER