RE: 8.1.6: possible to set role in db's logon trigger?

From: Jamadagni, Rajendra <Rajendra.Jamadagni_at_espn.com>
Date: Mon, 13 Jan 2003 09:04:36 -0800
Message-ID: <F001.0052DC91.20030113090436@fatcity.com>

Roy,

this is in 9202 ... check the custom code for hash joins ... it has been working fine for us for > 2 months ...

CREATE OR REPLACE TRIGGER "SYSTEM".DBT_USERS_LOGON AFTER LOGON ON DATABASE
--

DECLARE
CURSOR cur_sess IS
SELECT *
FROM v$session
WHERE AUDSID = USERENV('SESSIONID')
AND USERNAME NOT IN ('HEARTBEAT');
--

recSess cur_sess%ROWTYPE;
--

PRAGMA AUTONOMOUS_TRANSACTION;
--

BEGIN
OPEN cur_sess;
FETCH cur_Sess INTO recSess;
CLOSE cur_sess;
--

INSERT INTO USER_LOGON_AUDIT
(SESS_AUDSID, DB_USER, OS_USER, TERMINAL, PROGRAM, TRIGGER_EVENT,LOGON_TIME, LOGOFF_TIME)
VALUES
(USERENV('SESSIONID'), UPPER(ora_login_user), UPPER(recSess.osuser), recSess.terminal, recSess.program, ORA_SYSEVENT, SYSDATE, NULL); COMMIT;
--

IF UPPER(ORA_LOGIN_USER) = 'AFF_QUERY' AND UPPER(recSess.machine) = 'IMAPPROD1' THEN EXECUTE IMMEDIATE 'alter session set hash_join_enabled=false'; END IF;
--

EXCEPTION
WHEN OTHERS THEN
NULL;
END DBT_USERS_LOGON;
/
Raj

---

Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com
Any opinion expressed here is personal and doesn't reflect that of ESPN Inc.

QOTD: Any clod can have facts, but having an opinion is an art!

--

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

Author: Jamadagni, Rajendra
  INET: Rajendra.Jamadagni_at_espn.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). Received on Mon Jan 13 2003 - 11:04:36 CST