Checking Database Feature Usage Stats

Today I came across view DBA_FEATURE_USAGE_STATISTICS (10g) which let’s us know whether particular Database Feature has been used till now or not. If yes, then it also let’s us know when it was used for the first time and also the last usage time. This can be helpful to check if anyone is using Database feature which is not licensed .

I have used filter DETECTED_USAGES >0 for checking the features which has been used in this Database.

<span style="font-size: x-small; font-family: helvetica;"><span style="font-size: small;">SQL&gt;  SELECT NAME,DETECTED_USAGES AS "USAGE",CURRENTLY_USED,FIRST_USAGE_DATE,LAST_USAGE_DATE
  2  FROM DBA_FEATURE_USAGE_STATISTICS WHERE DETECTED_USAGES &gt;0 order by 1;

NAME                                               USAGE CURRE FIRST_USA LAST_USAG
--------------------------------------------- ---------- ----- --------- ---------
Automatic SQL Execution Memory                         2 TRUE  14-AUG-08 21-AUG-08
Automatic SQL Execution Memory                         1 TRUE  13-AUG-08 13-AUG-08
Automatic Segment Space Management (system)            2 TRUE  14-AUG-08 21-AUG-08
Automatic Segment Space Management (system)            1 TRUE  13-AUG-08 13-AUG-08
Automatic Segment Space Management (user)              1 TRUE  13-AUG-08 13-AUG-08
Automatic Storage Manager                              2 TRUE  14-AUG-08 21-AUG-08
Automatic Storage Manager                              1 TRUE  13-AUG-08 13-AUG-08
Automatic Undo Management                              2 TRUE  14-AUG-08 21-AUG-08
Automatic Undo Management                              1 TRUE  13-AUG-08 13-AUG-08
Character Set                                          2 TRUE  14-AUG-08 21-AUG-08
Character Set                                          1 TRUE  13-AUG-08 13-AUG-08
Dynamic SGA                                            1 TRUE  13-AUG-08 13-AUG-08
Internode Parallel Execution                           2 TRUE  14-AUG-08 21-AUG-08
Locally Managed Tablespaces (system)                   1 TRUE  13-AUG-08 13-AUG-08
Locally Managed Tablespaces (system)                   2 TRUE  14-AUG-08 21-AUG-08
Locally Managed Tablespaces (user)                     1 TRUE  13-AUG-08 13-AUG-08
Locally Managed Tablespaces (user)                     2 TRUE  14-AUG-08 21-AUG-08
Parallel SQL Query Execution                           2 TRUE  14-AUG-08 21-AUG-08
Partitioning (system)                                  1 TRUE  13-AUG-08 13-AUG-08
Partitioning (system)                                  2 TRUE  14-AUG-08 21-AUG-08
Protection Mode - Maximum Performance                  1 TRUE  13-AUG-08 13-AUG-08
Protection Mode - Maximum Performance                  2 TRUE  14-AUG-08 21-AUG-08
Real Application Clusters (RAC)                        2 TRUE  14-AUG-08 21-AUG-08
Real Application Clusters (RAC)                        1 TRUE  13-AUG-08 13-AUG-08
Recovery Area                                          1 TRUE  13-AUG-08 13-AUG-08
Recovery Area                                          2 TRUE  14-AUG-08 21-AUG-08
Segment Advisor                                        2 TRUE  14-AUG-08 21-AUG-08
Server Parameter File                                  2 TRUE  14-AUG-08 21-AUG-08
Server Parameter File                                  1 TRUE  13-AUG-08 13-AUG-08
Streams (system)                                       2 TRUE  14-AUG-08 21-AUG-08
Streams (system)                                       1 TRUE  13-AUG-08 13-AUG-08
Streams (user)                                         1 TRUE  13-AUG-08 13-AUG-08
Streams (user)                                         2 TRUE  14-AUG-08 21-AUG-08
Virtual Private Database (VPD)                         2 TRUE  14-AUG-08 21-AUG-08
Virtual Private Database (VPD)                         1 TRUE  13-AUG-08 13-AUG-08
XDB                                                    2 TRUE  14-AUG-08 21-AUG-08

36 rows selected.

</span></span>

So be careful when you use any Licensed feature (Unless you have already bought it) like Partitioning, AWR,Database Replay as this auditing is enabled by default ๐Ÿ™‚

Amit Bansal

Experienced professional with 16 years of expertise in database technologies. In-depth knowledge of designing and implementation of Disaster Recovery / HA solutions, Database Migrations , performance tuning and creating technical solutions. Skills: Oracle,MySQL, PostgreSQL, Aurora, AWS, Redshift, Hadoop (Cloudera) , Elasticsearch, Python

This Post Has 5 Comments

  1. Chris_C

    Also remember this is included in the rda output when you upload the files to support

  2. Amit

    Thanks Chris. I was not aware of this . Good point ๐Ÿ™‚
    For others, they can check RDA and click RDBMS and then you will find link “Feature Usage Statistics”. This links to files called RDA_DBA_vfeatureusage.htm and RDA_DBA_vfeatureusage.txt which contain this data.

  3. Theetha

    Hi Amit,

    Good work, seems like you continue to contribute to KM in different means..
    All the best !

  4. Amit

    Thanks Theetha..yeah had to find some alternatives ๐Ÿ™‚

  5. Leo James

    Very valuable information Amit.

    I will keep visiting here. Keep the good work. Information presented here is very clear.

Leave a Reply

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