Check history of Executed queries
1 min readThe view v$sql contains almost of queries which are executed in your Oracle DB. Basically
you have privileges to query this view, you can check all from it. Below are some useful
queries for you to do on this view.
- Get latest query
select sql_text from v$sql where first_load_time=(select max(first_load_time) from v$sql);
- Sort executed queries by load time
select sql_text, first_load_time from v$sql order by first_load_time desc;
- Get executed queries in a schema which have special text and sort by load time
select * from v$sql where parsing_schema_name like 'YOUR_SCHEMA' and sql_text like
'%YOUR_TEXT%' order by first_load_time desc;
- Get 100 last executed queries
select sql_fulltext from (select * from v$sql where parsing_schema_name like 'VHA' order
by first_load_time desc) where rownum < 101;
- Get 100 executed UPDATE or DELETE queries in a specific time period and sort by load
time
select sql_text,sql_fulltext, first_load_time, parsing_schema_name from ( select * from
v$sql where parsing_schema_name like 'YOUR_SCHEMA' and (sql_text like '%UPDATE %' or
sql_text like '%INSERT %') and to_timestamp(first_load_time, 'YYYY-MM-DD/HH24:MI:SS') >to_timestamp ('2012-09-27/14:06:00', 'YYYY-MM-DD/HH24:MI:SS') order by
first_load_time desc) where rownum < 101;
