Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question.

Asked: April 11, 2017 - 9:26 am UTC

Last updated: April 12, 2017 - 5:39 am UTC

Version: 12.1.0.2

Viewed 10K+ times! This question is

You Asked

Hi,

As per Oracle documentation, USERNAME is : Oracle username and SCHEMANAME is : Schema user name.

I tried to find the difference by running the below command in couple of our prod envs but most of them resulted 0 rows except for one :

SQL> select username,schemaname from v$session where username!=SCHEMANAME;

USERNAME SCHEMANAME
------------------------------ ------------------------------
SYSDG SYS

Want to understand the difference between these two.

and Connor said...

SQL> select username,schemaname from v$session where sid = sys_context('USERENV','SID');

USERNAME             SCHEMANAME
-------------------- ------------------------------
MCDONAC              MCDONAC

SQL> alter session set current_schema = scott;

Session altered.

SQL> select username,schemaname from v$session where sid = sys_context('USERENV','SID');

USERNAME             SCHEMANAME
-------------------- ------------------------------
MCDONAC              SCOTT


I might use "alter session set current_schema = scott" so that when I do

select * from emp

then by by default, I'm looking for scott.emp, not mcdonac.emp

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Ram, April 12, 2017 - 5:33 am UTC

So the purpose of current_schema is to avoid using synonyms ?
Connor McDonald
April 12, 2017 - 5:39 am UTC

That is certainly one potential use

The other possible cases

Rajeshwaran, April 12, 2017 - 12:12 pm UTC

The other case would be that, i may be a highly privileged users, where i need to run the deployment script for an application cattered over multiple schemas.

rather than connect/disconnect across multiple schema

i could login as a highly privileged users (DBA user) and then use "alter session" to switch across multiple schema for executing the deployment scripts.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.