As a system in Sqlplus, how do I query another user table?

Question

In accordance with the selection of the name from system_privilege_map, the system was provided:

SELECT ANY TABLE 

... and many other * ANY TABLES.

Simple move

 select * from the_table; select * from the_table; 

... binds this answer:

ERROR on line 1: ORA-00942: table or view does not exist

I can log in as this user and execute the same command just fine.

I work under the assumption that I will be able to run queries (select in this case) agaisnt a common user DB table. Is my assumption correct, and if so, how to do it?

Answer 1

If the_table belongs to user "some_user", then:

 select * from some_user.the_table; 

Answer 2

As mentioned in previous answers, you can prefix the name of the object with the name of the scheme:

 SELECT * FROM schema_name.the_table; 

Or you can use a synonym (private or public):

 CREATE (PUBLIC) SYNONYM the_table FOR schema_name.the_table; 

Or you can issue the alter session command to set the default scheme to the one you want:

 ALTER SESSION SET current_schema=schema_name; 

Note that this simply sets the default schema and is the equivalent of prefixing all (unqualified) object names with schema_name . You can prefix objects with a different schema name to access an object from another schema. Using SET current_schema does not affect your privileges: you still have the privileges of the user you are logged into, and not with the scheme you set.

Answer 3

You need to do:

 SELECT * FROM schema_name.the_table; 

Or use SYNONYMs ...

 CREATE SYNONYM the_table FOR schema_name.the_table;