Re: finding ip address of the connected sql session in database

Hi Edwin,

>> Is there a way to find IP address of the connected sql sessions of
the database.

If you are on Oracle 8i upwads, you can get the IP address as part of an 'AFTER LOGON' trigger. The 9.2 docs gives details in Chapter 16 Working With System Events of the Application Developres Guide - Fundamentals.

<quote apologies for formatting>

Event Attribute Functions

When a trigger is fired, you can retrieve certain attributes about the event that fired the trigger. Each attribute is retrieved by a function call.

Notes:

• To make these attributes available, you must first run the CATPROC.SQL script.
• The trigger dictionary object maintains metadata about events that will be published and their corresponding attributes.
• In earlier releases, these functions were accessed through the SYS package. We recommend you use these public synonyms whose names begin with ora_.

      Table 16-1 System Defined Event Attributes  
      Attribute 	Type 	Description 	Example

      ora_client_ip_address

      	

      VARCHAR2

      	

      Returns the IP address of the client in a LOGON event, when the
underlying protocol is TCP/IP
      	

      if (ora_sysevent = 'LOGON')
        then addr := 
      ora_client_ip_address;
      end if;

</quote>

HTH Cheers,
Norman.

Norman Dunbar.
Contract Oracle DBA.
Rivers House, Leeds.

Internal : 7 28 2051
External : 0113 231 2051

Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else.

We have checked this email and its attachments for viruses. But you should still check any attachment before opening it.

We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes.

If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk

--
http://www.freelists.org/webpage/oracle-l