cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 

Summary

This article answers question on how FNMS detects options/packs and the usage from cloned Oracle database

Synopsis

This article explains how inventory works from a closed database in more detail.

Discussion

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
              FROM DBA_FEATURE_USAGE_STATISTICS
              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
 
            SELECT
            '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"
            FROM OPTION_DATA
Was this article helpful? Yes No
No ratings
Version history
Last update:
‎Feb 18, 2019 07:02 AM
Updated by: