But how to update this view
Edgars, March 11, 2002 - 7:45 am UTC
Hello Tom!
Is there a possibility to update fields in v$session table. For example suppose I want to set terminal field to SYS_CONTEXT( 'USERENV', 'IP_ADDRESS' ) because my client app set's this field to 'unknown', this possibility could be very useful.
March 11, 2002 - 8:02 am UTC
You can set the following fields:
CLIENT_INFO
MODULE
ACTION
using the dbms_application_info package. You cannot modify any of the other fields.
may not work properly with jobs
Alex Rakhalski, March 11, 2002 - 9:03 am UTC
I think it may not work properly with jobs, because within job USERENV('SESSIONID') returns 0 (as for all other system processes). If you have a trigger with such logic, table probably will be non-updateable from any job. I had a problem like this on Oracle 8.0.5.
March 11, 2002 - 9:45 am UTC
you can use
select * from v$session where sid = ( select sid from v$mystat where rownum=1 )
as well -- that'll work from any environment.
Getting sessionid within dbms_jobs
Marc, March 26, 2002 - 9:57 am UTC
The following query
select audsid
from v$session
where sid = (select sid from v$mystat where rownum=1);
shows the same value as select userenv('sessionid') from dual. But when I submit this query within dbms_job, the audsid is 0. For logging reasons I need the session id. My database version is 8.1.6.3.0.
Could you tell my another way to select the session id within dbms_jobs. Many thanks.
March 26, 2002 - 11:25 am UTC
There is no "audsid" for the job. You really should be using the SID,SERIAL# (thats the UNIQUE id for the session. I have no idea what you might be using the audsid for for logging)
select userenv('sessionid') under control of dbms_job is zero
Marc, March 26, 2002 - 5:17 pm UTC
Hi Tom
In my eventlog I write for example the current oracle_user, the os_user, the sysdate and the session_id. This goes fine for procedures started interactive within SQL*Plus. When I submit the same stored procedure within dbms_job, the session_id (= audsid in v$session) is always 0. And your given workaround 'select audsid from v$session where sid = ( select sid from v$mystat where rownum=1 );' is also 0 unter control of dbms_job. Thanks for your feedback.
to previous reviewer
Alexander Rakhalski, March 27, 2002 - 1:41 am UTC
Hi, Tom!
1. I has forgotten to rank your last answer on my question. It's really great! I hasn't thought that v$mystat can be useful in application development...
2. I want to notice previous reviewer that question was "how get my ROW from v$session", not "how get my AUDSID from v$session". There are many other useful fields in v$session - SID, SERIAL# and so on. And they <>0 within job. You can consider using them.
March 27, 2002 - 8:05 am UTC
that was my point too -- the audsid, i don't know why you would need that one. sid, serial#, transaction id (from another table) maybe....
audsid always distinct in v$session ?
Robert, November 27, 2002 - 6:32 pm UTC
>> SELECT osuser INTO l_osuser FROM v$session
>> WHERE audsid = USERENV('SESSIONID')
so I will never get hit with ORA-01422 for the above code ?
thanks, Happy Thankgiving !
November 27, 2002 - 6:37 pm UTC
should be OK
select * from v$session where sid = ( select sid from v$mystat where rownum=1)
is my "new preferred" method. I've found userenv('sessionid') and audsid to not be set in certain event triggers. v$mystat never fails.
sys_context against v$session
Darko, November 28, 2002 - 12:56 am UTC
Is using SYS_CONTEXT faster and more scalable solution then selecting from v$session (for info which is available in both cases).
November 28, 2002 - 7:56 am UTC
I would use sys_context if it met my needs for then I have no need to grant select on v_$session (more available to more users)
Uniqally identifying session
atul, August 29, 2003 - 2:11 am UTC
Hi,
My requirement is,I want to identify my SESSION uniqually out of many session.
My problem is,I can identify easily if logged in from unix(character mode),but how to get it if logged in from web?
Thanks.
Atul
August 29, 2003 - 9:09 am UTC
why does "character mode" differ from "the web" for you?
Oracle cannot tell the difference.
ora-01422
A reader, September 02, 2003 - 6:18 am UTC
Hi
I have following code
create or replace procedure test
as
l_cnt number;
begin
select count(*)
into l_cnt
from v$session where audsid=USERENV('sessionid');
insert into emp(empno) values(l_cnt);
end;
/
when I submit to a job and run it I see this
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ----------------- ---------- ---------- ----------
7499 ALLEN SALESMAN 7698 19810220 00:00:00 1600 300 30
7521 WARD SALESMAN 7698 19810222 00:00:00 1250 500 30
7654 MARTIN SALESMAN 7698 19810928 00:00:00 1250 1400 30
7698 BLAKE MANAGER 7839 19810501 00:00:00 2850 30
7782 CLARK MANAGER 7839 19810609 00:00:00 2450 10
7839 KING PRESIDENT 19811117 00:00:00 5000 10
7844 TURNER SALESMAN 7698 19810908 00:00:00 1500 0 30
7369 SMITH CLERK 7902 19801217 00:00:00 800 20
7566 JONES MANAGER 7839 19810402 00:00:00 2975 20
7788 SCOTT ANALYST 7566 19821209 00:00:00 3000 20
7876 ADAMS CLERK 7788 19830112 00:00:00 1100 20
7900 JAMES CLERK 7698 19811203 00:00:00 950 30
7902 FORD ANALYST 7566 19811203 00:00:00 3000 20
7934 MILLER CLERK 7782 19820123 00:00:00 1300 10
13
How come there are 13 sessions...? In fact there is a note on Metalink that is can happen when using v$session and dbms_job. How so?
Also I can see sometime my job stops running
select LAST_DATE, next_date, broken, failures from user_jobs;
LAST_DATE NEXT_DATE B FAILURES
----------------- ----------------- - ----------
20030902 04:44:50 20030903 04:44:00 N 0
!date
Tue Sep 2 04:50:34 CEST 2003
How can we fix this? I dont see why this can happen :(
September 02, 2003 - 7:40 am UTC
use
select count(*) from v$session where sid = ( select sid from v$mystat where rownum = 1)
you'll find your audsid is 0 or something like that in a job. if you want your v$session row -- I'd use that query in all cases... jobs or not.
as for the "my job stops running"
a) make sure job_queue_processes > 0
b) make sure job_queue_interval is set reasonable (eg, if set to 10 minutes, your job might not run until 4:54:50
c) make sure all of the job queues are not currently running jobs (see a again)
d) make sure your job isn't actually RUNNING already (dba_jobs_running)
e) make sure the clock on your system that you did the !date from is the same clock as on the server!! your time might not be anywhere close to their time
Audsid
OracleO, December 24, 2003 - 12:26 am UTC
Hi Tom,
You have used the column audsid from v$session. What actual is the role of this column. The reference manual just says that its auditing Session ID. Is it alloted by oracle for auditing the session ?
Thanks,
OracleO
December 24, 2003 - 9:59 am UTC
I've started to prefer this view:
select * from v$session where sid = ( select sid from v$mystat where rownum=1 );
that works in all environments. the audsid column is populated via a sequence and for normal sessions is the same as "userenv('sessionid')" -- but for some background sessions -- it is not set (it comes back as "0") making the view not work.
So, I'd just ignore "audsid" for now and use SID.
Just what I needed
Evan, January 30, 2004 - 1:03 pm UTC
As always, you were right on the mark. The follow ups were helpful as well.
V$MYSTAT
Ramakrishna, March 19, 2004 - 2:03 am UTC
Can we assume that V$MYSTAT will always have atleast one row? So that the query
"Select SID From V$MYSTAT where ROWNUM = 1" does not fail.
March 19, 2004 - 8:59 am UTC
yes (and the query would not "fail", it would return zero records which is not "failure", just an empty set)
but not to worry -- you will always have a fixed number of stats in each release and the number is much larger than 1.
from a job or not from a job?
Andres, October 10, 2004 - 3:46 pm UTC
Is there any way to tell (for the active session itself, inside a program) whether the session was initiated from a job or not?
The best i could think of is
select count(*) from dba_jobs_running where sid = ( select sid from v$mystat where rownum=1 );
but it seems pretty slow compared to the select from v$session (0.45 seconds vs 0.07 seconds). And the question - could the audsid=0 condition be used as a sign, that this session started from a job (i presume not?) - select count(*) from v$session where sid = ( select sid from v$mystat where rownum = 1) and audsid = 0). And if yes, is it the same in 10g (i'm currently using 9iR2)?
Thank you!
October 10, 2004 - 6:43 pm UTC
ops$tkyte@ORA9IR2> create table t ( msg varchar2(255) );
Table created.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create or replace procedure p( p_msg in varchar2 )
2 as
3 l_job number;
4 begin
5 select <b>nvl( sys_context( 'userenv', 'fg_job_id' ), sys_context('userenv','bg_job_id') )</b>
6 into l_job
7 from dual;
8
9
10 insert into t values ( p_msg || ': "' || l_job || '"' );
11 commit;
12 end;
13 /
Procedure created.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> exec p( 'run in foreground ' );
PL/SQL procedure successfully completed.
ops$tkyte@ORA9IR2> variable n number
ops$tkyte@ORA9IR2> exec dbms_job.submit( :n, 'p( ''run as job using dbms_job.run'' );' );
PL/SQL procedure successfully completed.
ops$tkyte@ORA9IR2> print n
N
----------
9
ops$tkyte@ORA9IR2> commit;
Commit complete.
ops$tkyte@ORA9IR2> exec dbms_job.run( :n );
PL/SQL procedure successfully completed.
ops$tkyte@ORA9IR2> exec dbms_job.submit( :n, 'p( ''run as job using job queue'' );' );
PL/SQL procedure successfully completed.
ops$tkyte@ORA9IR2> print n
N
----------
10
ops$tkyte@ORA9IR2> commit;
Commit complete.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> declare
2 l_cnt number;
3 begin
4 loop
5 select count(*) into l_cnt from user_jobs where job = :n;
6 exit when l_cnt = 0;
7 dbms_lock.sleep(1);
8 end loop;
9 end;
10 /
PL/SQL procedure successfully completed.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select * from t;
MSG
-------------------------------------------------------------------------------
run in foreground : "0"
run as job using dbms_job.run: "9"
run as job using job queue: "10"
<b>the nvl isn't technically 'needed' in 9i -- but when they fix the fg/bg variable in sys_context it will be in 10g, so might as well have it there (in 10g, you do need it as the job id will be NULL from fg when run in the bg...)</b>
A reader, January 31, 2005 - 6:03 am UTC
SID
A reader, January 31, 2005 - 5:31 pm UTC
January 31, 2005 - 6:06 pm UTC
v$instance has it as well -- just the "instance" name. don't know if it is documented or not, if you read about sys_context and it isn't there -- then it isn't documented.
and it isn't returning my sid for me, not sure what it is.
ops$tkyte@ORA10G> select instance_name, sys_context('userenv', 'sid' ), userenv('sid') from v$instance;
INSTANCE_NAME
----------------
SYS_CONTEXT('USERENV','SID')
-------------------------------------------------------------------------------
USERENV('SID')
--------------
ora10g
147
147
SID
A reader, January 31, 2005 - 7:30 pm UTC
I get the same SID as v$mystat.
SELECT
SYS_CONTEXT('USERENV', 'SID') sys_ctx_sid,
(SELECT sid FROM v$mystat WHERE ROWNUM = 1) v_my_stat_sid
FROM
dual
/
SYS_CTX_SID V_MY_STAT_SID
------------- -------------
129 129
January 31, 2005 - 8:29 pm UTC
ahh (if i would have paged up, i might have made that connection myself :)
Back to AUDSID...
Brad Goodwin, April 28, 2005 - 3:16 am UTC
Fine Grained Auditing includes the ability to have a "handler" procedure that is called upon the audit event. I want the handler procedure to have access to the same SESSION_ID that FGA stores in its table DBMS_FGA_AUDIT_TRAIL. The value FGA uses is the same value as SYS_CONTEXT('USERENV','SESSIONID')
but!
I need my handler procedure to have access to that same value. Unfortunately, my handler procedure always returns 0 when it calls SYS_CONTEXT('USERENV','SESSIONID') or SELECT audsid INTO l_session_id FROM v$session WHERE sid = ( SELECT sid FROM v$mystat WHERE rownum=1 ).
Is there any way for the called procedure to get access to the "true" session id?
April 28, 2005 - 7:52 am UTC
what does
select sys_context( ... ) from dual;
return (select it from dual, don't assign the function)
Brad Goodwin, April 28, 2005 - 3:47 pm UTC
Selecting sys_context(...) from dual returns 0.
The FG audit record seems to know about the session_id of the user whose action is being audited, as it records it in the audit record.
But my procedure, which the FG audit policy calls, cannot get the session_id (AUDSID) via these methods. I was hoping to examine the code of the FGA_ADD_POLICY package to see if there are additional parameters or options, but it is wrapped.
SID and SERIAL info
jean marc (beginner), May 24, 2005 - 7:11 am UTC
thanks for this help, I was looking for something like that for days now.
I intend to kill a query that takes too much time to execute.
the query is executed via web(php) and so, there is no means to do a Ctrl-C as in SQLplus.
By storing the SID/Serial, the user will be able to launch a "ALter system kill session 'SID,SERIAL'" in order to kill the ongoing query.
It does not look like a clean way, but it may work.
v$session
Suresh, August 04, 2005 - 7:53 am UTC
Tom,
Sorry for posting this query here as this site was always busy.
Let me start with a piece of existing architecture of our application. Whenever any invoice needs to be printed from Forms, a record with all relevant details is entered in one of the tables. There is a Pro*C program which runs in the background that keeps checking the tables continuously and sends the details to the printer to get it printed. This is all working fine.
This Pro*C program which I mentioned above is invoked through Microsoft scheduler. Say, after couple of hours, this Proc program becomes dormant (does not pick up any entries from the table for printing) for reasons unknown, but still the scheduler shows the status as "Active".
There are 2 issues here:
1.) There should be only one instance of this Pro*C program running. i.e if there is one instance of this program running, even if we try to invoke this program again through scheduler, it should not run.
2.) If the first instance of program that is running is "dormant" for any reason, we should be able to close this through the scheduler and restart again without any problem.
Solutions:
For First point, I thought of putting a check/fix in the Proc program by checking whether the V$Session entry for this ProC program is existing and active. If yes, even if we try start this proc program from Scheduler, program should not allow it to start (i.e) exit.
For Second Point, even if the existing v$session entry is "dormant", this first fix won't work, because there is still an entry in the v$session table. I read/understand from the oracle documents that there is no guarantee how long this v$session entry will stay in the table as it depends on the amount of rewind it has to do before deleting the entry.
My question is, is there a way around to solve this issue.
Thanks for your help and please let me know if you need more information.
Suresh
August 04, 2005 - 9:42 am UTC
... Sorry for posting this query here as this site was always busy ....
(and now you know why..... I get tons of these)
seems you have a problem with the "microsoft scheduler", whatever that might be.
issues with fg_job_id
Thomas Becker, December 13, 2005 - 8:06 pm UTC
Hi Tom,
I tried you example with the fg_job_id and it does not work for me:
We are running: Oracle 9.2.0.5.0 on HP Unix
The Code is:
create table t (msg varchar2(50));
create or replace procedure fg_job_id(p_text in varchar2)
as
begin
insert into t select p_text || sys_context( 'userenv', 'fg_job_id') from dual;
end;
/
BEGIN
fg_job_id('in plus: ');
end;
declare
n NUMBER;
begin
dbms_job.submit(n, 'fg_job_id( ''in job using run: '');' );
dbms_job.run(n);
end;
declare
n NUMBER;
begin
dbms_job.submit(n, 'fg_job_id( ''in job normal: '' );');
commit;
end;
... wait a moment ...
select * from t;
MSG
--------------------------------------------------
in job normal:
in plus: 0
in job using run: 140
Any idea ? Are there any setups wrong ?
Thanks
Thomas
December 14, 2005 - 7:52 am UTC
seems that they have "fixed" it.
fg_ is there when there is a "foreground" using run.
bg_ is there when run as a "background" by the job queue.
Hence my proposed method of getting the value, which you did not use....
ops$tkyte@ORA9IR2> create table t (msg varchar2(50));
Table created.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create or replace procedure job_id(p_text in varchar2)
2 as
3 l_job number;
4
5 begin
6 select nvl( sys_context( 'userenv', 'fg_job_id' ), sys_context('userenv','bg_job_id') )
7 into l_job
8 from dual;
9
10 insert into t values( p_text || l_job );
11 end;
12 /
Procedure created.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> BEGIN
2 job_id('in plus: ');
3 end;
4 /
PL/SQL procedure successfully completed.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> declare
2 n NUMBER;
3 begin
4 dbms_job.submit(n, 'job_id( ''in job using run: '');' );
5 dbms_job.run(n);
6 end;
7 /
PL/SQL procedure successfully completed.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> declare
2 n NUMBER;
3 begin
4 dbms_job.submit(n, 'job_id( ''in job normal: '' );');
5 commit;
6 end;
7 /
PL/SQL procedure successfully completed.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> exec dbms_lock.sleep(5);
PL/SQL procedure successfully completed.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select * from t;
MSG
--------------------------------------------------
in plus: 0
in job using run: 8
in job normal: 9
Alexander the ok, June 01, 2006 - 3:52 pm UTC
Tom,
Is v_$session session info or connection info? It almost looks like both. I need to be able to limit the number of connections each user has to our application to two. I was thinking of doing something like
select count (*)
into l_cnt
from v_$session v
where sys_context ('userenv', 'os_user') = v.osuser;
if l_cnt > 2 then
raise_application_error (-1, 'Only two connections allowed');
end if;
That sort of thing in a logon trigger but if v_$session is not strictly connections the numbers will be off and this approach probably won't work. Thanks a bunch.
June 01, 2006 - 4:19 pm UTC
I can be connected without having a session.
I can be connected once and have MULTIPLE sessions.
I can even have a session and NOT be connected!!
Autotrace is a simple way to see this:
ops$tkyte@ORA9IR2> select count(*) from v$session where username = user;
COUNT(*)
----------
1
ops$tkyte@ORA9IR2> set autotrace on statistics
ops$tkyte@ORA9IR2> select count(*) from v$session where username = user;
COUNT(*)
----------
2
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
379 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
ops$tkyte@ORA9IR2> set autotrace off
ops$tkyte@ORA9IR2> select count(*) from v$session where username = user;
COUNT(*)
----------
1
Autotrace creates a new session on the existing connection in order to "watch your session from another session"
Alexander the ok, June 01, 2006 - 4:29 pm UTC
So how should I proceed for this? I still do not know what is contained within v$session.
It's trickier than it needs to be because our app connects to the database all through the same user. Then we have a table that has user/password stuff stored in it.
June 01, 2006 - 5:11 pm UTC
v$session contains a record for every session a user has. That is all.
if you use a single account, v$session is going to be "something less than useful for you!"
You tell me - looking at the database (given you use a single account) what tells you WHAT session is used by WHAT user?
Alexander the ok, June 01, 2006 - 6:04 pm UTC
Well, there's all kinds of stuff in v$session, osuser, machine, terminal...the only thing that is the same for everyone is schemaname. I thought maybe some combination of other things would get it for me.
Believe me I did not choose this design, stuck with it unfortunately.
I was exploring the possibilities at the database level. It sounds like this may have to be an application side solution though from your response. Thanks.
June 01, 2006 - 6:25 pm UTC
exactly
IF the application is using the same name for everyone
and the application is running on a middle tier server
then everone in the database looks to us like "they are the same from the same machine"
Identify sql within stored procedure
Sanji, July 06, 2006 - 4:52 pm UTC
Hello Tom,
We are on 10g release 1, HP-UX 11i.
I execute the following query to identify a resource intensive sql.
select sql_text from v$sqlarea
where (address,hash_value) =
select sql_address, sql_hash_value
from v$session
where sid=&sid);
The output is
SQL_TEXT
-----------------------------------------------------------------
Declare ReturnValue number; Begin ReturnValue := amartaScheduler('Monthiversary Processing');
IF ReturnValue != 0 THEN dbms_standard.raise_application_error(-20224+ReturnValue, 'Scheduler Error'); END IF; End;
Apart from running a trace on this session, is there any other method that can show me the recursive sqls in the stored procedure. I even tried v$open_cursor.
Thanks
Sanji
July 08, 2006 - 10:31 am UTC
probably - using the new program_id column (which *might* be sufficient, but might return false positives and or miss some)
There is also the fact that, well, you can read the code out??
(don't you really wish that was just:
begin amartaScheduler( 'Monthiversay Processing' ); end;
I mean - why have something return a "return code", that is sooo 1970's. It truly seems that amartaScheduler itself SHOULD RAISE AN EXCEPTION upon error!!!)
say you have procedures:
ops$tkyte@ORA10GR2> create or replace procedure p1
2 as
3 begin
4 for x in ( select /*+ look for me */ * from dual d1 )
5 loop
6 null;
7 end loop;
8 for x in ( select /*+ look for me */ * from dual d2 )
9 loop
10 null;
11 end loop;
12 end;
13 /
Procedure created.
ops$tkyte@ORA10GR2> create or replace procedure p2
2 as
3 begin
4 for x in ( select /*+ look for me */ * from dual d3 )
5 loop
6 null;
7 end loop;
8 end;
9 /
Procedure created.
ops$tkyte@ORA10GR2> create or replace procedure p3
2 as
3 begin
4 for x in ( select /*+ look for me */ * from dual d1 )
5 loop
6 null;
7 end loop;
8 end;
9 /
Procedure created.
Note that p3 has SQL identical to P1...
If you:
ops$tkyte@ORA10GR2> exec p3
PL/SQL procedure successfully completed.
ops$tkyte@ORA10GR2> exec p1
PL/SQL procedure successfully completed.
ops$tkyte@ORA10GR2> exec p2
PL/SQL procedure successfully completed.
ops$tkyte@ORA10GR2> select sql_text from v$sql where program_id = (select object_id from user_objects where object_name = 'P1' );
SQL_TEXT
-------------------------------------------------------------------------------
BEGIN p2; END;
SELECT /*+ look for me */ * FROM DUAL D2
The BEGIN p2; end; is a "false positive" (the program ID was left hanging out from the first).
The FROM DUAL D3 is "missing" (since P3 got it first).
But if you:
ops$tkyte@ORA10GR2> -- exec p3
ops$tkyte@ORA10GR2> exec p1
PL/SQL procedure successfully completed.
ops$tkyte@ORA10GR2> -- exec p2
ops$tkyte@ORA10GR2> select sql_text from v$sql where program_id = (select object_id from user_objects where object_name = 'P1' );
SQL_TEXT
-------------------------------------------------------------------------------
SELECT /*+ look for me */ * FROM DUAL D2
SELECT /*+ look for me */ * FROM DUAL D1
select sql_text from v$sql where program_id = (select object_id from user_objec
ts where object_name = 'P1' )
You get another false postive, but do get to see the sql executed by P1...
and so on.
userenv( 'sessionid' ) vs. mystat
Max, September 11, 2007 - 1:06 pm UTC
One of the things I appreciate most on your ***GREAT*** site is that you always provide proof for your statements.
Could you please elaborate on those cases you 've mentioned at the very beginning of this thread where both approaches, using userenv/mystat, do not return the same outcome?
(I'd like to apply those tests to our systems/applications where we might gain very much from using the built-in function instead of the dictionary view)
September 15, 2007 - 3:47 pm UTC
you'll need to be a bit more precise in your question.
userenv and v$mystat are not comparable things - one is a function, the other a view.
Not sure what you mean - the thread above seems clear, there are times (eg: the job queues were explicitly mentioned) where you cannot find your v$session row using userenv('sessionid') - you would use sid from v$mystat instead.
Max, September 25, 2007 - 12:23 am UTC
Maybe I just didn't get your statement:
<quote>
I've found userenv('sessionid') and audsid to not be set in certain event triggers.
</quote>
above right. Can you confirm that:
select userenv( 'sessionid' ) from dual
and
select b.audsid from v$session b where b.sid = ( select a.sid from v$mystat a where rownum = 1 )
would always return the SAME -- although both statements could return 0 (which is what you mean by "not to be set") in certain cases?
September 26, 2007 - 9:07 pm UTC
and zero is not valid, there could be many '0' in v$session at the same time
hence, it is not usable.
Relating Frontend User in v$session
V.Hariharaputhran, December 08, 2009 - 1:13 am UTC
As you said
" IF the application is using the same name for everyone
and the application is running on a middle tier server
then everone in the database looks to us like "they are the same from the same machine"
I am feeling the heat of the exact above scenario at the moment, Every user from frontend is connected to the database with a single username, Apart from SID and Serial# rest of the information like OSUSER, MACHINE TERMINAL are all identical.
Now I am unable to identify a session to enable tracing, when ever a frontend user complaints about the database performance.
Is there really way to identify the session to enable tracing with the above specified identical issues.
I feel like it is totally an application design issue?, What do you say.
Appreciate your splendid services to the oracle community.
Regards
puthranv
December 10, 2009 - 8:15 am UTC
... Is there really way to identify the session to enable tracing with the
above specified identical issues. ...
if and only if the application developers had a brain during development.
if and only if the application developers had a heart during development (thinking of you, the DBA staff)
if and only if the application developers had a desire to maintain their application after deployment.
if and only if the application developers added a simple, trivial, tiny, easy call to dbms_session.set_identifier.
Then you could simply use dbms_monitor to trace that virtual session - across connections in connection pools, across whatever. The application would never have to turn trace on or off - they would just have to IDENTIFY THEMSELVES TO THE DATABASE.
Then - then you would be set.
But I'll assume the developers did not do that. In which case - they all look the same to us, we cannot tell them apart and neither can you
Relating Frontend User in v$session
V.Hariharaputhran, December 10, 2009 - 11:39 pm UTC
Excellent, thanks for your reply and valuable time.
Regards
puthranv
Additional Note Re RAC
Bill Bennett, January 14, 2010 - 9:05 am UTC
When Using RAC the mentioned SQL actually returns multiple rows to get the details for your login session not the temporary sessions (I was after sid,serial#) you can use
where audsid = sys_context ('USERENV', 'SESSIONID' and ownerid=2147483644;
Getting the name of the program
Javed, August 30, 2010 - 2:29 pm UTC
So i did run this "select program from v$session where sid = ( select sid from v$mystat where rownum=1)"
from within a pl/sql program (mysql.sql) running under sqlplus for windows. This always returns the name sqlplusw.exe not the name of the pl/sql program (mysql.sql). I need to get the mysql.sql not sqlplusw.exe.
After reading all the comments here I did not see this addressed, ur suggestion will be appreciated. Thanks.
September 09, 2010 - 11:36 am UTC
your 'file' mysql.sql is not a program, the program is in fact sqlplusw.exe. mysql.sql is just a script.
I cannot find "ur" right now - so... sorry I guess? Unless you want me to answer? I'm confused...
if your script sets appinfo on, the name of the script would be available via sys_context as follows:
ops$tkyte%ORA10GR2> !cat mysql.sql
set appinfo on
select sys_context( 'userenv', 'module' ) from dual;
ops$tkyte%ORA10GR2> @mysql
ops$tkyte%ORA10GR2> set appinfo on
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select sys_context( 'userenv', 'module' ) from dual;
SYS_CONTEXT('USERENV','MODULE')
----------------------------------------------------------------------------------------------------
01@ mysql.sql
nice feature!
Sokrates, September 09, 2010 - 3:03 pm UTC
where clause
sivakumar, June 08, 2011 - 9:49 pm UTC
how to check whether where clause is present in a query or not?
June 09, 2011 - 9:33 am UTC
You'd have to parse it and look for it, we really don't care if there is one or not - it is not anything special to us, we wouldn't flag it as "abnormal" or exceptional.
where clause
sivakumar, June 09, 2011 - 10:31 am UTC
how to check whether where clause is present in a query or not?
Followup June 9, 2011 - 9am Central time zone:
You'd have to parse it and look for it, we really don't care if there is one or not - it is not anything special to us, we wouldn't flag it as "abnormal" or exceptional.
i meant when i type a update query a trigger should raise exception if it does not have where clause
eg. update emp set salary=10000;
trigger should raise exception that 'missing where clause'
pl help. i dono to do.
sessionifno
A reader, October 29, 2011 - 9:48 pm UTC
Log wrong USER ID
ChauHuynh, June 13, 2014 - 10:41 am UTC
Hello Tom,
I'm facing with logging wrong USER ID issue.
In java, developer use function dbms_application_info.SET_CLIENT_INFO('userid|machine IP') to put userid to v$session.client_info.
Transaction timeout is 20 mins.
In Oracle, I log USER ID who perform the traction by calling this function.
function get_web_user
return varchar2
is
begin
if g_web_user is null then
select coalesce(substr(client_info, 1, instr(client_info, '|')-1), sys_context('userenv', 'session_user'), user) into g_web_user
from v$session
where sid = (select sid from v$mystat where rownum = 1);
end if;
return g_web_user;
end;
I got a report that the logged USER ID is not the USER who perform the transaction from the customer with captured screen.
I did many testing on different browsers different users at time same on UAT, but can not create that error.
You have any ideas that how error can happen?
Many thanks,
Huynh