History of executed queries
The view v$sql contains almost of queries which are executed in your Oracle Database. I attached few sql queries which will be useful to check history of SQL.
1. Get latest query
select sql_txt from v$sql where first_load_time=(select max(1st_load_time) from v$sql);
2. Sort executed queries by load time
select sql_text, first_load_time from v$sql order by first_load_time desc;
select * from v$sql where parsing_schema_name like 'YOUR_SCHEMA_DETAILS' and sql_text like '%YOUR_SQLTEXT%' order by first_load_time desc;
select sql_fulltext from (select * from v$sql where parsing_schema_name like 'ORAAGENT' order by first_load_time desc) where rownum < 51;
5. Get 50 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('2021-01-24/09:06:00', 'YYYY-MM-DD/HH24:MI:SS')
order by first_load_time desc
)
where rownum < 51You can create your own queries to find out what queries you need to check. Remember this view v$sql doesn’t store prepared statements.
Thank you for giving your valuable time to read the above information.
If you want to be updated with all our articles send us the Invitation or Follow us:
Ramkumar’s LinkedIn: https://www.linkedin.com/in/ramkumardba/
LinkedIn Group: https://www.linkedin.com/in/ramkumar-m-0061a0204/
Facebook Page: https://www.facebook.com/Oracleagent-344577549964301
Ramkumar’s Twitter : https://twitter.com/ramkuma02877110
Ramkumar’s Telegram: https://t.me/oracleageant
Ramkumar’s Facebook: https://www.facebook.com/ramkumarram8
One thought on “History of executed queries”