Monday, September 28, 2015

How to see the DB Queries executed in Database

It is very common requirement to see what are all the queries are being executed in database,


Step 1 - Execute the below query, In below example, I want to see what are all the queries are being executed on EMPLOYEE table.

select to_char(LAST_ACTIVE_TIME,'dd-mon-yyyy hh24:mi:ss'),last_load_time,sql_id,sql_text
    from v$sql where upper(sql_text) like '%EMPLOYEE%' order by 1 desc;

Step 2 - Look for most recent 'Last_Load_Time' time in the search results

Step 3 - Get the corresponding result row associated 'sql_id' value from above step and supply to below query

                select sql_fulltext from v$sql where sql_id='&name';

Step4 - Supply the sql_id to below query to see the bind variables

                  select name, value_string from v$sql_bind_capture where sql_id = '&sql_id'