Para reduzir a retenção do AWR

EXEC dbms_workload_repository.drop_snapshot_range (low_snap_id=>1107, high_snap_id=>1108);
— Reduzir a retencao para dois dias.

exec dbms_workload_repository.modify_snapshot_settings(retention => 2880, interval => 60, topnsql => ‘DEFAULT’);

— Verificar o status da retencao atual do AWR

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;

retention = snapshot interval (1 hr ) X 24 X X 7 days
For each week it’s 10080

– Retain data for 21 days ( 3 weeks )
exec dbms_workload_repository.modify_snapshot_settings(retention => 30240, interval => 60, topnsql => ‘DEFAULT’);

– Retain data for 28 days (4 weeks

SQL> desc dba_hist_snapshot;
Nome Nulo? Tipo
—————————————– ——– —————————-

SNAP_ID NOT NULL NUMBER
DBID NOT NULL NUMBER
INSTANCE_NUMBER NOT NULL NUMBER
STARTUP_TIME NOT NULL TIMESTAMP(3)
BEGIN_INTERVAL_TIME NOT NULL TIMESTAMP(3)
END_INTERVAL_TIME NOT NULL TIMESTAMP(3)
FLUSH_ELAPSED INTERVAL DAY(5) TO SECOND(1)

SNAP_LEVEL NUMBER
ERROR_COUNT NUMBER

SQL>

Drop AWR snapshots in range

declare
v_max number;
v_min number;
begin

select max(snaP_id) ,min(snap_id)
into v_max, v_min
from dba_hist_snapshot;

dbms_workload_repository.drop_snapshot_range (low_snap_id=>v_min, high_snap_id=> v_max);
end;

MAX(SNAP_ID) MIN(SNAP_ID)
———— ————
14360 14176

Drop AWR snapshots in range

EXEC dbms_workload_repository.drop_snapshot_range (low_snap_id=>14176, high_snap_id=>14360);