How to see query with the same SQL_TEXT but different parameter in history in v$sql?

I want to check history of queries to specific table( let's call it specific.table) in oracle sql. I have a query:

Query 1:SELECT sql_id, sql_text FROM v$sql WHERE sql_text like "%specific.table%"

Let's say I did one query like this

Query 2: SELECT * FROM specific.table WHERE specifc.table.something = :1 where :1 is parameter.

Now I can use Query 1 to check Query 2 in history and it will be there. But then when I make Query 2 with different bind variable, it is not visible in history returned by Query 1. Why the second Query 2 is not visible? Is there a way to check parameter :1 used in sql every time I use Query 2?

Edit:

Query I am using for checking bind variables:

SELECT * FROM v$sql_bind_capture WHERE sql_id = "id" 

Edit 2: Responding to Rene:

I want two make two queries like this:

select * from ngm_gebruiker where naam = 'A'; select * from ngm_gebruiker where naam = 'B' 

and then check v$sql for the table:

SELECT sql_id, sql_text FROM v$sql WHERE sql_text like '%ngm_gebruiker% 

but the result is

3 g71fx4fyyku6n select * from ngm_gebruiker where naam = :"SYS_B_0"

and when I check bind variable for g71fx4fyyku6n, I can only get A. I would like to check bind variable for both queries.

6

1 Answer

Here's an example with three different queries on the same table:

select * from ngm_gebruiker; select * from ngm_gebruiker where naam = 'Piet'; select * from ngm_gebruiker where naam = 'Piet' and idc_monitor='N'; 

Result of querying v$sql for that table:

SELECT sql_id, sql_text FROM v$sql WHERE sql_text like '%ngm_gebruiker%' 2 a0q4f137bffs6 select * from ngm_gebruiker 3 g71fx4fyyku6n select * from ngm_gebruiker where naam = :"SYS_B_0" 4 4vpyf8g2q711h select * from ngm_gebruiker where naam = :"SYS_B_0" and idc_monitor=:"SYS_B_1" 
1

Your Answer

Sign up or log in

Sign up using Google Sign up using Facebook Sign up using Email and Password

Post as a guest

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

You Might Also Like