To check all tables in another schema, you should have one or more of the following system privileges:
SELECT ANY DICTIONARY
(SELECT | INSERT | UPDATE | DELETE) ANY TABLE
or the big-hammer, the DBA role.
With any of those privileges, you can select:
SELECT DISTINCT OWNER, OBJECT_NAME
FROM DBA_OBJECTS
WHERE OBJECT_TYPE = 'TABLE'
AND OWNER = '[some other schema]'
But, without those system privileges, you can just see the tables you have been granted some level of access to, whether directly or through a role like this:
SELECT DISTINCT OWNER, OBJECT_NAME
FROM ALL_OBJECTS
WHERE OBJECT_TYPE = 'TABLE'
AND OWNER = '[some other schema]'
Ultimately, you can always query the data dictionary for your own tables, as your rights to your tables cannot be revoked (as of 10g):
SELECT DISTINCT OBJECT_NAME
FROM USER_OBJECTS
WHERE OBJECT_TYPE = 'TABLE'
Back