CODEWITHSUNDEEP
oracle-database
Chhitij
Chhitij

Reputation: 1

Name resolution of tables with similar name while querying

I have 3 schemas in Oracle:

  1. Two schemas lets say A and B have a table with same name :-say Table X
  2. I have granted select rights to schema C on Table X from both tables.
  3. Now if I write select * from X in schema C.

Which table will it use to select from X schema A or schema B ???

Upvotes: 0

Views: 51

Answers (1)

MT0
MT0

Reputation: 167981

Which table will it use to select from X schema A or schema B ???

Neither.

In the situation where you have just granted SELECT permissions to C then:

select * from X

will result in 

ORA-00942: table or view does not exist.

You would need to qualify the query with the schema:

select * from A.X

or

select * from B.X

or you would need to create a synonym:

CREATE SYNONYM X FOR B.X;

then select * from X will use table X in the B schema.

Upvotes: 1

Related Questions