[oracle] sqlplus how to find details of the currently connected database session

I have very recently started to work on oracle databases. Generally I have many sqlplus windows open to different oracle databases. When switching from one sqlplus session to another, how can i view the current session details for each sqlplus session ? Any help is greatly appreciated!

show user

to get connected user

 select instance_name from v$instance

to get instance or set in sqlplus

set sqlprompt "_USER'@'_CONNECT_IDENTIFIER> "

We can get the details and status of session from below query as:

select ' Sid, Serial#, Aud sid : '|| s.sid||' , '||s.serial#||' , '||
       s.audsid||chr(10)|| '     DB User / OS User : '||s.username||
       '   /   '||s.osuser||chr(10)|| '    Machine - Terminal : '||
       s.machine||'  -  '|| s.terminal||chr(10)||
       '        OS Process Ids : '||
       s.process||' (Client)  '||p.spid||' (Server)'|| chr(10)||
       '   Client Program Name : '||s.program "Session Info"
  from v$process p,v$session s
 where p.addr = s.paddr
   and s.sid = nvl('&SID',s.sid)
   and nvl(s.terminal,' ') = nvl('&Terminal',nvl(s.terminal,' '))
   and s.process = nvl('&Process',s.process)
   and p.spid = nvl('&spid',p.spid)
   and s.username = nvl('&username',s.username)
   and nvl(s.osuser,' ') = nvl('&OSUser',nvl(s.osuser,' '))
   and nvl(s.machine,' ') = nvl('&machine',nvl(s.machine,' '))
   and nvl('&SID',nvl('&TERMINAL',nvl('&PROCESS',nvl('&SPID',nvl('&USERNAME',
       nvl('&OSUSER',nvl('&MACHINE','NO VALUES'))))))) <> 'NO VALUES'
/

For more details: https://ora-data.blogspot.in/2016/11/query-session-details.html

Thanks,

I know this is an old question but I did try all the above answers but didnt work in my case. What ultimately helped me out is

SHOW PARAMETER instance_name

Try:

select * from v$session where sid = SYS_CONTEXT('USERENV','SID');

You might also be interested in this AskTom post

After seing your comment, you can do:

SELECT * FROM global_name;

select * from v$session
where sid = to_number(substr(dbms_session.unique_session_id,1,4),'XXXX')

select sys_context('USERENV','INSTANCE_NAME') from dual;

&

select instance_name from v$instance;

will give you the instance name. You can also use select * from global_name; to view the global name of the instance.