Note that when finding sessionid, querying from
v$mystat where rownum=1 is faster than selecting sys_context from dual, because
dual access requires full table scan on it, which means 3-5 LIOs, depending on
version, buffer pins, latches etc..
Even when you don't select sys_context from dual,
but assign its return value directly to a variable, a select from sys.dual
is executed in background.
In my simple tests, getting sid from v$mystat is
about 50% faster than using sys_context with regular dual table. Sometimes I've
done an optimization by creating a vdual view pointing to x$dual in sys schema,
granting select right to users and making a *local* dual synonym under relevant
schemas. That way, when select from dual is done, local synonym is resolved
first and fast dual can be used. This is much more safer than replacing original
dual with a view.
In pre-9i versions you don't have x$dual, then you
can do similar trick using index organized tables or a regular table with
one column and unique index on it.
Tanel.
----- Original Message -----
Sent: Friday, December 19, 2003 9:59
PM
Subject: Re: Who fired the trigger
You can identify the user via
sys_context ('userenv', 'sessionid') and raise an exception,
or just exit the code block.
Jared
To All,
I'm feeling in a LAZY mood this afternoon so I'm going to ask the list if
someone has an answer to this. Otherwise I guess it will wait till
Monday.
We have
a before update trigger on a table to prevent assemblies on the line from
being unscrapped. But we also have a need o periodically unscrap stuff.
The question is can a trigger recognize who fired it & abort if that
is a particular user?
Dick Goulet
Senior Oracle DBA
Oracle
Certified 8i DBA
--
Please see the official ORACLE-L FAQ:
http://www.orafaq.net
--
Author: Goulet, Dick
INET:
DGoulet@vicr.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@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: Tanel Poder
INET: tanel.poder.003_at_mail.ee
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 Sat Dec 27 2003 - 14:04:24 CST
