Toad World® Forums

How to query USER_ tables while being sys or system


Is there a way to query USER_ tables for an other schema owner in TOAD?

I connect to my database being system and would like to query SCOTT’S USER_TABLES.

I tried setting the current schema in Toad (drop box with all schema) and I also tried “ALTER SESSION SET CURRENT_SCHEMA” but both to no avail.

Thanks for your help.

Select *

from all_tables

where owner = ‘SCOTT’;


Select *

from dba_tables

where owner = ‘SCOTT’;

The first will only show you tables that you have privileges on.

The 2nd requires the SELECT privilege on DBA_TABLES, which is not granted to public.

That I knew but that is not exactly what I want. Is there anyway to see the query’s result just as if I was connected as the schema owner?

As far as I know, it’s not possible to do that. The current_schema trick doesn’t work in SQL*Plus either. I believe it’s because it doesn’t change the USER variable (‘select user from dual’ still returns your login user)

If you find a way, let me know!