0

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?

Sandra Rossi
  • 11,934
  • 5
  • 22
  • 48
Himanshu Sourav
  • 700
  • 1
  • 10
  • 35
  • 1
    the SAPUSER table is the table that holds the logon information to the actual database. It is generally only readable by the adm-User, in your case E64ADM. Usually the ADM-user is identified externally (meaning identified by the operating system). As your central instance is not on the same machine as the database instance, you may have to check on how E64ADM is identified on the database. Only that user should have access to that table, granting access to other users isn't recommended for security reasons. See notes 50088 and 400241 for details regarding the OPS$-mechanism. – Dirk Trilsbeek May 01 '15 at 09:31
  • thanks @DirkTrilsbeek , but as I mentioned, its a 3 tier architecture where database instance and central instance are expected to be installed on separate machines..and I have not deviated from standard installation process, hence not sure if I need to make any changes now.. – Himanshu Sourav May 02 '15 at 03:58

2 Answers2

1

who are you signed in as? unless it's the owner of the table you will need to change your code to include the owner ie.

select * from OPS$E64ADM.SAPUSER
davegreen100
  • 2,055
  • 3
  • 13
  • 24
1

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
anudeepks
  • 1,080
  • 1
  • 12
  • 23