DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: Information for every client connected to the database

  1. #1
    Join Date
    May 2004
    Location
    India
    Posts
    5

    Information for every client connected to the database

    Hi,

    I am using the following query in order to get some information like Database Username, Client IP Address, Server IP Address, and Server Name.

    How should we get the Client Name? What syntax I add in the following query in order to get client name?

    In addition the following query did not give the Client_IP Address of other users who are connected to the database, when I query it as sysdba.


    SQL> select username,sys_context('USERENV','IP_ADDRESS') Client_IP,
    utl_inaddr.get_host_name SERVER_IP,utl_inaddr.get_host_name DB_Server_Name
    from V$session where username is not null;

    Could some one please assist me about the problem?




    Regards

    Benazir

  2. #2
    Join Date
    May 2004
    Location
    India
    Posts
    5
    I am using Datbase version 8.1.7.0

    Benazir

  3. #3
    Join Date
    Feb 2003
    Location
    INDIA
    Posts
    96
    Hi Benazir,

    Following query would give info you want

    select sid, serial#, username, osuser, machine, terminal, status
    from v$session;


    Dilip Patel
    OCP 8i

    Catch me online at Yahoo: ddpatel256

  4. #4
    Join Date
    May 2004
    Location
    India
    Posts
    5
    Hi,

    What's about CLIENT_IP AADDRESS, CLIENT NAME,
    SERVER IP _ADDRESS ?

    The following query did not give the Client_IP Address of other users who are connected to the database, when I query it as sysdba.


    How should we get the Client Name? What syntax I add in the following query in order to get client name?

    I am using version 8.1.7.0.



    SQL> select username,sys_context('USERENV','IP_ADDRESS') Client_IP,
    utl_inaddr.get_host_name SERVER_IP,utl_inaddr.get_host_name DB_Server_Name
    from V$session where username is not null;

    Or should we use other approach in order to get the desired results.

    Could someone suggest about it?

    Regards

    Benazir

  5. #5
    Join Date
    Jan 2003
    Location
    Hull, UK
    Posts
    220
    hi,

    SQL> select username,sys_context('USERENV','IP_ADDRESS') Client_IP,
    utl_inaddr.get_host_name SERVER_IP,utl_inaddr.get_host_name DB_Server_Name
    from V$session where username is not null;


    In ur sql above, it will give ip_address from where u r executing this sql, not the IP of all clients connected.


    Select sys_context('userenv','terminal') from dual;

    the above sql gives the machine-name.

    Srini

  6. #6
    Join Date
    May 2004
    Location
    India
    Posts
    5
    Thanks

    How should we get the IP_Address of All the connected clients?
    What is the replacement of my following query:

    SQL> select username,sys_context('USERENV','IP_ADDRESS') Client_IP,
    utl_inaddr.get_host_name SERVER_IP,utl_inaddr.get_host_name DB_Server_Name
    from V$session where username is not null;


    How should I get the client name and IP address for all the connected users?

    Thanks



    REgards

    BEnazir

  7. #7
    Join Date
    Feb 2000
    Location
    Singapore
    Posts
    1,758
    I doubt if such feature is there. But try auditing "CREATE SESSION" and look into comment$text column in the SYS.AUD$
    (Not tested yet..)
    Sanjay G.
    Oracle Certified Professional 8i, 9i.

    "The degree of normality in a database is inversely proportional to that of its DBA"

  8. #8
    Join Date
    Feb 2003
    Location
    INDIA
    Posts
    96
    Benazir,

    I also agree with Sanjay, knowing each user IP would be complicated or difficult, you can get the machine info from v$session, from that you may track IP address from your network.
    Dilip Patel
    OCP 8i

    Catch me online at Yahoo: ddpatel256

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width