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

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.

Chloe Bennett

Chloe Bennett

Culture, Media & Entertainment Columnist

Chloe Bennett explores the intersection of pop culture, streaming entertainment, digital trends, and contemporary lifestyle. Her weekly commentary reaches thousands of culture enthusiasts.

Share this article
Twitter Facebook Pinterest