CODEWITHSUNDEEP
sqloracle11g
RAJ
RAJ

Reputation: 69

Unable to query USER_TABLES from a different schema

I have two schemas TEST and DEV. I am working in the TEST schema and I want to get the list of tables starting with SDB in the DEV schema. So I have used the below query:

      SELECT TABLE_NAME FROM DEV.USER_TABLES WHERE UPPER(TABLE_NAME) LIKE 'SDB%';

but I am getting an ORA-00942 error. Is this an issue with grants? Or is there any other method by which I can get the list of tables in the DEV schema.

Upvotes: 3

Views: 2955

Answers (2)

sagi
sagi

Reputation: 40481

You need this:

SELECT TABLE_NAME FROM all_tables where OWNER = 'DEV' and UPPER(TABLE_NAME) LIKE 'SDB%';

all_tables contains all the table that are on the db that you have access to, so that the table you should select from.

The ora you got is because this isn't a specific user table, you can't select from dev.ORACLE TABLES , when you select from there, don't specify a schema

Upvotes: 4

Alex Poole
Alex Poole

Reputation: 191275

USER_TABLES is a SYS view which only contains objects you own. It doesn't exist as part of each user's schema (so there is no DEV.USER_TABLES), and you cannot see another user's objects in it. Use ALL_TABLES instead, specifying the owner:

SELECT TABLE_NAME FROM ALL_TABLES
WHERE OWNER = 'DEV'
AND UPPER(TABLE_NAME) LIKE 'SDB%';

You will only be able to see the table in the other schema if you have privileges on it. If you don't then it won't be listed; in that case you can query DBA_TABLES if you have permission to see that.

Upvotes: 3

Related Questions