Home > diagnostic > AWR – automatic workload repository

AWR – automatic workload repository

to change the collection interval and retention policy

exec dbms_workload_repository.modify_snapshot_settings ( retention => 20160, -
interval => 30);

example above will change to 14 days (value in minutes) with 30mins snapshots

retention (default  8 days) and interval (default 1 hour)  in minutes

be aware – increasing the retention time or increase the frequence of snapshots the sysaux tablespace will increase also !

to show the current settings or the latest snapshot

col systimestamp format a35
col most_recent_snap_time format a25
col snap_interval format a17
col retention format a17

select systimestamp, most_recent_snap_time, snap_interval, retention
 from wrm$_wr_control 
  where dbid = (select dbid from v$database);

create manually a snapshot

exec dbms_workload_repository.create_snapshot

enable/disable Diagnostic&Tuning pack

If you havent’t licensed the packs you can disable the collection of awr datas.

This helps you to avoid license offending e.g. running awrrpt

alter system set control_management_pack_access='NONE';

Running now a awrrpt.sql (located in $ORACLE_HOME/rdbms/admin) will no longer show datas of the accordand areas

control_management_pack_access can be set to




transfering awr-information

sometimes its neccessary to backup / transfer awr datas to a single database (or to oracle support 🙂 )

with awrextr.sql ($ORACLE_HOME/rdbms/admin) you can export (via expdp) your awr-datas to disk.

create a directory where the dump should be written

sql> @?/rdbms/admin/awrextr

follow the instructions

now transfer the dumpfile to the desired location / instance

create a directory where the dump is located


follow the instructions as well

now it’s possible to run awr-reports on the imported awr datas

BUT, don’t use awrrpt.sql (as usually) because this script will generate awrreports for the running instance

USE awrrpti.sql and enter the desired dbid you want to query.

retrieve informations regarding AWR



Troubleshooting: AWR Snapshot Collection issues (Doc ID 1301503.1 on http://support.oracle.com)

Categories: diagnostic Tags: ,
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: