oracle 10g 禁用和卸载AWR

适用于：
Oracle database 企业版 10.2.0.1 和之前的版本
目的：
如果sysuax表空间由于awr报告消耗过多的空间，awr报告可以禁用或者卸载，释放sysaux表空间的空间。
解决方案：
awr默认是启用的，因为数据库的许多新特性，如Automatic segment advisor and Undo advisor捕获的信息都保持在awr中，这些新特性，可以通过访问awr一些试图获得。
在Metalink Note：1909073.1中，Oracle提供了一个增强脚本，称为： dbmsnoawr.plb
通过这个脚本可以启用和禁用AWR采样功能.

Rem
Rem dbmsnoawr.sql
Rem
Rem Copyright (c) 2006, Oracle. All rights reserved.
Rem
Rem    NAME
Rem      dbmsnoawr.sql - Declaration of the DBMS_AWR package
Rem
Rem    DESCRIPTION
Rem      Utilities for disabling and getting status of AWR
Rem
Rem    NOTES
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    gwood       04/13/07 - created
Rem
create or replace package dbms_awr as
--    PACKAGE dbms_awr
--     This package allows users to disable AWR functionality in a Oracle 10g+ database.
--     The use of this package is not resticted by licencing of the Diagnostic Pack.
--     Additionally this package contains two functions that can be used to determine
--        if AWR is currently enabled.
--
--    PROCEDURE dbms_awr.disable_awr
--    PURPOSE: turns off collections into Automatic Workload Repository
--    PARAMETERS: none
  procedure disable_awr;
--    PROCEDURE dbms_awr.enable_awr
--    PURPOSE: turns on collections into Automatic Workload Repository. The capture interval
--        is set to the default of 60 minutes.
--    PARAMETERS: none
  procedure enable_awr;
--    FUNCTION dbms_awr.awr_enabled
--    PURPOSE: Returns TRUE if Automatic Workload Repository is performing periodic capture.
--             Returns FALSE if Automatic Workload Repository periodic capture is disabled.
--    PARAMETERS: none
  function awr_enabled return boolean;
--    FUNCTION dbms_awr.awr_status
--    PURPOSE: Returns 'ENABLED' if Automatic Workload Repository is performing periodic capture.
--             Returns 'DISABLED' if Automatic Workload Repository periodic capture is disabled.
--    PARAMETERS: none
  function awr_status return varchar2;
end dbms_awr;
/

create or replace package body dbms_awr wrapped
a000000
b2
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
b
27b 1de
XeDco+SpVfG9KEZ2ikXc00yhW88wg2P3AK7bfHRAWE7VX0b1S25KKJCp5VrehjNR9oaXoWT1
GGfYVnyl/lLyux308Fmhfp1y9pjrQyux50RY8xmHmiSG2bFbFs2Upn6MLYcfsqsW+joOTKYe
4TyFpXVqzVWS+Tjt8bcmSiai64IVcdOB3Q7Y6kQ8PGwqXqAiy9sFQKD0X6RC/ePGAQzUKwvS
3L8/hKgjdK9Fgw8bb7v1HTq22OJlAv+R/DYCSK57rPmAkyx/XLuXcPo3hcYs8fvUUAO33szW
gy5zNau9U7xiyAOExBz9Vh0U7EaMRl6rLr6UXpk/0tk3BW0W/GVo3XfdSzUpGN5aKa1xF2Yh
trcMV3KuK/FfIpy0bNDxSQ3LFuOsB8i5xzhj/dCqMxT4dO2awc0hnP3XeLhxWDvcEuqdkR9O
u+Z6US/LtRISXt2I8zFq6/aDSuOXTor9KQ1jYA==

/

 

Installing the Package:

To install, run the package as SYS from SQL*Plus:

SQL> @dbmsnoawr.plb

To execute the package, use the command:

SQL> begin dbms_awr.disable_awr(); end;

 

Note:

Some database features may become unavailable because of their dependence on the AWR.
For example, the Automatic Segment Advisor will be turned off.
However, they  can still be invoked manually.

Before the feature is disabled,  the view dba_feature_usage_statistics can be used to check how often AWR was utilized:

 

SQL v10204> SELECT name,
  2              detected_usages detected,
            total_samples   samples,
  3    4              currently_used  used,
  5              to_char(last_sample_date,'MMDDYYYY:HH24:MI') last_sample,
  6              sample_interval interval
  7         FROM dba_feature_usage_statistics
      WHERE name = 'Automatic Workload Repository';
  8

NAME                   DETECTED    SAMPLES USED  LAST_SAMPLE      INTERVAL
-------------------- ---------- ---------- ----- -------------- ----------
Automatic Workload          15        169 FALSE 05012012:23:22     604800
Repository

The output shows AWR was detected 15 times with 169 samples. 

卸载 AWR  

It is not recommended to uninstall AWR. 

However, for those users who wish to uninstall AWR, they can use the following method:

sqlplus /nolog
connect / as sysdba
show parameters statistics_level
alter system set statistics_level=basic scope=spfile;
shutdown immediate
startup restrict
$ORACLE_HOME/rdbms/admin/catnoawr 
shutdown immediate
startup

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/12798004/viewspace-1762228/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/12798004/viewspace-1762228/