Oracle-L: RE: AFTER database logon trigger keeps sessions open

From: Khedr, Waleed <Waleed.Khedr_at_FMR.COM>
Date: Tue, 11 Mar 2003 08:29:00 -0800
Message-ID: <F001.0056620C.20030311082900@fatcity.com>

You can use: execute immediate 'alter session close database link utilities_itport02_dblink';
After you're done with the insertion

Regards,

Waleed

-----Original Message-----
Sent: Tuesday, March 11, 2003 10:54 AM
To: Multiple recipients of list ORACLE-L

Hi All,

I have a after database logon trigger on server/instance 1. All it does is insert a record into instance on server2 via a database link. This creates a session on server2. So every user who logs on to server1 a session is created on server2. The session does not go away. Does anyone know why it does not after the user logs on?

Below is after logon trigger

DECLARE

str varchar2(200); CURSOR temp_rec IS SELECT user AS user_name, NVL(UPPER(SUBSTR(osuser,1,30)),'SERVER') AS os_user_name, RTRIM(NVL(SUBSTR(machine,instr(machine,'\') +1,12),'SERVER'),CHR(0)) AS machine_name, sid AS session_id, serial# AS serial_no, SYSDATE AS logon_time, SYS_CONTEXT('userenv','ip_address') AS ip_address, NVL(SUBSTR(program,1,64),'INTERNAL') AS program_name, i.instance_name AS instance_name, i.host_name AS host_name, i.version AS version FROM v$session s,v$instance i WHERE s.username = user AND s.logon_time = (SELECT MAX(x.logon_time) FROM v$session x WHERE x.username = user); BEGIN FOR rec IN temp_rec LOOP INSERT INTO login_history_at_utilities_itport02_dblink (user_name, os_user_name, machine_name, session_id, serial_no, logon_time, ip_address, program_name, instance_name, host_name, version) VALUES (rec.user_name, rec.os_user_name, rec.machine_name, rec.session_id, rec.serial_no, rec.logon_time, rec.ip_address, rec.program_name, rec.instance_name, rec.host_name, rec.version); END LOOP; EXCEPTION WHEN OTHERS THEN NULL; END;

--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author:
INET: Rick_Cale_at_teamhealth.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author: Khedr, Waleed
INET: Waleed.Khedr_at_FMR.COM

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------