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 [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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Khedr, Waleed
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).
