1. Directly query V$SQL or V$SQLAREA view
Through the system viewV$SQLandV$SQLAREAYou can directly obtain the SQL execution records cached in the shared pool, including historical SQL text, execution times, CPU/time consumption and other statistical information:
-- Query all executedSQL（Descending order in total time-consuming） SELECT sql_id, sql_text, executions, ROUND(elapsed_time/1000, 2) AS total_elapsed_time_s, ROUND(cpu_time/1000, 2) AS cpu_time_s, first_load_time, last_active_time FROM v$sql ORDER BY elapsed_time DESC;
- Key fields：
sql_text(SQL text),executions(Number of executions),last_active_time(Last execution time)16.
2. Related query in combination with session information
By associationV$SESSIONandV$SQLView, traceable SQL executor and session information:
-- QuerySQLExecutor and session details SELECT , , , , q.sql_text, , q.last_load_time FROM v$session s JOIN v$sql q ON s.sql_id = q.sql_id WHERE q.last_active_time > SYSDATE - 7; -- close7Days executedSQL
- illustrate：
v$sessionProvides the login user and operating system information for the session.v$sqlRelated specific SQL content23.
3. Filter by time or operation type
For a specific time period or operation type (e.g.DELETE、UPDATE) Perform accurate screening:
-- Query2025Year3moon1The futureDELETEOperation record
SELECT sql_text, parsing_user_id, last_active_time FROM v$sqlarea WHERE sql_text LIKE 'DELETE%' AND last_active_time >= TO_DATE('2025-03-01', 'YYYY-MM-DD');- Notice：
v$sqlareaThe view contains complete SQL text, but be aware of itLIKEPerformance impact of fuzzy matching47.
4. Filter by user or permission
passparsing_user_idorparsing_schema_nameFields track SQL execution records for specific users:
-- Query the userSCOTTPerformedSQL SELECT sql_text, executions, last_active_time FROM v$sql WHERE parsing_user_id = (SELECT user_id FROM dba_users WHERE username = 'SCOTT');
- Depend on permissions: Need to have
DBA_USERSandV$SQLquery permission57.
Things to note
- Data retention period：
V$SQLandV$SQLAREAThe cached SQL in the shared pool is stored. If the instance is restarted or the SQL is eliminated, the history may be lost 16. - Permission Requirements: Querying the system view requires granting
SELECT_CATALOG_ROLEOr directly authorized (e.g.GRANT SELECT ON v_$sql TO user;）78。 - Performance impact: High-frequency query system views may have a slight impact on database performance, and it is recommended to operate during off-peak hours23.
The above methods can be used in a comprehensive manner, and it is recommended to pass first.V$SQLReduce the query scope by time or user dimension, and then associate the session information positioning problem SQL14.
Summarize
This is the article about the SQL statement methods that have been executed by Oracle database query. For more related contents of SQL statements that have been executed by Oracle database query, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!