| V$SESSION doesn't show sql_id of statement ran from PL\SQL Developer tool [message #566070] |
Tue, 11 September 2012 05:14  |
Andrey_R
Messages: 345
Registered: January 2012
Location: Israel
|
Senior Member |
|
|
Hi All,
I'm experiencing a problem with getting SQL_ID of a statement ran from pl\sql developer tool:
It just doesn't show up in my v$session. When I run it in SQL*Plus - sql_id is showing correctly.
My test case looks like this:
1) I open PL\SQL Developer tool, connect with user "ANDREY"
2) I run the statement select userenv('sid') from dual;
The result is: 106
3) Still in the PL\SQL Developer session, I run select * from test_table;
then, I open a SQL*Plus session, with
1) C:\...>sqlplus andrey@connstring
SQL*Plus: Release 11.2.0.2.0 Production on Tue Sep 11 12:53:03 2012
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Enter password:
Connected to:
Oracle Database 11g Release 11.1.0.7.0 - 64bit Production
SQL>
2) I try to find the sql_id of the statement executed from PL\SQL Developer tool with:
SQL> select sql_id, prev_sql_id
2 from v$session v
3 where v.sid = 106;
SQL_ID PREV_SQL_ID
------------- -------------
bydf32qgqdwdu
SQL> select sql_text from v$sql
2 where sql_id='bydf32qgqdwdu';
SQL_TEXT
------------------------------------------------------------------------
begin sys.dbms_output.get_line(line => :line, status => :status); end;
SQL>
I can't see my statement I know I executed "select * from test_table".
****************************************************************
****************************************************************
So, I try to do the same from two SQL*Plus sessions I open:
First session:
Microsoft Windows [Version 6.1.7601]
Copyright (c) 2009 Microsoft Corporation. All rights reserved.
C:\...>sqlplus andrey@connstring
SQL*Plus: Release 11.2.0.2.0 Production on Tue Sep 11 13:05:09 2012
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Enter password:
Connected to:
Oracle Database 11g Release 11.1.0.7.0 - 64bit Production
SQL>
SQL> select userenv('sid') from dual;
USERENV('SID')
--------------
122
SQL>
SQL>
SQL> select * from test_table where rownum<5;
ACOL BCOL
---------- --------------------
1210 some string1210
1211 some string1211
1212 some string1212
1213 some string1213
SQL>
Second session:
Microsoft Windows [Version 6.1.7601]
Copyright (c) 2009 Microsoft Corporation. All rights reserved.
C:\...>sqlplus andrey@connstring
SQL*Plus: Release 11.2.0.2.0 Production on Tue Sep 11 13:06:48 2012
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Enter password:
Connected to:
Oracle Database 11g Release 11.1.0.7.0 - 64bit Production
SQL>
SQL> select sql_id,prev_sql_id
2 from v$session v
3 where v.sid=122;
SQL_ID PREV_SQL_ID
------------- -------------
078s8pqj8zzvd
SQL> select sql_text
2 from v$sql
3 where sql_id='078s8pqj8zzvd';
SQL_TEXT
---------------------------------------------------------------------
select * from test_table where rownum<5
SQL>
Does anybody know why this is happening?
why is sql_id\prev_sql_id in v$session recording differently from PL\SQL Developer tool
Then it does in SQL*Plus?
Thanks in advance for your help.
Best Regards,
Andrey
[Updated on: Tue, 11 September 2012 05:16] Report message to a moderator |
|
|
|
|
|
| Re: V$SESSION doesn't show sql_id of statement ran from PL\SQL Developer tool [message #566083 is a reply to message #566075] |
Tue, 11 September 2012 06:11  |
Andrey_R
Messages: 345
Registered: January 2012
Location: Israel
|
Senior Member |
|
|
Thanks Michel, however:
The issue is not the output that can't be seen.
It's that
1. If I run "select * from test_table" from PL\SQL Developer session - Its v$session record will not show the correct sql_id.
2. If I run "select * from test_table" from SQL*Plus session - Its v$session record will show the correct sql_id.
* I will take into account that this kind of issues belong to this forum thread,
And not performance. Thanks.
Regards,
Andrey
[Updated on: Tue, 11 September 2012 06:14] Report message to a moderator |
|
|
|
|
|
|
|
|
|
|
|
] 
Search
Help
Members
Register
Login
Home
