the table/view doesn't exist by using different schema

Question

1) To create a table under schema A.

create table test_PK (
     stdid INT NOT NULL,
     stdname VARCHAR(10),
     PRIMARY KEY(stdid)
     );

insert into test_pk values (10,'Bob');  //Here works fine

2) To execute:

GRANT SELECT,INSERT,DELETE ON TEST_PK TO B;  //works fine

3) To use schema B to login the DB, while executing select * from test_pk; it complains:

ORA-00942: table or view does not exist
00942. 00000 - "table or view does not exist"
*Cause:
*Action:
Error at Line: 12 Column: 14

Did I miss something?

I think you are confusing schema and user. When you granting access to table you are granting it to the user. The user B still needs to be able to login to the A's schema — cha

sgeddes sgeddes · Accepted Answer · 2013-03-12T03:22:11

My guess is you need to specify the schema when logged in with a different user:

select * from A.test_pk

This post might also help: ORA-00942: Can select from "schema.table" but not "table"? -- you can create a synonym to bypass having to specify the schema in your SELECT.

the table/view doesn't exist by using different schema

1 Answers