How to list all my Oracle Products from Database park?

This is part of DBA role: know and prospect the use of Oracle Products for Oracle contract periodical review, isn’t?
It usually represent a huge problem, or, at least, demands a long time to refresh your spread sheet…

Well, If you use OEM, we offer you a better option! 😀
(I said ‘we’, because Dieison Santos came to me with this problem theese days…. So we talked about, I gave some directives and he mainly solved the problem. This way, great part of ‘we’ should be ‘he’… haha)

Without further, here’s a query that can map your environment (at least your Oracle database products):
You can use it to automate a report and set thresholds. Be creative… 😉

PS: From now, I’ll post all in english. Just for fun.

distinct(ddi.host_name) "Host",
when like '%Active Data Guard%' then 'Oracle Active Data Guard'
when like '%Advanced Compression%' then 'Oracle Advanced Compression'
when like '%Audit Vault%' then 'Oracle Audit Vault'
when like '%Database Vault%' then 'Oracle Database Vault'
when like '%Partitioning (User)%' then 'Oracle Partitioning'
when like '%Real Application Clusters%' then 'Oracle Real Application Clusters'
when like '%Real Application Testing%' then 'Oracle Real Application Testing'
when ( like '%ADDM%' or like '%Automatic Database Diagnostic Monitor%' or like '%Automatic Workload Repository%' or like '%AWR%' or like '%Baseline%' or like '%Diagnostic Pack%' ) then 'Oracle Diagnostic Pack'
when ( like '%SQL Monitoring%' or like '%SQL Performance%' or like '%SQL Performance%' or like '%SQL Profile%' or like '%SQL Tuning%' or like '%SQL Access Advisor%' or like '%Tuning Pack%') then 'Oracle Tuning Pack'
when like '%Change Management Pack%' then 'Oracle Change Management Pack'
when ddi.edition like 'Enterprise Edition' then 'Oracle Database Enterprise Edition'
end) "Produto Oracle",
hcd.num_cores "Cores",
ohs.virtual "Virtual",
hcd.impl "Processador",
ddi.dbversion "Versao"
mgmt$hw_cpu_details hcd,
mgmt$os_hw_summary ohs,
mgmt$db_dbninstanceinfo ddi,
h.host_name as host,
h.target_name as database_name,
i.instance_name as instance_name,
h.target_type   as target_type,
h.target_guid as target_guid,
mgmt_db_featureusage f,
mgmt_targets h,
mgmt_db_dbninstanceinfo_ecm i,
gc$ecm_gen_snapshot s
s.is_current = 'Y' and
s.snapshot_guid = i.ecm_snapshot_id and
s.target_guid = f.target_guid and
h.target_type in ('oracle_database','rac_database') and
s.target_type = h.target_type and
s.snapshot_type in ('oracle_dbconfig','oracle_racconfig') and
) opt
and ohs.host_name=ddi.host_name
and ddi.target_guid=opt.target_guid
and ( like '%Active Data Guard%' -- Active Data Guard
or like '%Advanced Compression%' -- Advanced Compression
or like '%Audit Vault%' -- Audit Vault
or like '%Database Vault%' -- DB Vault
or like '%Partitioning (user)%' -- Partitioning
or like '%Real Application Clusters%' --RAC
or like '%Real Application Testing%' -- RAT
or like '%ADDM%' -- Diagnostic Pack
or like '%Automatic Database Diagnostic Monitor%' -- Diagnostic Pack
or like '%Automatic Workload Repository%' -- Diagnostic Pack
or like '%AWR%' -- Diagnostic Pack
or like '%Baseline%' --  Diagnostic Pack
or like '%Diagnostic Pack%' -- Diagnostic Pack
or like '%SQL Monitoring%' -- Tuning Pack
or like '%SQL Performance%' -- Tuning Pack
or like '%SQL Profile%' -- Tuning Pack
or like '%SQL Tuning%' -- Tuning Pack
or like '%SQL Access%' -- Tuning Pack
or like '%Tuning Pack%' -- Tuning Pack
or like '%Change Management Pack%' -- Change Management Pack
or ddi.edition like 'Enterprise Edition')
order by ddi.host_name;


2 thoughts on “How to list all my Oracle Products from Database park?

  1. i am not able to run this query as i am getting error like gc$ecm_gen_snapshot object does not exists.
    could you please let me know possible reason ? i am executing query by sys user

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.