Thursday, October 1, 2009

Useful query to find out Oracle Licensing Option usage

Below SQL statement is useful to comply Oracle licensing. It also as a useful tools to check whether specific option has been used before it can be safely removed/deinstalled.


SQL> desc DBA_FEATURE_USAGE_STATISTICS
Name Null? Type
----------------------------------------- -------- -----------------
DBID NOT NULL NUMBER
NAME NOT NULL VARCHAR2(64)
VERSION NOT NULL VARCHAR2(17)
DETECTED_USAGES NOT NULL NUMBER
TOTAL_SAMPLES NOT NULL NUMBER
CURRENTLY_USED VARCHAR2(5)
FIRST_USAGE_DATE DATE
LAST_USAGE_DATE DATE
AUX_COUNT NUMBER
FEATURE_INFO CLOB
LAST_SAMPLE_DATE DATE
LAST_SAMPLE_PERIOD NUMBER
SAMPLE_INTERVAL NUMBER
DESCRIPTION VARCHAR2(128)


SQL> select name,last_usage_date from dba_feature_usage_statistics
2 where detected_usages>0 order by 1;

NAME LAST_USAG
------------------------------------------------------- ---------
Audit Options 23-SEP-09
Automatic Maintenance - Optimizer Statistics Gathering 23-SEP-09
Automatic Maintenance - SQL Tuning Advisor 23-SEP-09
Automatic Maintenance - Space Advisor 23-SEP-09
Automatic Memory Tuning 23-SEP-09
Automatic SQL Tuning Advisor 25-JUN-09
Automatic Segment Advisor 25-JUN-09
Automatic Segment Space Management (system) 23-SEP-09
Automatic Segment Space Management (user) 23-SEP-09
Automatic Undo Management 23-SEP-09
Automatic Workload Repository 11-MAY-09

NAME LAST_USAG
------------------------------------------------------- ---------
Backup BZIP2 Compression 23-SEP-09
Character Set 23-SEP-09
Client Identifier 14-MAR-09
Dynamic SGA 22-MAR-09
EM Database Control 11-MAY-09
Extensibility 23-SEP-09
LOB 23-SEP-09
Locally Managed Tablespaces (system) 23-SEP-09
Locally Managed Tablespaces (user) 23-SEP-09
Materialized Views (User) 23-SEP-09
Object 23-SEP-09

NAME LAST_USAG
------------------------------------------------------- ---------
Oracle Managed Files 23-SEP-09
Parallel SQL DDL Execution 14-MAR-09
Partitioning (system) 23-SEP-09
RMAN - Disk Backup 23-SEP-09
Recovery Area 23-SEP-09
Recovery Manager (RMAN) 23-SEP-09
Resource Manager 20-MAY-09
Restore Point 23-SEP-09
Segment Advisor 30-AUG-09
Server Parameter File 23-SEP-09
Services 23-SEP-09

NAME LAST_USAG
------------------------------------------------------- ---------
XDB 23-SEP-09