view with the tables from multiple user schema

Question

I have a view that is built on multiple tables from different users schema. By virtue of the currently logged in user, he is able to see the table from different schema. When the view is created the table name becomes ambiguous as the user have access to the same tables from the different schema. Is there any way to specify to use the table from current user schema while creating the view? Can we do it for one of the tables from the view definition while other tables can be selected from any schema?

Answer 1

You should be able to access schema (with correct permissions) by prefixing the schema name.

schemaname.tablename

Hope I understood your question correctly.

Answer 2

"When the view is created the table name becomes ambiguous as the user have access to the same tables from the different schema"

It isn't ambiguous to Oracle.

The view exists in a schema, SCHEMA_1. If that view refers to an object TABLE_A, Oracle will first look for an object TABLE_A in SCHEMA_1. If it finds a table, it will use that. If it finds a SYNONYM it will use whatever the synonym points to. If there is nothing in SCHEMA_1, it will look for a PUBLIC SYNONYM for TABLE_A and use whatever that points to.

SYNONYMS can point to other synonyms, views or tables.

You can query USER_DEPENDENCIES to see what objects the view is actually based on.

You cannot have a view in SCHEMA_1 that uses TABLE_A in SCHEMA_2 if queried from SCHEMA_2 but uses a different TABLE_A in SCHEMA_3 if queried from SCHEMA_3.