0

I am creating a Java application that connects to an Oracle Database.

In the application I connect to the DB as User A, which works as intended.

I then try to access tables in Schema 1 and Schema 2.

However when I do so I get the error:

ORA-00942: table or view does not exist

I looked in SQLDeveloper and in the "other users" section of User A I cannot see any tables for Schemas 1 and 2.

How can I solve this error so that I can see and access tables in Schemas 1 and 2?

Can I do this by assigning GRANT SELECT privileges on User A for schemas 1 and 2?

  • 1
    Yes you can grant SELECT object privilege to the users who need to access the tables. You could try(Only in test environment) it ;-) – JSapkota Apr 4 '16 at 13:10
  • thanks, is this preferred over creating synonyms? – java123999 Apr 4 '16 at 13:14
  • You can create synonym if you want to get rid of schema name prefixed with table name while selecting rows from table of another schema. – JSapkota Apr 4 '16 at 13:21
  • Sorry I am not sure what you mean? – java123999 Apr 4 '16 at 13:22
  • 1
    @java123999 - a synonym wont give you privileges on an object but will allow you to refer to SCHEMA1.TABLE_1 as TABLE_1 in your code as the synonym will be used to find the full name of the table. – BriteSponge Apr 4 '16 at 13:25
1

Take a look at what Oracle says about this type of error.

ORA-00942 table or view does not exist

Cause: The table or view specified does not exist, a synonym that is not allowed here was used, or a view was referenced where a table is required. If you are trying to access tables or views of another schema then you might not have required privileges to do it.
Action: You can-

  • check the spelling of the table or view name.
  • check that an existing table or view name exists.
  • contact the DBA if the table needs to be created or if user or application privileges are required to access the table.

  • The following is the example to address your situation. It shows how grant SELECT object privilege and how to create synonym.

    SQL> create user userA identified by userA;
    
    User created.
    
    SQL> grant connect, resource to usera;
    
    Grant succeeded.
    
    SQL> conn userA/userA
    Connected.
    SQL> create table tab (id number);
    
    Table created.
    
    SQL> conn / as sysdba
    Connected.
    SQL> create user schema1 identified by schema1;
    
    User created.
    
    SQL> grant connect, resource to schema1;
    
    Grant succeeded.
    
    SQL> conn schema1/schema1
    Connected.
    SQL> create table tab1(id number);
    
    Table created.
    
    SQL> select * from usera.tab;
    select * from usera.tab
                        *
    ERROR at line 1:
    ORA-00942: table or view does not exist
    
    
    SQL> conn usera/userA
    Connected.
    SQL> grant select on tab to schema1;
    
    Grant succeeded.
    
    SQL> conn schema1/schema1
    Connected.
    SQL> select * from usera.tab;
    
    no rows selected
    
    SQL> create synonym for usera.tab;
    create synonym for usera.tab
                   *
    ERROR at line 1:
    ORA-00995: missing or invalid synonym identifier
    
    
    SQL> create synonym usera_tab for usera.tab;
    create synonym usera_tab for usera.tab
    *
    ERROR at line 1:
    ORA-01031: insufficient privileges
    
    
    SQL> conn / as sysdba
    Connected.
    
    SQL> grant create synonym to schema1;
    
    Grant succeeded.
    
    SQL> conn schema1/schema1 
    Connected.
    SQL> create synonym usera_tab for usera.tab;
    
    Synonym created.
    
    SQL> select * from usera_tab;
    
    no rows selected
    
    SQL> 
    

    Your Answer

    By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

    Not the answer you're looking for? Browse other questions tagged or ask your own question.