This script reports SQL TEXT from v$sqltext.
undefine vst2_sid
clear col bre comp
select st.sql_text
from v$process p, v$session s, v$sqltext st
where sid=&&vst2_sid
and addr=paddr
and st.address=decode(sql_address, '00', prev_sql_addr, sql_address)
order by piece
/
Sample Output:
Code: Select all
SQL> undefine vst2_sid
clear col bre comp
select st.sql_text
from v$process p, v$session s, v$sqltext st
where sid=&&vst2_sid
and addr=paddr
and st.address=decode(sql_address, '00', prev_sql_addr, sql_address)
order by piece
/
SQL> columns cleared
breaks cleared
computes cleared
SQL> 2 3 4 5 6 7 Enter value for vst2_sid: 159
old 3: where sid=&&vst2_sid
new 3: where sid=159
SQL_TEXT
----------------------------------------------------------------
select st.sql_text from v$process p, v$session s, v$sqltext st w
here sid=159 and addr=paddr and st.address=decode(sql_address, '
00', prev_sql_addr, sql_address) order by piece
SQL>
To check the current Oracle SQL statements performed by a certain session, use the below query to filter Username:
select sql_text
from v$sqlarea
where (address, hash_value) in
(select sql_address, sql_hash_value
from v$session
where username like '&username')
/
Sample Output:
Code: Select all
SQL> select sql_text
from v$sqlarea
where (address, hash_value) in
(select sql_address, sql_hash_value
from v$session
where username like '&username')
/
2 3 4 5 6 7 Enter value for username: SYS
old 6: where username like '&username')
new 6: where username like 'SYS')
SQL_TEXT
--------------------------------------------------------------------------------
select sql_text from v$sqlarea where (address, hash_value) in (select sql_add
ress, sql_hash_value from v$session where username like 'SYS')