This article answers question on how FNMS detects options/packs and the usage from cloned Oracle database
This article explains how inventory works from a closed database in more detail.
Every time the DB is cloned in oracle it creates a new DBID for the new database. The way we are handling the cloning part is by checking the DBA_FEATURE_USAGE_STATISTICS table . We query the last sample date and pick the max one from that table . Then we find the associated DBID for that date and collect the usage stats for that DBID only.
-- Query the DBA_FEATURE_USAGE_STATISTICS for the max LAST_SAMPLE_DATE
WITH OPTION_DATA as (
SELECT DBID, MAX(LAST_SAMPLE_DATE) as LAST_SAMPLE_DATE,
SUM(DECODE(CURRENTLY_USED,'TRUE',1,0)) as USED,
MIN(FIRST_USAGE_DATE) as First_Usage_Date, MAX(Last_Usage_Date) as Last_Usage_Date,
SUM(Detected_Usages) AS Total_Detected_Usages
WHERE NAME IN ('Data Masking Pack (GC)')
AND LAST_SAMPLE_DATE = (SELECT MAX(LAST_SAMPLE_DATE) FROM DBA_FEATURE_USAGE_STATISTICS) - - here is where we are filtering the data
GROUP BY DBID
-- get the data only for that DBID
'Data Masking Pack' as "Option",
COALESCE(Used,0) AS "Installed",
COALESCE(Used,0) AS "Used",
DBID AS "DBID",
Last_Sample_Date AS "LastSampleDate",
First_Usage_Date AS "FirstUsageDate",
Last_Usage_Date AS "LastUsageDate",
COALESCE(Total_Detected_Usages,0) AS "TotalDetectedUsages"