sqlplus how to find details of the currently connected database session

Question

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

User · Answer

show user   to get connected user    select instance name from v instance   to get instance or set in sqlplus    set sqlprompt   USER    CONNECT IDENTIFIER gt

User · Answer

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   amp SID  s sid     and nvl s terminal        nvl   amp Terminal  nvl s terminal          and s process   nvl   amp Process  s process     and p spid   nvl   amp spid  p spid     and s username   nvl   amp username  s username     and nvl s osuser        nvl   amp OSUser  nvl s osuser          and nvl s machine        nvl   amp machine  nvl s machine          and nvl   amp SID  nvl   amp TERMINAL  nvl   amp PROCESS  nvl   amp SPID  nvl   amp USERNAME          nvl   amp OSUSER  nvl   amp MACHINE   NO VALUES          lt  gt   NO VALUES      For more details  https   ora-data blogspot in 2016 11 query-session-details html  Thanks

User · Answer

Take a look at this one  c  Tanel Poder  You may either run it from your glogin sql  so these settings will update each time you connect  or just run it manually  Notice host title command - it changes your sql plus console window title with session information - extremely useful with many windows open simultaneously   -- the Who am I script  def   mysid  NA  def  i spid  NA  def  i cpid  NA  def  i opid  NA  def  i serial  NA  def  i inst  NA  def  i host  NA  def  i user   amp  user  def  i conn   amp  connect identifier   col i username head USERNAME for a20 col i sid head SID for a5 new value mysid col i serial head SERIAL  for a8 new value  i serial col i cpid head CPID for a15 new value  i cpid col i spid head SPID for a15 new value  i spid col i opid head OPID for a5 new value  i opid col i host name head HOST NAME for a25 new value  i host col i instance name head INST NAME for a12 new value  i inst col i ver head VERSION for a10 col i startup day head STARTED for a8 col  i user noprint new value  i user col  i conn noprint new value  i conn col i myoraver noprint new value myoraver  select      s username          i username       i instance name i instance name       i host name         i host name       to char s sid           i sid       to char s serial        i serial        select substr banner  instr banner   Release    8 10  from v version where rownum   1  i ver       select  substr substr banner  instr banner   Release    8               1              instr substr banner  instr banner   Release    8      -1       from v version       where rownum   1  i myoraver      to char startup time   YYYYMMDD   i startup day       p spid              i spid       trim to char p pid          i opid       s process           i cpid       s saddr             saddr       p addr              paddr      lower s username    i user       upper   amp  connect identifier     i conn  from      v session s       v instance i       v process p where      s paddr   p addr and      sid    select sid from v mystat where rownum   1    -- Windows CMD exe specific stuff  -- host title  CP   amp  i user  amp  i conn  sid  amp mysid ser   amp  i serial spid  amp  i spid inst  amp  i inst host  amp  i host cpid  amp  i cpid opid  amp  i opid     host title  CP   amp  i user  amp  i conn  sid  amp mysid    amp  i serial  -- host doskey  exename sqlplus exe desc set lines 80 sqlprompt    Tdescribe    Tset lines 299 sqlprompt  SQL gt     -- short xterm title -- host echo -ne   033 0  amp  i user  amp  i inst  amp mysid  amp  i spid  007  -- long xterm title --host echo -ne   033 0 host  amp  i host inst  amp  i inst sid  amp mysid ser   amp  i serial spid  amp  i spid cpid  amp  i cpid opid  amp  i opid 007    def myopid  amp  i opid def myspid  amp  i spid def mycpid  amp  i cpid  -- undef  i spid  i inst  i host  i user  i conn  i cpid   Sample output   17 39 35 SYSTEM saz-dev gt   sandbox Connected  18 29 02 SYSTEM sandbox gt   me  USERNAME             INST NAME    HOST NAME                 SID   SERIAL   VERSION    STARTED  SPID            OPID  CPID            SADDR    PADDR -------------------- ------------ ------------------------- ----- -------- ---------- -------- --------------- ----- --------------- -------- -------- SYSTEM               xe           OARS-SANDBOX              34    175      11 2 0 2 0 20130318 3348            30    6108 7776       6F549590 6FF51020  1 row selected   Elapsed  00 00 00 04

User · Answer

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

User · Answer

select sys context  USERENV   INSTANCE NAME   from dual     amp   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

User · Answer

select   from v session where sid   to number substr dbms session unique session id 1 4   XXXX

User · Answer

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

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

Examples related to oracle

Examples related to sqlplus