| 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: 441
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: 441
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 |
|
|
|
|
|
| Re: V$SESSION doesn't show sql_id of statement ran from PL\SQL Developer tool [message #566129 is a reply to message #566086] |
Tue, 11 September 2012 08:03 |
Andrey_R
Messages: 441
Registered: January 2012
Location: Israel
|
Senior Member |
|
|
Thanks Michel, I've done that, but now it's showing me
SQL_TEXT
-------------------------------------------------------------------------
begin :id := sys.dbms_transaction.local_transaction_id; end;
Don't understand what component I should disable now to make it work.
Tried running PL\SQL Developer's login.sql script with "set serveroutput off" line,
or CMD window(in addition to graphically tick off the "enable" button under "OUTPUT").
nothing seems to change, I can only get this DBMS_...line's sql_id and not my query's..
Please help..
Thanks and Best Regards,
Andrey
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
