-
How to get full query for end user sessions
Hi ,
I want to get the full sql stmt running by end user.I tried
v$sqltext;
v$sqlarea;
v$sql;
v$sqltext_with_newlines;
But none of the views is giving me fill sql statement only partial....Can u pls help..?
-
V$SQLTEXT should provide entire SQL. BTW what's your Oracle Version?
-
try changing the column format
-
Originally Posted by Mohith123
try changing the column format
Not sure what you mean.
As stated by Malay v$sqltext provides the info poster is looking for, just take into consideration each query is described in as many v$sqltext rows as needed; don't forget to sort by piece.
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
-
Originally Posted by Mohith123
column format a500;
Why?
Code:
SQL> desc v$sqltext
Name Null? Type
----------------------------------------- -------- ----------------------------
ADDRESS RAW(8)
HASH_VALUE NUMBER
SQL_ID VARCHAR2(13)
COMMAND_TYPE NUMBER
PIECE NUMBER
SQL_TEXT VARCHAR2(64)
SQL>
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
SQL> col SQL_TEXT format a50
SQL> select SQL_TEXT from v$sqltext where SQL_ID='6hwjmjgrpsuaa';
SQL_TEXT
--------------------------------------------------
, req_reason
nqueue_statistics where total_req# != 0 order
by eq_type
#, succ_req#, failed_req#, cum_wait_time, event
# from v$e
:instance_number, eq_type, req_reason, total_re
q#, total_wait
led_req#, cum_wait_time, event#) select :sn
SQL_TEXT
--------------------------------------------------
ap_id, :dbid,
eq_type, req_reason, total_req#, total_wait#,
succ_req#, fai
insert into wrh$_enqueue_stat (snap_id, dbid, in
stance_number,
7 rows selected.
SQL> col SQL_TEXT format a10000
SQL> select SQL_TEXT from v$sqltext where SQL_ID='6hwjmjgrpsuaa';
SQL_TEXT
--------------------------------------------------------------------------------
, req_reason
nqueue_statistics where total_req# != 0 order by eq_type
#, succ_req#, failed_req#, cum_wait_time, event# from v$e
:instance_number, eq_type, req_reason, total_req#, total_wait
led_req#, cum_wait_time, event#) select :snap_id, :dbid,
eq_type, req_reason, total_req#, total_wait#, succ_req#, fai
insert into wrh$_enqueue_stat (snap_id, dbid, instance_number,
-
Originally Posted by Mohith123
SQL> col SQL_TEXT format a10000
SQL> select SQL_TEXT from v$sqltext where SQL_ID='6hwjmjgrpsuaa';
SQL_TEXT
--------------------------------------------------------------------------------
, req_reason
nqueue_statistics where total_req# != 0 order by eq_type
#, succ_req#, failed_req#, cum_wait_time, event# from v$e
:instance_number, eq_type, req_reason, total_req#, total_wait
led_req#, cum_wait_time, event#) select :snap_id, :dbid,
eq_type, req_reason, total_req#, total_wait#, succ_req#, fai
insert into wrh$_enqueue_stat (snap_id, dbid, instance_number,
I see, very useful huh?
Can't you see don't matter if you format the column a1Trillion the column would always hold no more than 64 chars?
Why don't you do it the easy -right way and stop confusing the poster?
Code:
select sql_text
from v$sqltext
where sql_id='???'
order by piece;
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
thanks for that...sorry if i created confusion.....
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|