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
Feb 18, 2019 07:02 AM