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.
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