Para reduzir a reten\u00e7\u00e3o do AWR<\/p>\n
EXEC dbms_workload_repository.drop_snapshot_range (low_snap_id=>1107, high_snap_id=>1108);
— Reduzir a retencao para dois dias.<\/p>\n
exec dbms_workload_repository.modify_snapshot_settings(retention => 2880, interval => 60, topnsql => ‘DEFAULT’);<\/p>\n
— Verificar o status da retencao atual do AWR<\/p>\n
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; <\/p>\n
retention = snapshot interval (1 hr ) X 24 X X 7 days
For each week it’s 10080 <\/p>\n
– Retain data for 21 days ( 3 weeks )
exec dbms_workload_repository.modify_snapshot_settings(retention => 30240, interval => 60, topnsql => ‘DEFAULT’); <\/p>\n
– Retain data for 28 days (4 weeks<\/p>\n
SQL> desc dba_hist_snapshot;
Nome Nulo? Tipo
—————————————– ——– —————————-<\/p>\n
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)<\/p>\n
SNAP_LEVEL NUMBER
ERROR_COUNT NUMBER<\/p>\n
SQL> <\/p>\n
Drop AWR snapshots in range <\/p>\n
declare
v_max number;
v_min number;
begin<\/p>\n
select max(snaP_id) ,min(snap_id)
into v_max, v_min
from dba_hist_snapshot;<\/p>\n
dbms_workload_repository.drop_snapshot_range (low_snap_id=>v_min, high_snap_id=> v_max);
end;<\/p>\n
MAX(SNAP_ID) MIN(SNAP_ID)
———— ————
14360 14176<\/p>\n
Drop AWR snapshots in range <\/p>\n
EXEC dbms_workload_repository.drop_snapshot_range (low_snap_id=>14176, high_snap_id=>14360);<\/p>\n","protected":false},"excerpt":{"rendered":"
Para reduzir a reten\u00e7\u00e3o 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) … Continue lendo