The below query is used to find the history of sql statement which has been executed earlier.
Below query has been used for finding the yesterday data.
eg:
set colsep “|” set pages 9999 line 9999 set TAB off col PARSING_SCHEMA_NAME for a20 col sql_text for a50 wrap col BEGIN_INTERVAL_TIME for a25 col end_INTERVAL_TIME for a25 select PARSING_SCHEMA_NAME, dhss.sql_id, dhss.executions_delta, dhss.executions_total, dhss.ELAPSED_TIME_TOTAL, dhss.CPU_TIME_TOTAL, sn.begin_interval_time, sn.end_interval_time, sq.sql_text from dba_hist_sqlstat dhss, dba_hist_snapshot sn, dba_hist_sqltext sq where dhss.snap_id = sn.snap_id and sq.sql_id = dhss.sql_id and trunc(sn.begin_interval_time) = trunc(sysdate-1) and PARSING_SCHEMA_NAME not in (‘SYS’,’SYSTEM’);
In the above query based on your requirement change sysdate values to get the required output.
