Deactivate Oracle Diagnostic Pack

[Detelin_Nedev]

Dear Experts,

I have a requirement to deactivate the Oracle Diagnostic and Tuning Packs and delete the data that are generated by these packs.

Addition to the parameter CONTROL_MANAGEMENT_PACK_ACCESS to be set to NONE (Oracle 11g) and the entry in the table ORA_FEAT_USED for Tuning Pack, what about the deletion of the data:

Is it enough just to delete the content of the tables DBA_HIST?

Thank you,

Best regards,

Detelin

[stefan_koehler]

Hi Detelin,

well it is pretty easy.

1. Set init parameter "CONTROL_MANAGEMENT_PACK_ACCESS" to NONE

2. Recreate AWR by using the following procedure

SQL> shutdown immediate
SQL> startup restrict
-- On both 10g and 11g 
SQL> @?/rdbms/admin/catnoawr.sql
SQL> alter system flush shared_pool;
SQL> @?/rdbms/admin/catawrtb.sql
SQL> @?/rdbms/admin/utlrp.sql
--On 11g it is necessary to also run:
SQL> @?/rdbms/admin/execsvrm.sql
SQL> exec dbms_workload_repository.modify_snapshot_settings(interval=>0);

Regards

Stefan