SELECT * FROM dba_hist_wr_control ;
or, only timings:
SELECT EXTRACT( DAY FROM snap_interval) *24*60+ EXTRACT( HOUR FROM snap_interval) *60+ EXTRACT( MINUTE FROM snap_interval ) "Snapshot Interval", EXTRACT( DAY FROM retention) *24*60+ EXTRACT( HOUR FROM retention) *60+ EXTRACT( MINUTE FROM retention ) "Retention Interval" FROM dba_hist_wr_control /
Modify, units are minutes:
BEGIN DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS( retention => 20160); END; /
Modify, units are minutes:
BEGIN DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS( INTERVAL => 60); END; /
Modify, units are minutes:
BEGIN DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS( topnsql => 100); END; /
SELECT DBID, snap_id, snap_level, to_char(begin_interval_time, 'dd/mm/yy hh24:mi:ss') BEGIN FROM dba_hist_snapshot ORDER BY 1;
SELECT SQL_TEXT FROM WRH$_SQLTEXT WHERE SQL_ID='<SQL_ID>';