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