LOGOFF TRIGGER for getting IP ADDRESS ETC

Hi Every one,

Now I am using the following logon trigger in order to get the required information:

CREATE OR REPLACE TRIGGER trg_session_info  AFTER LOGON ON DATABASE     DECLARE

     session_ids      VARCHAR2(30);
     work_stations    VARCHAR2(30);
     host_names        VARCHAR2(30);
     database_servers  VARCHAR2(30);
     db_domains        VARCHAR2(30);
     auth_types       VARCHAR2(30);
     ip_addrs         VARCHAR2(30);
     network_protocols VARCHAR2(30);


    

   BEGIN     SELECT sys_context ('USERENV', 'SESSIONID') INTO session_ids     FROM dual;

    SELECT SYS_CONTEXT('USERENV','OS_USER') INTO work_stations     FROM dual;

    SELECT SYS_CONTEXT('USERENV','HOST') INTO host_names    FROM dual;

   SELECT SYS_CONTEXT('USERENV','DB_NAME') INTO database_servers     FROM dual;

    SELECT SYS_CONTEXT('USERENV','DB_DOMAIN') INTO db_domains     FROM dual;

     SELECT sys_context ('USERENV', 'AUTHENTICATION_TYPE')INTO auth_types

    FROM dual;

     SELECT sys_context ('USERENV', 'IP_ADDRESS') INTO ip_addrs     FROM dual;

     SELECT SYS_CONTEXT('USERENV','NETWORK_PROTOCOL') INTO network_protocols

     FROM dual;

 INSERT INTO session_info(session_id,client_name,logon_date,  work_station,host_name,database_server,db_domain,auth_type,ip_address,network_protocol)  VALUES (session_ids,user,sysdate,work_stations ,host_names ,database_servers,db_domains,
  auth_types,ip_addrs, network_protocols);    

   commit;

   END;
/

I also use the LOGOFF trigger in order to get rid of the session information when they logoff.

But it is not useful if the session terminated abnormally,because the session information can't delete from the session_info table.

 Is there another approach in order to delete the session information from the table session_info, when they termniated abnormally? Following is the LOGOFF TRIGGER which I am using now:

CREATE OR REPLACE TRIGGER logoff_session  BEFORE LOGOFF ON DATABASE     DECLARE

     session_idss      VARCHAR2(30);
     

BEGIN     SELECT sys_context ('USERENV', 'SESSIONID') INTO session_idss     FROM dual;

DELETE from session_info where session_id=session_idss; commit;  

   commit;

   END;
/

Could you please give your comment on the above problem that how to delete the session information from the table when it terminated abnormally.

In addition,I like to inform you that I am using Oraexcel for getting the information available on it.

I am using the following query in order to populate an excel sheet:

select SESSION_ID,CLIENT_NAME,to_char(logon_date, 'DD/MM/YY HH24:MI:SS')LOGON_TIME,
WORK_STATION,HOST_NAME,DATABASE_SERVER,DB_DOMAIN, AUTH_TYPE,IP_ADDRESS,NETWORK_PROTOCOL
  from session_info;

So my table "session_info" populates the required information into an Excel sheet,whenevere clients logon to database. But now I want that this information ,which is available on Oraexcel sheet,would be showing a Graphic-visualization of the Network from an Oracle perspective.
The graphical visualization will show the Database server and the connected clients and below the clients show the active sessions.

Could someone please give me idea how should I do that?

THANKS WITH BEST REGARDS DAVID Received on Mon Feb 23 2004 - 08:28:24 CST