CODEWITHSUNDEEP
javasqloracle-databaseoracle11goracle10g
gravetii
gravetii

Reputation: 9634

How can I know if Oracle Diagnostics pack and the Oracle Tuning pack are installed with an Oracle installation?

As part of a project, I need to figure out if the oracle installation has the oracle diagnostics pack and the oracle tuning pack installed. Could anyone help me out as to how that can be done? Also, if present how can i disable them?

I am using a windows machine.

Edit: From this document I see that a new initialization parameter, CONTROL_MANAGEMENT_PACK_ACCESS is to be set to NONE to disable the Oracle diagnostics pack and the Oracle tuning pack. Could anyone tell me where I need to set this parameter?

Thanks

Upvotes: 4

Views: 32478

Answers (1)

Jon Heller
Jon Heller

Reputation: 36892

Query the initialization parameter control_management_pack_access to determine if the Diagnostic and Tuning packs are enabled:

select * from v$parameter where name = 'control_management_pack_access';

The value can be changed like this:

alter system set control_management_pack_access = none;

Possible values are NONE, DIAGNOSTIC, and DIAGNOSTIC+TUNING. The default is DIAGNOSTIC+TUNING.

But those values do not mean you have licensed the product. And changing the value does not seem to disable the products.

Instead of disabling, you can monitor them and ensure nobody is using them. Except it's difficult to know exactly which features are in each pack, and difficult to know if those features are used and who is using them and when. The view DBA_FEATURE_USAGE_STATISTICS may help. I've tried to pick the relevant features but I've certainly made mistakes:

select name, detected_usages, last_usage_date, last_sample_date
from dba_feature_usage_statistics
where name in (
    'ADDM', 'Automatic SQL Tuning Advisor', 'Automatic Workload Repository',
    'AWR Baseline', 'AWR Baseline Template', 'AWR Report', 'EM Performance Page',
    'Real-Time SQL Monitoring', 'SQL Access Advisor',
    'SQL Monitoring and Tuning pages', 'SQL Performance Analyzer',
    'SQL Tuning Advisor', 'SQL Tuning Set (system)', 'SQL Tuning Set (user)'
)
order by name;

NAME                            DETECTED_USAGES LAST_USAG LAST_SAMP
------------------------------- --------------- --------- ---------
ADDM                                          0           05-JAN-14
AWR Baseline                                  0           05-JAN-14
AWR Baseline Template                         0           05-JAN-14
AWR Report                                    0           05-JAN-14
Automatic SQL Tuning Advisor                 24 05-JAN-14 05-JAN-14
Automatic Workload Repository                 0           05-JAN-14
EM Performance Page                           0           05-JAN-14
Real-Time SQL Monitoring                     24 05-JAN-14 05-JAN-14
SQL Access Advisor                            0           05-JAN-14
SQL Monitoring and Tuning pages               0           05-JAN-14
SQL Performance Analyzer                      0           05-JAN-14
SQL Tuning Advisor                            0           05-JAN-14
SQL Tuning Set (system)                       0           05-JAN-14
SQL Tuning Set (user)                         0           05-JAN-14

But to use DBA_FEATURE_USAGE_STATISTICS you must purchase the License Pack. Just kidding.

This is all incredibly confusing. I've never seen an organisation put much effort into it.

Upvotes: 5

Related Questions