Re: session current_user vs user_tables etc

From: Mark C. Stock <mcstockX_at_Xenquery>
Date: Wed, 25 Jan 2006 18:21:45 -0500
Message-ID: <aZWdnalPh82TlUXenZ2dnUVZ_sydnZ2d@comcast.com>

"Malcolm Dew-Jones" <yf110_at_vtn1.victoria.tc.ca> wrote in message news:43d7fedd$1_at_news.victoria.tc.ca...
>I can use
> alter session set current_schema=xxx
>
> and then queries such as
> select * from table_name_no_schema
> will find the table in the xxx schema, just as if I was xxx to begin with.
>
> However, if I do queries such as
> select * from user_tables
> then I see my own tables, not the xxx tables (which is understandable, I'm
> not questioning that).
>
> My question is, are there any views similar to the user_* views but to
> access things within the current_schema, or must I use the all_* views and
> filter everything by sys_context('USERENV','CURRENT_SCHEMA') ?
>
> Feedback appreciated.
>
> malcolm

SQL> show user
USER is "OEX"

SQL> select sys_context('USERENV','CURRENT_SCHEMA') from dual;

SYS_CONTEXT('USERENV','CURRENT_SCHEMA')

---

OEX SQL> select table_name from all_tables
  2 where owner = sys_context('USERENV','CURRENT_SCHEMA');

TABLE_NAME

---

ALL_USERS_COPY
CACHE_TEST_MODIFIED
EMP

EURO_AUTHS
EURO_COLUMN_FORMATS
EURO_FORMAT_TYPES

...
24 rows selected.

SQL> alter session set current_schema = scott;

Session altered.

SQL> select table_name from all_tables
  2 where owner = sys_context('USERENV','CURRENT_SCHEMA');

TABLE_NAME

---

DEPT
EMP
PUBLIC_DEPT ++ mcs Received on Wed Jan 25 2006 - 17:21:45 CST