Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question, Arum Kusuma .

Asked: May 02, 2000 - 1:21 pm UTC

Last updated: June 09, 2011 - 12:00 pm UTC

Version:

Viewed 10K+ times! This question is

You Asked

could you tell me how put the information about current
username,session id,terminal on variable

like select user into v_user from dual

than i can manipulate in procedure or function or trigger.



and Tom said...



The query is:

select <whatever you want> from v$session where audsid=userenv('sessionid');

that gets the data for the currently logged in user from the v$session table. In order to create a trigger or procedure that references v$session, you need to:

SQL> grant select on v_$session to <owner of the procedure|trigger>

when connected as SYS. v_$session is a view and can be granted on. v$session is a synonym for that view (and hence cannot be granted on, thats why you grant on v_$session, not v$session). If you want to know why you need this grant, see </code> http://asktom.oracle.com/Misc/RolesAndProcedures.html <code>

Some people create a view:

create view my_session_info
as
select * from V$session where audsid = userenv('sessionid');

grant select onmy_session_info to public;


as SYS so everyone can see their session. You would be able to use that grant and view in your procedure|trigger as well.



Rating

  (36 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

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.

Tom Kyte
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.


Tom Kyte
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.

Tom Kyte
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.

Tom Kyte
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 !

Tom Kyte
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).

Tom Kyte
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



Tom Kyte
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 :(

Tom Kyte
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

Tom Kyte
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.



Tom Kyte
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!

Tom Kyte
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

It does not seem to be documented, but in 10g (not sure in 9i), you can get the SID by using SYS_CONTEXT('USERENV', 'SID'), or just USERENV('SID').

This is the 10g SYS_CONTEXT link in the docs where it is not mentioned.
</code> http://download-west.oracle.com/docs/cd/B14117_01/server.101/b10759/functions150.htm <code>

Tom,
Do you know if this is documented anywhere?


Tom Kyte
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

Tom Kyte
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?

Tom Kyte
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



Tom Kyte
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

Tom Kyte
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.

Tom Kyte
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.

Tom Kyte
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.

Tom Kyte
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

Tom Kyte
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)
Tom Kyte
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?

Tom Kyte
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

Tom Kyte
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.
Tom Kyte
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?
Tom Kyte
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.
Tom Kyte
June 09, 2011 - 12:00 pm UTC

you don't know, and neither do I because no such thing exists.

that update is as valid, proper and good as any other update.


maybe you'll be interested in this:

https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:3512966500346125917

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

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library