Disabling and Uninstalling AWR (Doc ID 1909073.1)

 

Some reference on it?
– How To Avoid the AWR Usage Without Having Diagnostic Pack License (Doc ID 2276199.1)
– Controlling Diagnostic and Tuning Pack Usage (Doc ID 436386.1)
– Disabling and Uninstalling AWR (Doc ID 1909073.1)

SELECT name, detected_usages detected,
total_samples samples, currently_used used,
to_char(last_sample_date,’MMDDYYYY:HH24:MI’) last_sample,
sample_interval interval
FROM dba_feature_usage_statistics
WHERE name = ‘Automatic Workload Repository’;

NAME DETECTED SAMPLES USED LAST_SAMPLE INTERVAL
—————————— ———- ———- —– ————– ———-
Automatic Workload Repository 0 1 FALSE 01092024:22:31 604800

 

SQL> show parameter control_management_pack_access

NAME TYPE VALUE
———————————— ———– ——————————
control_management_pack_access string DIAGNOSTIC+TUNING
SQL>
SQL>
SQL>
SQL> select name from v$database;

NAME
——————————
TEST

SQL> ALTER SYSTEM SET control_management_pack_access=NONE;

System altered.

SQL> select client_name, operation_name, status from dba_autotask_operation;

CLIENT_NAME OPERATION_NAME STATUS
—————————————————————- —————————————————————- ——–
auto optimizer stats collection auto optimizer stats job DISABLED
auto space advisor auto space advisor job DISABLED
sql tuning advisor automatic sql tuning task DISABLED

 

[oracle@dbhost tmp]$ cp dbmsnoawr.plb /u01/app/oracle/patch-jdk/
[oracle@dbhost tmp]$ cd /u01/app/oracle/patch-jdk/
[oracle@dbhost patch-jdk]$ ls
35638318 dbmsnoawr.plb PatchSearch.xml
[oracle@dbhost patch-jdk]$ ls -l
total 8
drwxr-xr-x 4 oracle oinstall 67 Jan 5 17:25 35638318
-rw-r–r– 1 oracle oinstall 2369 Jan 10 10:47 dbmsnoawr.plb
-rw-rw-r– 1 oracle oinstall 2475 Oct 17 21:21 PatchSearch.xml
[oracle@dbhost patch-jdk]$ !sq
sqlplus / as sysdba;

SQL*Plus: Release 19.0.0.0.0 – Production on Wed Jan 10 10:47:45 2024
Version 19.14.0.0.0

Copyright (c) 1982, 2021, Oracle. All rights reserved.

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 – Production
Version 19.14.0.0.0

SQL> select name from v$database;

NAME
———
TEST

SQL> @dbmsnoawr.plb

Package created.

Package body created.

SQL> begin dbms_awr.disable_awr(); end;
2 /

PL/SQL procedure successfully completed.