Re: help:login as another schema

In article <h4Rd8.888$Vs2.14_at_nwrddc01.gnilink.net>, "Mike says...
>
>Sometimes, I need to login as another schema in order to explain plan for
>some statements. When I looked at dba_users, the password is something like
>A375439DC97, etc. Is there any way we can use this encrypted password to
>login as another schema? Or I have to get the real password to do that,
>which changes frequently?
>
>Or is there anything like 'alter session set current_user = another scchema'
>so that I can run explain plan?
>
>Thanks for your help.
>
>

if you have the correct privs, see

http://osi.oracle.com/~tkyte/Misc/su.html

If you have ACCESS to the underlying tables, then setting the current_schema is all you need. for example, I'm logged in as "me". I want to explain a query that SCOTT has been running. I have access to SCOTTS tables but would have to rewrite his query with SCOTT. everywhere to get it to run. I can simply:

ops$tkyte_at_ORA817DEV.US.ORACLE.COM> desc emp ERROR:
ORA-04043: object emp does not exist

ops$tkyte_at_ORA817DEV.US.ORACLE.COM> desc scott.emp;

 Name                                 Null?    Type
 ------------------------------------ -------- -------------------------
 EMPNO                                NOT NULL NUMBER(4)
 ENAME                                         VARCHAR2(10)
 JOB                                           VARCHAR2(9)
 MGR                                           NUMBER(4)
 HIREDATE                                      DATE
 SAL                                           NUMBER(7,2)
 COMM                                          NUMBER(7,2)
 DEPTNO                                        NUMBER(2)

ops$tkyte_at_ORA817DEV.US.ORACLE.COM> set autotrace traceonly explain ops$tkyte_at_ORA817DEV.US.ORACLE.COM> select * from emp; select * from emp

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

ops$tkyte_at_ORA817DEV.US.ORACLE.COM> alter session set current_schema=scott;

Session altered.

ops$tkyte_at_ORA817DEV.US.ORACLE.COM> select * from emp;

Execution Plan

   0         SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=14 Bytes=448)
   1    0      TABLE ACCESS (FULL) OF 'EMP' (Cost=1 Card=14 Bytes=448)

set the current_schema to scott -- doesn't change the PRIVS of my session at all, just the default schema name used to qualify object references.

--
Thomas Kyte (tkyte@us.oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp