How to see query executed for a specific user from V$SQL

Posted on

Question :

I am trying to have the queries used for a specific user at the end of the day. But it seems the queries in this view V$SQL are deleted every short time. I am not sure how much time. Also i dont know why they are deleted if i still see some records with older LAST_ACTIVE_TIME is there a way to save them before they are deleted. For that when are they deleted?or other suggestion. Hope you can help me.

Thank you!

Answer :

You can query v$sqlarea, which holds sql history for a little longer than v$sql (holds info only about current running sql). Both have similar columns, but depending on the report you want to extract, you might wanna join v$sqlarea with other views. But V$sqlarea is also limited to a period of time, and if you need to go more in depth, the view is DBA_HIST_SQLTEXT. Keep in mind that for every dynamic (V$) view, there is a corresponding dba_hist table… But since dba_hist views are often maintained by AWR, you might need to be licensed for diagnostic/ tuning pack for complete reports.

Leave a Reply

Your email address will not be published. Required fields are marked *