As System in Sqlplus, how do I query another user's table?

Question

According to select name from system_privilege_map System has been granted:

SELECT ANY TABLE (and lots of other * ANY TABLES)

Plainly running

SQL>select * from the_table;
select * from the_table;
              *
ERROR at line 1:
ORA-00942: table or view does not exist

nets the given response. I can log in as that user and run the same command just fine.

I'm running under the assumption I should be able to run queries (select in this case) agaisnt a general user's DB table. Is my assumption correct, and if so, how do I do it?

Answer 1

If the_table is owned by user "some_user" then:

select * from some_user.the_table;

Answer 2

You need to do:

SELECT * FROM schema_name.the_table;

Or use SYNONYMs...

CREATE SYNONYM the_table FOR schema_name.the_table;

Answer 3

As the previous responses have said, you can prefix the object name with the schema name:

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 an alter session command to set the default schema to the the one you want:

ALTER SESSION SET current_schema=schema_name;

Note that this just sets the default schema, and is the equivalent of prefixing all (unqualified) object names with schema_name. You can still 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 logged in as, not the schema you have set.