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 🙂

This Post Has 5 Comments

Chris_C 26 Aug 2008 Reply

Also remember this is included in the rda output when you upload the files to support
Amit 26 Aug 2008 Reply

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.
Theetha 1 Oct 2008 Reply

Hi Amit,

Good work, seems like you continue to contribute to KM in different means..
All the best !
Amit 2 Oct 2008 Reply

Thanks Theetha..yeah had to find some alternatives 🙂
Leo James 15 Oct 2008 Reply

Very valuable information Amit.

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