CODEWITHSUNDEEP
sqloracle-databasesqlplusora-00942
Pete Brisco
Pete Brisco

Reputation:

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

According to select name from system_privilege_map System has been granted:

SELECT ANY TABLE

...and lots of other * ANY TABLES.

Plainly running

select * from the_table;
select * from the_table;

...nets the given response:

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

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?

Upvotes: 5

Views: 14538

Answers (3)

Gazmo
Gazmo

Reputation: 227

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.

Upvotes: 9

cagcowboy
cagcowboy

Reputation: 30828

You need to do:

SELECT * FROM schema_name.the_table;

Or use SYNONYMs...

CREATE SYNONYM the_table FOR schema_name.the_table;

Upvotes: 4

Tony Andrews
Tony Andrews

Reputation: 132570

If the_table is owned by user "some_user" then:

select * from some_user.the_table;

Upvotes: 7

Related Questions