Question: I want to keep my AWR data long-term and disable the jobs that periodically purge the AWR snapshots. What are the best practices for keeping AWR data for many months and years?
Answer: There are several ways to keep long-term AWR performance data. Keeping AWR data long-term involves these steps:
One question is whether or not to compress the AWR data by removing the SQL table data periodically.
truncate table wrh$_sqlstat;
truncate table wrh$_sqltext;
truncate table wrh$_sql_plan;
You can schedule this periodic truncate of the SQL tables using either dbms_scheduler or with a crontab job.
1 - Truncate the SQL tables periodically
2 - Modify the snapshot retention period
3 - Remove the automatic flush job
One question is whether or not to compress the AWR data by removing the SQL table data periodically.
Compressing AWR data
If you choose to compress the AWR data, you will need to periodically truncate these "unnecessary" tables. These tables contain SQL that is rarely useful after a month. Note: You must be connected as SYSDBA to truncate these tables.- wrh$_sqlstat contains a history for SQL execution statistics and stores snapshots of v$sql view.
- wrh$_sqltext stores actual text for SQL statements captured from v$sql.
- wrh$_sql_plan stores execution plans for SQL statements available indba_hist_sqlstat.
truncate table wrh$_sqlstat;
truncate table wrh$_sqltext;
truncate table wrh$_sql_plan;
You can schedule this periodic truncate of the SQL tables using either dbms_scheduler or with a crontab job.
Change retention period for snapshots:
The AWR has a special background process, MMON, which is responsible for gathering regular snapshots. The DBA is able to specify the frequency at which MMON gathers snapshots via the dbms_workload_repository.modify_snapshot_settings procedure:
SQL> desc dbms_workload_repository
PROCEDURE MODIFY_SNAPSHOT_SETTINGS
Argument Name Type In/Out Default?
----------------- --------------- ------ --------
RETENTION NUMBER IN DEFAULT
INTERVAL NUMBER IN DEFAULT
DBID NUMBER IN DEFAULT
The interval parameter sets the time interval, in minutes, between the snapshots. The default interval between snapshots is 60 minutes. The valid range of values for this parameter ranges from 10 minutes to 52,560,000 minutes (100 years).
The dbms_workload_repository package has the global variables min_intervaland max_interval , which set the lower and upper limits for this parameter. If the value specified for the interval is zero, automatic and manual snapshots will be prohibited.
The first dbms_workload_repository.modify_snapshot_settings procedure parameter, retention , allows the DBA to specify the time period, in minutes. The AWR will preserve that particular snapshot in the repository. The default value for this parameter is 10,080 minutes (seven days).
The valid range of values for this parameter also ranges from 10 minutes to 52,560,000 minutes (100 years). The dbms_workload_repository package has global variables min_retention and max_retention, which set up the lower and upper limits for the retention parameter.
NOTE: If a zero value is specified for retention, snapshots will be stored for an unlimited time.
The current settings for AWR retention and interval parameters can be viewed using dba_hist_wr_control data dictionary view.
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;
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;
This script returns the current AWR interval values in minutes:
Snapshot Interval Retention Interval
----------------- ------------------
60 10080
Here are examples of modifying the snapshot collection thresholds
dbms_workload_repository.modify_snapshot_settings(retention = > 0)
AWR flushing and the MMON background process
The MMON Oracle background process is responsible for periodically flushing the oldest AWR tables, using a LIFO queue method. Here, we see the flush_level for an AWR installation:
SQL> desc dbms_workload_repository
PROCEDURE CREATE_SNAPSHOT
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
FLUSH_LEVEL VARCHAR2 IN DEFAULT
FUNCTION CREATE_SNAPSHOT RETURNS NUMBER
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
FLUSH_LEVEL VARCHAR2 IN DEFAULT
PROCEDURE CREATE_SNAPSHOT
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
FLUSH_LEVEL VARCHAR2 IN DEFAULT
FUNCTION CREATE_SNAPSHOT RETURNS NUMBER
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
FLUSH_LEVEL VARCHAR2 IN DEFAULT
The only parameter listed in the procedures is the flush_level , which can have either the default value of TYPICAL or a value of ALL. When the statistics level is set to ALL, the AWR gathers the maximum amount of performance data.
The MMON background process is responsible for removing old historical data from the AWR. The amount of retention time after which data will be removed from database is determined by the retention setting.
However, data can be cleared from the AWR tables by using thedbms_workload_repository.drop_snapshot_range procedure. The starting and ending snapshots for the history to be removed from the AWR will need to be set to run the following script, drop_snapshot_range.
desc dbms_workload_repository
PROCEDURE DROP_SNAPSHOT_RANGE
Argument Name Type In/Out Default?
------------------------------ -------------------- ------ --------
LOW_SNAP_ID NUMBER IN
HIGH_SNAP_ID NUMBER IN
DBID NUMBER IN DEFAULT
PROCEDURE DROP_SNAPSHOT_RANGE
Argument Name Type In/Out Default?
------------------------------ -------------------- ------ --------
LOW_SNAP_ID NUMBER IN
HIGH_SNAP_ID NUMBER IN
DBID NUMBER IN DEFAULT
There are a number of AWR views (all starting DBA_HIST_) which I will go into in other posts but the one we are interested in to display the AWR settings is DBA_HIST_WR_CONTROL. This view only has 4 columns which are:
Using dbms_workload_repository.modify_snapshot_settings one can modify retention, interval and topnsql.
– get the dbid which is needed to passs to dbms_workload_repository.modify_snapshot_settings
SQL> select dbid from v$database;
SQL> select dbid from v$database;
DBID
———-
1992878807
———-
1992878807
– retention=>value in minutes so (45 days * 24 (hours per day) * 60 minutes per hour = 64800), max value can be set by passing a value of 0 which means forever
– internal=>60min (snap at this interval), a value of 0 will turn off AWR
– topnsql – top N sql size, specify value of NULL will keep the current setting
SQL> exec dbms_workload_repository.modify_snapshot_settings(retention=>64800, interval=>60, topnsql=>100, dbid=>1992878807);
– internal=>60min (snap at this interval), a value of 0 will turn off AWR
– topnsql – top N sql size, specify value of NULL will keep the current setting
SQL> exec dbms_workload_repository.modify_snapshot_settings(retention=>64800, interval=>60, topnsql=>100, dbid=>1992878807);
PL/SQL procedure successfully completed.
– shows retention and interval after it was modified
SQL> 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,
topnsql
from dba_hist_wr_control;
SQL> 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,
topnsql
from dba_hist_wr_control;
Snapshot Interval Retention Interval topnsql
—————– —————— ———-
60 64800 100
—————– —————— ———-
60 64800 100
— Change snapshot interval to 30mins
SQL> EXEC dbms_workload_repository.modify_snapshot_settings(interval=>30);
PL/SQL procedure successfully completed.
SQL> EXEC dbms_workload_repository.modify_snapshot_settings(interval=>30);
PL/SQL procedure successfully completed.
SQL> desc dba_hist_wr_control
Name Null? Type
——————————- ——– —————————-
DBID NOT NULL NUMBER
SNAP_INTERVAL NOT NULL INTERVAL DAY(5) TO SECOND(1)
RETENTION NOT NULL INTERVAL DAY(5) TO SECOND(1)
TOPNSQL VARCHAR2(10)
If we select from it we can see the defaults
Name Null? Type
——————————- ——– —————————-
DBID NOT NULL NUMBER
SNAP_INTERVAL NOT NULL INTERVAL DAY(5) TO SECOND(1)
RETENTION NOT NULL INTERVAL DAY(5) TO SECOND(1)
TOPNSQL VARCHAR2(10)
If we select from it we can see the defaults
SQL> set linesize 100
SQL> col snap_interval format a20
SQL> col retention format a20
SQL> col topnsql format a20
SQL> select * from dba_hist_wr_control;
SQL> col snap_interval format a20
SQL> col retention format a20
SQL> col topnsql format a20
SQL> select * from dba_hist_wr_control;
DBID SNAP_INTERVAL RETENTION TOPNSQL
———- ——————– ——————– ——————–
3566081556 +00000 01:00:00.0 +00007 00:00:00.0 DEFAULT
So, the defaults are automatic snapshots are taken every 1 hour and snapshots are kept for 7 days.
———- ——————– ——————– ——————–
3566081556 +00000 01:00:00.0 +00007 00:00:00.0 DEFAULT
So, the defaults are automatic snapshots are taken every 1 hour and snapshots are kept for 7 days.
So, what if we want to change these? Oracle have supplied a package DBMS_WORKLOAD_REPOSITORY which is used to take manual snapshots, create baselines and the important one for us, change snapshot settings. So, for example, if we wanted to change the settings so we took snapshots every 10 minutes and kept these snapshots for 10 days we would issue the following sql:
execute dbms_workload_repository.modify_snapshot_settings
( interval => 10,
retention => 14400);
All the values are in minutes, so for the retention 14400 is 10 days. So, if we now look at dba_hist_wr_control we can see our change has been reflected
( interval => 10,
retention => 14400);
All the values are in minutes, so for the retention 14400 is 10 days. So, if we now look at dba_hist_wr_control we can see our change has been reflected
SQL> select * from dba_hist_wr_control;
DBID SNAP_INTERVAL RETENTION TOPNSQL
———- ——————– ——————– ——————–
3566081556 +00000 00:10:00.0 +00010 00:00:00.0 DEFAULT
It is always worth reviewing the size of your SYSAUX tablespace before you make any changes. Oracle have provided a handy script for this, which is called utlsyxsz.sql (held in $ORACLE_HOME/rdbms/admin) which will estimate the required size for the SYSAUX tablespace based on the retention period, snapshot interval you are going to use and a few other variables like number of active sessions and number of tables.
———- ——————– ——————– ——————–
3566081556 +00000 00:10:00.0 +00010 00:00:00.0 DEFAULT
It is always worth reviewing the size of your SYSAUX tablespace before you make any changes. Oracle have provided a handy script for this, which is called utlsyxsz.sql (held in $ORACLE_HOME/rdbms/admin) which will estimate the required size for the SYSAUX tablespace based on the retention period, snapshot interval you are going to use and a few other variables like number of active sessions and number of tables.
Disabling AWR data purging
Compressing AWR data
If you choose to compress the AWR data, you will need to periodically truncate these “unnecessary” tables. These tables contain SQL that is rarely useful after a month. Note: You must be connected as SYSDBA to truncate these tables.
wrh$_sqlstat contains a history for SQL execution statistics and stores snapshots of v$sql view.
wrh$_sqltext stores actual text for SQL statements captured from v$sql.
wrh$_sql_plan stores execution plans for SQL statements available in dba_hist_sqlstat.
connect sys/manager as sysdba;
connect sys/manager as sysdba;
truncate table wrh$_sqlstat;
truncate table wrh$_sqltext;
truncate table wrh$_sql_plan;
truncate table wrh$_sqltext;
truncate table wrh$_sql_plan;
You can schedule this periodic truncate of the SQL tables using either dbms_scheduler or with a crontab job.
Change retention period for snapshots:
The AWR has a special background process, MMON, which is responsible for gathering regular snapshots. The DBA is able to specify the frequency at which MMON gathers snapshots via the dbms_workload_repository .modify_snapshot_settings procedure:
SQL> desc dbms_workload_repository
PROCEDURE MODIFY_SNAPSHOT_SETTINGS
Argument Name Type In/Out Default?
—————– ————— —— ——–
RETENTION NUMBER IN DEFAULT
INTERVAL NUMBER IN DEFAULT
DBID NUMBER IN DEFAULT
The interval parameter sets the time interval, in minutes, between the snapshots. The default interval between snapshots is 60 minutes. The valid range of values for this parameter ranges from 10 minutes to 52,560,000 minutes (100 years).
SQL> desc dbms_workload_repository
PROCEDURE MODIFY_SNAPSHOT_SETTINGS
Argument Name Type In/Out Default?
—————– ————— —— ——–
RETENTION NUMBER IN DEFAULT
INTERVAL NUMBER IN DEFAULT
DBID NUMBER IN DEFAULT
The interval parameter sets the time interval, in minutes, between the snapshots. The default interval between snapshots is 60 minutes. The valid range of values for this parameter ranges from 10 minutes to 52,560,000 minutes (100 years).
The dbms_workload_repository package has the global variables min_interval and max_interval , which set the lower and upper limits for this parameter. If the value specified for the interval is zero, automatic and manual snapshots will be prohibited.
The first dbms_workload_repository.modify_snapshot_settings procedure parameter, retention , allows the DBA to specify the time period, in minutes. The AWR will preserve that particular snapshot in the repository. The default value for this parameter is 10,080 minutes (seven days).
The first dbms_workload_repository.modify_snapshot_settings procedure parameter, retention , allows the DBA to specify the time period, in minutes. The AWR will preserve that particular snapshot in the repository. The default value for this parameter is 10,080 minutes (seven days).
The valid range of values for this parameter also ranges from 10 minutes to 52,560,000 minutes (100 years). The dbms_workload_repository package has global variables min_retention and max_retention, which set up the lower and upper limits for the retention parameter.
NOTE: If a zero value is specified for retention, snapshots will be stored for an unlimited time.
The current settings for AWR retention and interval parameters can be viewed using dba_hist_wr_control data dictionary view.
The current settings for AWR retention and interval parameters can be viewed using dba_hist_wr_control data dictionary view.
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;
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;
This script returns the current AWR interval values in minutes:
Snapshot Interval Retention Interval
—————– ——————
60 10080
Here are examples of modifying the snapshot collection thresholds
Snapshot Interval Retention Interval
—————– ——————
60 10080
Here are examples of modifying the snapshot collection thresholds
dbms_workload_repository.modify_snapshot_settings(retention = > 0)
AWR flushing and the MMON background process
The MMON Oracle background process is responsible for periodically flushing the oldest AWR tables, using a LIFO queue method. Here, we see the flush_level for an AWR installation:
SQL> desc dbms_workload_repository
PROCEDURE CREATE_SNAPSHOT
Argument Name Type In/Out Default?
—————————— ———————– —— ——–
FLUSH_LEVEL VARCHAR2 IN DEFAULT
Argument Name Type In/Out Default?
—————————— ———————– —— ——–
FLUSH_LEVEL VARCHAR2 IN DEFAULT
FUNCTION CREATE_SNAPSHOT RETURNS NUMBER
Argument Name Type In/Out Default?
—————————— ———————– —— ——–
FLUSH_LEVEL VARCHAR2 IN DEFAULT
Argument Name Type In/Out Default?
—————————— ———————– —— ——–
FLUSH_LEVEL VARCHAR2 IN DEFAULT
The only parameter listed in the procedures is the flush_level , which can have either the default value of TYPICAL or a value of ALL. When the statistics level is set to ALL, the AWR gathers the maximum amount of performance data.
The MMON background process is responsible for removing old historical data from the AWR. The amount of retention time after which data will be removed from database is determined by the retention setting.
However, data can be cleared from the AWR tables by using the dbms_workload_repository.drop_snapshot_range procedure. The starting and ending snapshots for the history to be removed from the AWR will need to be set to run the following script, drop_snapshot_range.
desc dbms_workload_repository
PROCEDURE DROP_SNAPSHOT_RANGE
Argument Name Type In/Out Default?
—————————— ——————– —— ——–
LOW_SNAP_ID NUMBER IN
HIGH_SNAP_ID NUMBER IN
DBID NUMBER IN DEFAULT
Argument Name Type In/Out Default?
—————————— ——————– —— ——–
LOW_SNAP_ID NUMBER IN
HIGH_SNAP_ID NUMBER IN
DBID NUMBER IN DEFAULT
Disabling and enabling automatic AWR flushing
You can disable the AWR automatic data flushing mechanism at either the system level or disable flushing for individual tables.
For system-wide disable of AWR table flushing you can use these commands but beware that the AWR tablespace will continue to grow ad-infinitum:
alter session set events ‘immediate trace name awr_flush_table_off level 99′
alter session set events ‘immediate trace name awr_flush_table_off level 106′;
alter session set events ‘immediate trace name awr_flush_table_off level 106′;
To return to the default of weekly flushing you can issue these commands:
alter session set events ‘immediate trace name awr_flush_table_on level 99′;
alter session set events ‘immediate trace name awr_flush_table_on level 106′;
alter session set events ‘immediate trace name awr_flush_table_on level 106′;
If you need to disable flushing the run time statistics for an AWR workload table, you can get the underlying WRH tables with this query:
select
table_id_kewrtb,
table_name_kewrtb
from
x$kewrtb
order by
table_id_kewrtb;
table_id_kewrtb,
table_name_kewrtb
from
x$kewrtb
order by
table_id_kewrtb;
Once you identify specific AWR tables to disable flushing, you can use an ALTER SYSTEM command:
alter system set “_awr_disabled_flush_tables”=WRH$_FILESTATXS_BL;
alter system set “_awr_disabled_flush_tables”=WRH$_TEMPSTATXS;
alter system set “_awr_disabled_flush_tables”=WRH$_DATAFILE;
alter system set “_awr_disabled_flush_tables”=WRH$_TEMPFILE;
alter system set “_awr_disabled_flush_tables”=WRH$_COMP_IOSTAT;
alter system set “_awr_disabled_flush_tables”=WRH$_SQLSTAT_BL;
alter system set “_awr_disabled_flush_tables”=WRH$_TEMPSTATXS;
alter system set “_awr_disabled_flush_tables”=WRH$_DATAFILE;
alter system set “_awr_disabled_flush_tables”=WRH$_TEMPFILE;
alter system set “_awr_disabled_flush_tables”=WRH$_COMP_IOSTAT;
alter system set “_awr_disabled_flush_tables”=WRH$_SQLSTAT_BL;
You can run a script like this to mark all of the important AWR tables:
WARNING: SETTING UNDOCUMENTED PARAMETERS REQUIRE NOTIFYING MOSC.
set pages 9999;
spool runme.sql
select
‘alter system set “_awr_disabled_flush_tables”=’||table_name||’;’
from
dba_tables
where
tablespace_name = ‘SYSAUX’
and
table_name like ‘WRH_%’;
‘alter system set “_awr_disabled_flush_tables”=’||table_name||’;’
from
dba_tables
where
tablespace_name = ‘SYSAUX’
and
table_name like ‘WRH_%’;
spool off
runme.sql
runme.sql
Removing and Disabling AWR Information
There may be times when a DBA might desire to disable AWR (Automatic Workload Repository).
One reason might be to avoid licensing issues, because AWR isn’t part of the standard or even enterprise database – as it requires the optional (extra cost) Oracle Enterprise Manager (OEM) Diagnostic pack. So even though your database automatically collects AWR data every sixty minutes and retains it for a week – you cannot legally use the Oracle supplied PL/SQL packages (i.e. DBMS_WORKLOAD_REPOSITORY), the OEM screens for AWR, ADMM and ASH, or even the AWR data dictionary views (i.e. DBA_HIST_*) if you’re not licensed.
If you query the DBA_HIST_* data dictionary views, you better have purchased the OEM Diagnostic pack! For those of you who prefer to directly access the SYS data dictionary tables – that means don’t even select from tables with names like WRM$*, WRH$* or WRI$*!
So assuming that you prefer to disable AWR so as not to accidentally (or purposefully) violate your Oracle licensing agreement, here are some ways to disable AWR for a given database (you’ll need to do one of these to every database you manage):
Many Ways to Disable AWR:
1. Download Meta-Link script dbms_awr.plb, compile this package, then execute the PL/SQL package dbms_awr.disable_awr() [Metalink Note 436386.1].
2. Set your init.ora parameter STATISTICS_LEVEL = BASIC
3. Execute the Oracle provided PL/SQL package: dbms_workload_repository.modify_snapshot_settings(interval=>0)
4. Execute the Oracle provided PL/SQL package: dbms_scheduler.disable(‘GATHER_STATS_JOB’)
5. You can use Toad for #3: Main Menu->Database->Monitor->ADDM/AWR Reports screen, choose the Snapshot Management tab, set the interval to all zeroes, and then press the green checkmark in upper left corner to commit the change.
6. You can use Toad for #4: Main Menu->Schema Browser, choose the Sched. Job tab and disable the GATHER_STATS_JOB job.
7. You can use OEM for #4: Main Menu->Workload->Automatic Workload Repository, select the “Edit” button and then select the last radio group item labeled: Turn off Snapshot Collection, finally press OK
8. You can use OEM for #5: Main Menu->Scheduler->Jobs, select the data grid row for GATHER_STATS_JOB, choose the disable drop-down action, then finally press OK
9. Create your own database creation scripts (i.e. do not use DBCA) and make sure not to run the CATAWRTB.sql script [Note – Oracle upgrade process may undo this]
10. Run the $ORACLE_HOME\rdbms\admin\catnoawr.sql script to drop the AWR Repository tables [Note – Oracle upgrade process may undo this]
There may be times when a DBA might desire to disable AWR (Automatic Workload Repository).
One reason might be to avoid licensing issues, because AWR isn’t part of the standard or even enterprise database – as it requires the optional (extra cost) Oracle Enterprise Manager (OEM) Diagnostic pack. So even though your database automatically collects AWR data every sixty minutes and retains it for a week – you cannot legally use the Oracle supplied PL/SQL packages (i.e. DBMS_WORKLOAD_REPOSITORY), the OEM screens for AWR, ADMM and ASH, or even the AWR data dictionary views (i.e. DBA_HIST_*) if you’re not licensed.
If you query the DBA_HIST_* data dictionary views, you better have purchased the OEM Diagnostic pack! For those of you who prefer to directly access the SYS data dictionary tables – that means don’t even select from tables with names like WRM$*, WRH$* or WRI$*!
So assuming that you prefer to disable AWR so as not to accidentally (or purposefully) violate your Oracle licensing agreement, here are some ways to disable AWR for a given database (you’ll need to do one of these to every database you manage):
Many Ways to Disable AWR:
1. Download Meta-Link script dbms_awr.plb, compile this package, then execute the PL/SQL package dbms_awr.disable_awr() [Metalink Note 436386.1].
2. Set your init.ora parameter STATISTICS_LEVEL = BASIC
3. Execute the Oracle provided PL/SQL package: dbms_workload_repository.modify_snapshot_settings(interval=>0)
4. Execute the Oracle provided PL/SQL package: dbms_scheduler.disable(‘GATHER_STATS_JOB’)
5. You can use Toad for #3: Main Menu->Database->Monitor->ADDM/AWR Reports screen, choose the Snapshot Management tab, set the interval to all zeroes, and then press the green checkmark in upper left corner to commit the change.
6. You can use Toad for #4: Main Menu->Schema Browser, choose the Sched. Job tab and disable the GATHER_STATS_JOB job.
7. You can use OEM for #4: Main Menu->Workload->Automatic Workload Repository, select the “Edit” button and then select the last radio group item labeled: Turn off Snapshot Collection, finally press OK
8. You can use OEM for #5: Main Menu->Scheduler->Jobs, select the data grid row for GATHER_STATS_JOB, choose the disable drop-down action, then finally press OK
9. Create your own database creation scripts (i.e. do not use DBCA) and make sure not to run the CATAWRTB.sql script [Note – Oracle upgrade process may undo this]
10. Run the $ORACLE_HOME\rdbms\admin\catnoawr.sql script to drop the AWR Repository tables [Note – Oracle upgrade process may undo this]
If you want to rebuild the AWR Repository Tables later, you need to perform the following:
– Execute (again) the script $ORACLE_HOME/rdbms/admin/catnoawr.sql
– Execute the script $ORACLE_HOME/rdbms/admin/catawrtb.sql
– Bounce the database.
– On re-start of the database instance, the AWR tables will be populated with the required data.
– Execute (again) the script $ORACLE_HOME/rdbms/admin/catnoawr.sql
– Execute the script $ORACLE_HOME/rdbms/admin/catawrtb.sql
– Bounce the database.
– On re-start of the database instance, the AWR tables will be populated with the required data.
- General Guidelines for SYSAUX Space Issues [Document 552880.1]
- SYSAUX Tablespace Grows Heavily Due To AWR [Document 852028.1]
- SYSAUX Grows Because Optimizer Stats History is Not Purged [Document 1055547.1]
- Space issue in Sysaux tablespace due to Unexpected AWR size [Document 1218413.1]
- Space Management In Sysaux Tablespace with AWR in Use [Document 287679.1]
- Usage and Storage Management of SYSAUX tablespace occupants SM/AWR, SM/ADVISOR, SM/OPTSTAT and SM/OTHER [Document 329984.1]
