| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: 8.1.6: possible to set role in db's logon trigger?
Woah--free code! A thousand thanks--this looks really close to what I'd
like to do. If I can wrestle some extra privs on our test db I'll report
back as to whether I was able to get this going on 8.1.6.
Thanks also to Lisa & Thomas for responding.
Cheers,
-Roy
Roy Pardee
Programmer/Analyst
SWFPAC Lockheed Martin IT
Extension 8487
-----Original Message-----
Sent: Monday, January 13, 2003 9:05 AM
To: Multiple recipients of list ORACLE-L
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
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: Pardee, Roy E
INET: roy.e.pardee_at_lmco.com
Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting servicesto: 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 - 12:24:16 CST
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
 |
 |