適用於：
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

oracle 10g 禁用和解除安裝AWR

相關文章