AWR (automatic workload repository) of Oracle

####################AWR (automatic workload repository) of Oracle####################

Retention period of AWR report:

select retention from dba_hist_wr_control;

/*If we have more than 7 days,we can reduce it to 7 day which is 7*24*60 = 10080 minutes.
the retention period is modified to 7 days (10080 minutes) and 
the interval between each snapshot is 60 minutes through below query. */

execute dbms_workload_repository.modify_snapshot_settings (interval => 60,retention => 10080);

In case while reducing the retention period if we face an  error then check the MOVING_WINDOW_SIZE value with below query

SELECT moving_window_size FROM dba_hist_baseline WHERE baseline_type = 'MOVING_WINDOW';

exec DBMS_WORKLOAD_REPOSITORY.modify_baseline_window_size( window_size =>7);

Then update it to correct value and then execute the above AWR retention query.

execute dbms_workload_repository.modify_snapshot_settings (interval => 60,retention => 10080);

Once the retention period is set you can follow below steps to cleanup the old AWR reports to free up space. 
Run below query to find the oldest and newest AWR snapshots.

SELECT snap_id, begin_interval_time, end_interval_time
FROM SYS.WRM$_SNAPSHOT WHERE snap_id = ( SELECT MIN (snap_id) FROM SYS.WRM$_SNAPSHOT) UNION SELECT snap_id, begin_interval_time, end_interval_time FROM SYS.WRM$_SNAPSHOT WHERE
snap_id = ( SELECT MAX (snap_id) FROM SYS.WRM$_SNAPSHOT);

To cleanup all AWR reports between snap_id to execute below query.

BEGIN
dbms_workload_repository.drop_snapshot_range(low_snap_id => , high_snap_id=>);
END;
/

Else you can run below commands to drop the old AWR reports and rebuild the repositories. This process is very fast too.

connect / as sysdba
@?/rdbms/admin/catnoawr.sql
@?/rdbms/admin/catawrtb.sql

After clearing up all the AWR reports we will be able to get space from SYSAUX tablespace.

LOB segments in Sysaux table:

select count(*) from WRH$_SQLTEXT;

select * from dba_lobs where table_name = 'WRH$_SQLTEXT';

select * from dba_segments where segment_name = '';

To move AWR related  lob  in SYSAUX table:

Startup the database in RESTRICT mode to prevent interference from AWR operations during the rebuild.

alter table wrh$_sqltext move lob(sql_text) store as tablespace sysaux;

--the AWR table is WRH$_SQLTEXT, the LOB column is SQL_TEXT and is the name of the LOB segment whose storage we want to reclaim

--The new LOB segment will have a new data_object_id in dba_objects and a new (header_file,header_block) pair in dba_segments. 

It should also have a reduced number of bytes and blocks in dba_segments.

--Check for and rebuild any unusable index on the table after the LOB move:

select index_name from dba_indexes where table_name='WRH$_SQLTEXT' and status='UNUSABLE';

alter index SAMPLE rebuild;

--restart the database in normal mode.

shu immediate;
startup;



Thanks & Regards
Nikhil Kumar.