Try putting a
dbms_session.close_database_link('utilities_itport02_dblink');
after the insert. also aren't we missing a commit?? I'd also make this a autonomous transaction ...
Raj
-------------------------------------------------------------
Rajendra dot Jamadagni at espn dot com
Any views expressed here are strictly personal.
QOTD: Any clod can have facts, having an opinion is an art !!
-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, March 11, 2003 10:54 AM
To: Multiple recipients of list ORACLE-L
Subject: AFTER database logon trigger keeps sessions open
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 [EMAIL PROTECTED]
(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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
********************************************************************This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*********************************************************************2
