This discussion is archived
11 Replies Latest reply: Oct 11, 2012 4:43 AM by Osama_Mustafa RSS

Login Details

743462 Newbie
Currently Being Moderated
I need to find the IP address who are all connecting to the database thru application or sqlplus
  • 1. Re: Login Details
    Niket Kumar Pro
    Currently Being Moderated
    col "SID/SERIAL" format a10
    col username format a15
    col osuser format a15
    col program format a40
    select     s.sid || ',' || s.serial# "SID/SERIAL"
    ,     s.username
    ,     s.osuser
    ,     p.spid "OS PID"
    ,     s.program
    from     v$session s
    ,     v$process p
    Where     s.paddr = p.addr
    order      by to_number(p.spid)
    /


    you can find ip information in listener lgs also.
  • 2. Re: Login Details
    Fran Guru
    Currently Being Moderated
    I need to find the IP address who are all connecting to the database thru application or sqlplus
    Good luck!!!

    Try to make a question, and use magic words like 'please', 'thanks', ....It does not cost too much
  • 3. Re: Login Details
    Osama_Mustafa Oracle ACE
    Currently Being Moderated
    -you can use SYS_CONTEXT
    OR
    - SELECT UTL_INADDR.get_host_address(terminal) FROM v$session
  • 4. Re: Login Details
    Osama_Mustafa Oracle ACE
    Currently Being Moderated
    Niket Kumar wrote:
    col "SID/SERIAL" format a10
    col username format a15
    col osuser format a15
    col program format a40
    select     s.sid || ',' || s.serial# "SID/SERIAL"
    ,     s.username
    ,     s.osuser
    ,     p.spid "OS PID"
    ,     s.program
    from     v$session s
    ,     v$process p
    Where     s.paddr = p.addr
    order      by to_number(p.spid)
    /


    you can find ip information in listener lgs also.
    your statements is wrong which not giving Ip
    SID/SERIAL USERNAME        OSUSER          OS PID
    ---------- --------------- --------------- ------------------------
    PROGRAM
    ----------------------------------------
    
    16,1                       SYSTEM          3760
    ORACLE.EXE (MMNL)
    
    25,1                       SYSTEM          3920
    ORACLE.EXE (Q001)
    
    29,69      SPP             Administrator   4484
    JDBC Thin Client
    
    SID/SERIAL USERNAME        OSUSER          OS PID
    ---------- --------------- --------------- ------------------------
    PROGRAM
    ----------------------------------------
    
    32,5       SCDB            Administrator   5792
    JDBC Thin Client
    
    33,99                      SYSTEM          6080
  • 5. Login Details
    967671 Newbie
    Currently Being Moderated
    Hello
    you can get information about connected İP. İf you type below query

    select sys_context('userenv','ip_address') ipaddr from dual;
  • 6. Re: Login Details
    Osama_Mustafa Oracle ACE
    Currently Being Moderated
    This will work on Schema level for One user Only to get them all Use UTL_INADDR.get_host_address
    SQL> SELECT SYS_CONTEXT('USERENV','IP_ADDRESS') FROM DUAL;
    
    SYS_CONTEXT('USERENV','IP_ADDRESS')
    ----------------------------------------------------------
    192.168.50.88
  • 7. Re: Login Details
    Niket Kumar Pro
    Currently Being Moderated
    Thanks for:

    you can use SYS_CONTEXT
    - SELECT UTL_INADDR.get_host_address(terminal) FROM v$session

    Edited by: Niket Kumar on Oct 11, 2012 1:51 AM
  • 8. Re: Login Details
    743462 Newbie
    Currently Being Moderated
    I am getting the below error
    SQL> SELECT UTL_INADDR.get_host_address(terminal) FROM v$session
    2
    SQL> /
    ERROR:
    ORA-29257: host pts/ta unknown
    ORA-06512: at "SYS.UTL_INADDR", line 19
    ORA-06512: at "SYS.UTL_INADDR", line 40
    ORA-06512: at line 1

    Can you please help here
  • 9. Re: Login Details
    967671 Newbie
    Currently Being Moderated
    SQL> SELECT UTL_INADDR.get_host_address(terminal) FROM v$session

    Type below command

    The IP address of the database server is returned if the specified host name is NULL or is omitted.

    SQL> SELECT UTL_INADDR.get_host_address from dual;

    GET_HOST_ADDRESS
    --------------------------------------------------------------------------------
    192.168.2.5
  • 10. Re: Login Details
    967671 Newbie
    Currently Being Moderated
    or go to this site

    http://www.oracle-base.com/articles/misc/identifying-host-names-and-addresses.php
  • 11. Re: Login Details
    Osama_Mustafa Oracle ACE
    Currently Being Moderated
    user8934564 wrote:
    I am getting the below error
    SQL> SELECT UTL_INADDR.get_host_address(terminal) FROM v$session
    2
    SQL> /
    ERROR:
    ORA-29257: host pts/ta unknown
    ORA-06512: at "SYS.UTL_INADDR", line 19
    ORA-06512: at "SYS.UTL_INADDR", line 40
    ORA-06512: at line 1

    Can you please help here
    Check this
    http://oraclequirks.blogspot.com/2007/08/ora-29257-host-unknown.html

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points