pat98's always & forever

아래와 같은 script 로 oracle 옵션의 사용유무를 Check 할수 있다.

dba_feature_usage_statistics 뷰를 사용한다.

====================================================

SET LINESIZE 180;

SET PAGESIZE 1000;

SET FEEDBACK OFF;

SET COLSEP '|';

WHENEVER SQLERROR EXIT SQL.SQLCODE;

COL "Host Name" FORMAT A30;

COL "Option/Management Pack" FORMAT A60;

COL "Used" FORMAT A5;

with features as(

select a OPTIONS, b NAME  from

(

select 'Active Data Guard' a,  'Active Data Guard - Real-Time Query on Physical Standby' b from dual

union all 

select 'Advanced Compression', 'HeapCompression' from dual

union all

select 'Advanced Compression', 'Backup BZIP2 Compression' from dual

union all 

select 'Advanced Compression', 'Backup DEFAULT Compression' from dual

union all 

select 'Advanced Compression', 'Backup HIGH Compression' from dual

union all

select 'Advanced Compression', 'Backup LOW Compression' from dual

union all

select 'Advanced Compression', 'Backup MEDIUM Compression' from dual

union all

select 'Advanced Compression', 'Backup ZLIB, Compression' from dual

union all

select 'Advanced Compression', 'SecureFile Compression (user)' from dual

union all

select 'Advanced Compression', 'SecureFile Deduplication (user)' from dual

union all

select 'Advanced Compression',        'Data Guard' from dual

union all

select 'Advanced Compression', 'Oracle Utility Datapump (Export)' from dual

union all

select 'Advanced Compression', 'Oracle Utility Datapump (Import)' from dual

union all

select 'Advanced Security', 'ASO native encryption and checksumming' from dual

union all

select 'Advanced Security', 'Transparent Data Encryption' from dual

union all

select 'Advanced Security', 'Encrypted Tablespaces' from dual

union all

select 'Advanced Security', 'Backup Encryption' from dual

union all

select 'Advanced Security', 'SecureFile Encryption (user)' from dual

union all

select 'Change Management Pack', 'Change Management Pack (GC)' from dual

union all

select 'Data Masking Pack', 'Data Masking Pack (GC)' from dual

union all

select 'Data Mining', 'Data Mining' from dual

union all

select 'Diagnostic Pack',   'Diagnostic Pack' from dual

union all

select 'Diagnostic Pack',   'ADDM' from dual

union all

select 'Diagnostic Pack',   'AWR Baseline' from dual

union all

select 'Diagnostic Pack',   'AWR Baseline Template' from dual

union all

select 'Diagnostic Pack',   'AWR Report' from dual

union all

select 'Diagnostic Pack',   'Baseline Adaptive Thresholds' from dual

union all

select 'Diagnostic Pack',   'Baseline Static Computations' from dual

union all

select 'Tuning  Pack',   'Tuning Pack' from dual

union all

select 'Tuning  Pack',   'Real-Time SQL Monitoring' from dual

union all

select 'Tuning  Pack',   'SQL Tuning Advisor' from dual

union all

select 'Tuning  Pack',   'SQL Access Advisor' from dual

union all

select 'Tuning  Pack',   'SQL Profile' from dual

union all

select 'Tuning  Pack',   'Automatic SQL Tuning Advisor' from dual

union all

select 'Database Vault',   'Oracle Database Vault' from dual

union all

select 'WebLogic Server Management Pack Enterprise Edition',   'EM AS Provisioning and Patch Automation (GC)' from dual

union all

select 'Configuration Management Pack for Oracle Database',   'EM Config Management Pack (GC)' from dual

union all

select 'Provisioning and Patch Automation Pack for Database',   'EM Database Provisioning and Patch Automation (GC)' from dual

union all

select 'Provisioning and Patch Automation Pack',   'EM Standalone Provisioning and Patch Automation Pack (GC)' from dual

union all

select 'Exadata',   'Exadata' from dual

union all

select 'Label Security',   'Label Security' from dual

union all

select 'OLAP',   'OLAP - Analytic Workspaces' from dual

union all

select 'Partitioning',   'Partitioning (user)' from dual

union all

select 'Real Application Clusters',   'Real Application Clusters (RAC)' from dual

union all

select 'Real Application Testing',   'Database Replay: Workload Capture' from dual

union all

select 'Real Application Testing',   'Database Replay: Workload Replay' from dual

union all

select 'Real Application Testing',   'SQL Performance Analyzer' from dual

union all

select 'Spatial' ,'Spatial (Not used because this does not differential usage of spatial over locator, which is free)' from dual

union all

select 'Total Recall', 'Flashback Data Archive' from dual

)

)

select t.o "Option/Management Pack", 

       t.u "Used",

       d.DBID "DBID",

       d.name "DB Name",

       i.version "DB Version",

       i.host_name "Host Name",

       to_char(sysdate, 'YYYY-MM-DD HH24:MI:SS') "ReportGen Time"

from

(select OPTIONS o, DECODE(sum(num),0,'NO','YES') u

from

(

select f.OPTIONS OPTIONS, case

                   when f_stat.name is null then 0

                   when ( ( f_stat.currently_used = 'TRUE' and

                            f_stat.detected_usages > 0 and

                            (sysdate - f_stat.last_usage_date) < 366 and

                            f_stat.total_samples > 0

                          )

                          or 

                          (f_stat.detected_usages > 0 and 

                          (sysdate - f_stat.last_usage_date) < 366 and

                          f_stat.total_samples > 0)

                        ) and 

                        ( f_stat.name not in('Data Guard', 'Oracle Utility Datapump (Export)', 'Oracle Utility Datapump (Import)')

                          or

                          (f_stat.name in('Data Guard', 'Oracle Utility Datapump (Export)', 'Oracle Utility Datapump (Import)') and

                           f_stat.feature_info is not null and trim(substr(to_char(feature_info), instr(to_char(feature_info), 'compression used: ',1,1) + 18, 2)) != '0')

                        )

                        then 1

                   else 0

                  end num

  from features f,

       sys.dba_feature_usage_statistics f_stat

where f.name = f_stat.name(+)

) group by options) t,

  v$instance i,

  v$database d

order by 2 desc,1 

;

exit;