Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to find parameters in Oracle query received from v$sql?

I use query:

 select LAST_LOAD_TIME, ELAPSED_TIME, MODULE, SQL_TEXT elasped from v$sql
 WHERE MODULE='JDBC Thin Client'
 ORDER BY LAST_LOAD_TIME DESC

elasped:

 delete from tableA where fk in (select pk from tableB where tableB.fk=:1
 and tableB.date between :2 and :3)

Is it possible find these parameters 1, 2 and 3?

like image 482
user710818 Avatar asked Jan 08 '13 14:01

user710818


People also ask

What is the use of V parameter in Oracle?

V$PARAMETER displays information about the initialization parameters that are currently in effect for the session. A new session inherits parameter values from the instance-wide values displayed by the V$SYSTEM_PARAMETER view.

What is show parameter Oracle?

The SHOW SPPARAMETERS command, without any string following the command, displays all initialization parameters for all instances. Your output may vary depending on the version and configuration of the Oracle Database server to which you are connected.

What are SQL query parameters?

Parameterized SQL queries allow you to place parameters in an SQL query instead of a constant value. A parameter takes a value only when the query is executed, which allows the query to be reused with different values and for different purposes.


1 Answers

Something like this:

select s.sql_id, 
       bc.position, 
       bc.value_string, 
       s.last_load_time, 
       bc.last_captured
from v$sql s
  left join v$sql_bind_capture bc 
         on bc.sql_id = s.sql_id 
        and bc.child_number = s.child_number
where s.sql_text like 'delete from tableA where fk%' -- or any other method to identify the SQL statement
order by s.sql_id, bc.position;
like image 102
a_horse_with_no_name Avatar answered Sep 23 '22 16:09

a_horse_with_no_name