On 05/12/2004 09:10:26 PM, Stephane Faroult wrote: > Sorry to have to say that, but you need to give a look to your beloved > manuals yourself. There is a difference between the current user, who > determines what to you are allowed to do (although that in some old > Oracle 7 version ... but I am erring) and the current schema, ie > basically who is the default owner of the objects you reference when you > don't specify this owner name explicitly. By default, the current schema > IS the current user. But afterwards they can live independent lives. > Jacques' was the correct answer. Ryan asked for CURRENT USER and that is what I gave him. As for the schema, it can be changed. Current user, on the other hand, cannot be changed. If I do the following: Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production With the Partitioning, OLAP and Data Mining options SQL> alter session set current_schema=scott; Session altered. SQL> select sys_context('USERENV','SESSION_USER') from dual; SYS_CONTEXT('USERENV','SESSION_USER') --------------------------------------------------------------------------------OPS$MGOGALA SQL> select sys_context('USERENV','SESSION_SCHEMA') from dual; SYS_CONTEXT('USERENV','SESSION_SCHEMA') --------------------------------------------------------------------------------SCOTT SQL> I have OPS$MGOGALA for current user and and SCOTT for current schema. Let's see how it works: SQL> select username from v$session where type='USER'; USERNAME ------------------------------ OPS$MGOGALA SQL> V$SESSION reports a session by OPS$MGOGALA. Let's see about privilege level: SQL> alter system set timed_statistics=true; System altered. SQL> So, I can do "ALTER SYSTEM". Let's see what happens if I connect as SCOTT: SQL> SQL> connect scott/tiger Connected. SQL> alter system set timed_statistics=true; alter system set timed_statistics=true * ERROR at line 1: ORA-01031: insufficient privileges Obviously, SCOTT cannot do "ALTER SYSTEM". That means that, even with my schema altered, I still have the same privileges as I had when I was not disguised as SCOTT. SQL> connect / Connected. SQL> desc emp ERROR: ORA-04043: object emp does not exist SQL> alter session set current_schema=scott; Session altered. SQL> desc 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) SQL> The above means that when I change the current schema, the new schema is used for name resolution. All other characteristics of the session are inherited from the username that I originally connected as. That means that the username is much more important characteristics then the schema. As Ryan asked for the USERNAME and as I think that the USERNAME is more important then the schema, I gave him username. There is no correct or wrong answer to the question that Ryan asked, because the question itself was rather dubious and the only correct answer would be 42. If you ask me whether I know about the possibility of changing schema, I do, ever since the forementioned V7. -- Mladen Gogala Oracle DBA ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx put 'unsubscribe' in the subject line. -- Archives are at //www.freelists.org/archives/oracle-l/ FAQ is at //www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------