Home
E-mail Us
Oracle Articles
New Oracle Articles

Oracle Training
Oracle Tips
Oracle Forum
Class Catalog

Remote DBA
Oracle Tuning
Emergency 911
RAC Support
Apps Support
Analysis
Design
Implementation
Oracle Support

SQL Tuning
Security
Oracle UNIX
Oracle Linux
Monitoring
Remote support
Remote plans
Remote services
Application Server
Applications
Oracle Forms
Oracle Portal
App Upgrades
SQL Server
Oracle Concepts
Software Support
Remote Support
Development
Implementation

Consulting Staff
Consulting Prices
Help Wanted!

Oracle Posters
Oracle Books
Oracle Scripts
Ion
Excel-DB

Don Burleson Blog

Script to find features used by enterprise edition

Oracle Tips by Burleson

May 29, 2016

Question: What do I do to see if an enterprise edition (EE) database can be moved to standard edition (SE)? I want to see if my database is using any enterprise edition features and I would like a script to detect these features. How do I find out if I can migrate to standard edition?

Answer: First, see here for a list of the Enterprise edition features. Each of these features needs to be checked against the data dictionary to see what features are used. Also see the dba_features_usage_statistics table.

Overall options used
Overall Features used
Using Data Guard
Using data Guard redo apply (physical standby)
Using Data Guard SQL Apply (logical standby)
Using Active Data Guard
Using Bitmap Indexes
Using AWR
Using Flashback Database
Using MTS (shared servers)
Using Bitmap join indexes
Using Multi-master replication
12c using multitenant
Count of IOT's by schema
Fine grained auditing used
Using instance caging
Count of materialized views
Count function-based Indexes by schema
Count virtual private databases by schema
Detect parallel query installed
Count parallel tables and indexes by schema
Detect transportable tablespaces
Detect Exadata in-memory features
Detect SQL plan management
Detect Oracle Streams
12c detect Exadata Zone Maps
Detect 12c adaptive SQL execution plans
Result cache installed
Client side query cache installed
12c advanced index compression used
12c cell flash cache used
12c sharded queues used
Detect replication

This part of the script detects extra-cost packages

Using RAC
Using RAC One Node
Using Partitioning
Using OLAP
Using Data Mining
Using Oracle Spatial
Using Compression
Database Vault Installed
Using label security
Using Oracle Spatial

For a complete license review of your database to see can run using standard edition, contact us and we can analyze your existing database and see if you can migrate to SE.

Script to detect features and options

spool f.lst

set pages 999

prompt *******************************************
prompt Get the instance name
prompt *******************************************

select instance_name from v$instance;

prompt *******************************************

prompt checking for options installed

prompt *******************************************

col name format a50 heading "Option"

col value format a10 heading "Installed?" justify center wrap

break on value dup skip 1

select

parameter name,

value

from
See code depot for full script

v$option

order by

2 desc,

clear breaks

prompt *******************************************

prompt Checking for EE features used

prompt *******************************************

col c1 heading 'Feature Name' format a45

col c2 heading 'Detected|Usages' format 999,999

col c3 heading 'First|Usage|Date' format a10

col c4 heading 'Last|Usage|Date' format a10

select

name c1,

detected_usages c2,

first_usage_date c3,

last_usage_date c4

from

See code depot for full script
dba_feature_usage_statistics

where

first_usage_date is not null

order by

name;

prompt *************************************
prompt Details from dba_registry
prompt *************************************

col c1 heading 'Component' format a40
col c2 heading 'Version' format a12
col c3 heading 'Status' format a10

select
   comp_name c1,
   version   c2,
   status
from
   dba_registry;

prompt *******************************************************

prompt This query goes into more detail on usage

prompt of the tuning pack

prompt *******************************************************

col c1 heading 'Feature Name' format a35

col c2 heading 'Detected|Usages' format 999,999

col c3 heading 'Last|Usage|Date' format a10

col c4 heading 'Last|Sample|Date' format a10

select

name c1,

detected_usages c2,

last_usage_date c3,

last_sample_date c4

from
See code depot for full script

dba_feature_usage_statistics

where

name in (

'ADDM',

'Automatic SQL Tuning Advisor',

'Automatic Workload Repository',

'AWR Baseline',

'AWR Baseline Template',

'AWR Report',

'EM Performance Page',

'Real-Time SQL Monitoring',

'SQL Access Advisor',

'SQL Monitoring and Tuning pages',

'SQL Performance Analyzer',

'SQL Tuning Advisor',

'SQL Tuning Set (system)',

'SQL Tuning Set (user)'

)

order by name;

prompt *******************************************************

prompt This script will detect if Data Guard is being used

prompt *******************************************************

col c1 heading 'Data Guard Used' format a20

select

decode(count(*), 0, 'No', 'Yes') c1

from

v$dataguard_status;

prompt
prompt ************************************************
prompt Detect data guard redo apply (physical standby)
prompt ************************************************

select
   decode(count(*), 0, 'No', 'Yes') 
from
   v$managed_standby;

prompt
prompt **********************************************
prompt Detect data guard SQL Apply (logical standby)
prompt **********************************************

select
   decode(count(*), 0, 'No', 'Yes') 
from
   dba_tables
where
   table_name like '%logstdby%';

prompt
prompt **********************************************
prompt Detect active data guard
prompt **********************************************

select
   database_role
from
See code depot for full script
   v$database
where
   database_role <> 'PRIMARY';

prompt *******************************************************

prompt This script will count all index types

prompt for non-standard schema owners

prompt *******************************************************

set pages 9999

col c1 heading 'Owner' format a20

col c2 heading 'Index|type' format a25

col c3 heading 'Count' format 999,999

break on c1 skip 1

select distinct

owner c1,

index_type c2,

count(*)

from
See code depot for full script

dba_indexes

where

owner not in (

'APEX_040200',

'MDSYS',

'OUTLN',

'CTXSYS',

'OLAPSYS',

'FLOWS_FILES',

'SYSTEM',

'DVSYS',

'AUDSYS',

'DBSNMP',

'GSMADMIN_INTERNAL',

'OJVMSYS',

'ORDSYS',

'XDB',

'ORDDATA',

'SYS',

'WMSYS',

'LBACSYS')

group by

owner,

index_type

order by

owner,

index_type;

clear breaks

prompt *******************************************************

prompt This script will show if the database is using AWR

prompt *******************************************************

select

display_value

from

v$parameter
See code depot for full script

where

name = 'control_management_pack_access';

prompt ****************************************
prompt See if Flashback database is being used
prompt ****************************************

select flashback_on from v$database;

prompt
prompt *******************************************
prompt Is the MTS (shared servers) enabled?
prompt *******************************************

col c1 heading 'Shared|Servers' format a20

select
   decode(to_number(value), 1, 'No', 'Yes') c1
from
   v$parameter
where
   name = 'shared_servers';

prompt
prompt *******************************************
prompt Detect for Bitmap join indexes
prompt *******************************************

select 
   decode(count(*), 0, 'No', 'Yes') 
from 
   dba_indexes
where
   join_index = 'YES';

prompt
prompt *******************************************
prompt Detect Replication schemas/ sites
prompt *******************************************

select 
   decode(count(*), 0, 'No', 'Yes') 
from
  sys.dba_repschema;

prompt
prompt *******************************************
prompt Detect 12c multitenant (12c only)
prompt *******************************************

select 
   decode(count(*), 0, 'No', 'Yes') 
from
  v$pdbs;

prompt
prompt *******************************************
prompt Count IOT's by schema
prompt *******************************************

set feedback on

select 
   owner,
   count(*)
from 
See code depot for full script
   dba_tables
where
   iot_type = 'IOT'
and owner not in (
   'APEX_040200',                                                                   
   'CTXSYS',                                                                 
   'DBSNMP',                                                                  
   'GSMADMIN_INTERNAL',                                                              
   'SYS',                                                                      
   'WMSYS') 
group by
   owner;

prompt
prompt *******************************************
prompt Is Fine Grained Auditing (FGA) Used?
prompt *******************************************

select 
   decode(count(*), 0, 'No', 'Yes') 
from 
   dba_fga_audit_trail;

prompt
prompt *******************************************
prompt Is Instance caging used?
prompt *******************************************

select
   decode(count(*), 0, 'No', 'Yes') 
from
See code depot for full script
   v$rsrcmgrmetric_history
order by
   begin_time;

prompt
prompt *******************************************
prompt Count Materialized Views by Schema
prompt *******************************************

col c1 heading 'Schema|Owner' format a15

select
   owner c1,
   count(*)
from
   dba_mviews
group by
   owner;

prompt
prompt *******************************************
prompt Count function-based indexes
prompt *******************************************

col c1 heading 'Schema|Owner' format a15

select 
   owner    c1, 
   count(*)
from  
See code depot for full script
   dba_indexes
where 
   index_type like 'FUNCTION-BASED%'
and 
   owner not in ('XDB','SYS','SYSTEM','APEX_040200','WMSYS')
group by
   owner;

prompt
prompt *******************************************
prompt Count virtual private databases by schema
prompt *******************************************

col c1 heading 'Schema|Owner' format a15

select 
   object_owner    c1, 
   count(*)
from  
See code depot for full script
   dba_policies
where 
   object_owner not in (
   'MDSYS',
   'XDB',
   'SYS',
   'SYSTEM',
   'APEX_040200',
   'WMSYS')
group by
   object_owner;

prompt
prompt **********************************************
prompt Detect parallel query installed
prompt **********************************************

select
   decode(count(*), 0, 'No', 'Yes') 
from 
   v$pq_slave;

prompt
prompt *******************************************
prompt Count parallel tables and indexes by schema
prompt *******************************************

col c1 heading 'Schema|Owner' format a15

select 
   owner    c1,
   degree,
   count(*)
from  
See code depot for full script
   dba_tables
where 
   ltrim(degree) not in ('1', '0', 'DEFAULT')
group by
   owner,
   degree
UNION
select 
   owner    c1,
   degree,
   count(*)
from  
   dba_indexes
where 
   ltrim(degree) not in ('1', '0', 'DEFAULT')
group by
   owner,
   degree
;

prompt
prompt *******************************************
prompt Detect transportable tablespaces by schema
prompt *******************************************

col c1 heading 'Transportable|Tablespace' format a30

select
   t1.tablespace_name c1,
   count(*)
from
See code depot for full script
   dba_tablespaces t1,
   dba_tables      t2
where
   t1.tablespace_name = t2.tablespace_name 
and
   plugged_in <> 'NO'
group by
   t1.tablespace_name;

prompt
prompt **********************************************
prompt Detect Exadata in-memory features
prompt **********************************************

select 
   decode(count(*), 0, 'No', 'Yes') 
from
   dba_tables
where
   inmemory <> 'DISABLED';

prompt
prompt **********************************************
prompt Detect SQL Plan management
prompt **********************************************

select 
   decode(count(*), 0, 'No', 'Yes') 
from   
    dba_sql_plan_baselines;

prompt
prompt **********************************************
prompt Detect Oracle Streams
prompt **********************************************

select 
   decode(count(*), 0, 'No', 'Yes') 
from   
See code depot for full script
    dba_queue_tables
where
   owner = 'STRMADMIN';

prompt
prompt **********************************************
prompt Detect Exadata Zone Maps
prompt **********************************************

select 
   decode(count(*), 0, 'No', 'Yes') 
from
See code depot for full script
   dba_zonemap_measures;

prompt
prompt **********************************************
prompt Detect 12c Adaptive SQL Execution Plans
prompt **********************************************

select 
   decode(count(*), 0, 'No', 'Yes') 
from
See code depot for full script
   v$parameter
where
   name ='optimizer_adaptive_reporting_only'
and
   value = 'FALSE';

prompt
prompt **********************************************
prompt Detect Result Cache Installed
prompt **********************************************

select
   decode(count(*), 0, 'No', 'Yes') 
from
   v$result_cache_objects
where
   type = 'Result';

prompt
prompt **********************************************
prompt Detect Client side query Cache Installed
prompt **********************************************

select
   decode(count(*), 0, 'No', 'Yes') 
from
See code depot for full script
   v$parameter
where
   lower(name) = 'client_result_cache_size'
and
  ltrim(value) <> '0';

prompt
prompt **********************************************
prompt Detect 12c Advanced Index Compression Used
prompt **********************************************

select
   decode(count(*), 0, 'No', 'Yes') c1
from
See code depot for full script
   dba_indexes
where
   compression like 'ADVANCED%';

prompt
prompt **********************************************
prompt Detect 12c cell flash cache used
prompt **********************************************

select 
   decode(count(*), 0, 'No', 'Yes')  
from 
See code depot for full script
   v$mystat   s, 
   v$statname n 
where 
   n.statistic# = s.statistic# 
and 
   name like '%cell flash%' 
and
   value > 0
order by 1;

prompt
prompt **********************************************
prompt Detect 12c sharded queues
prompt **********************************************

select 
   decode(count(*), 0, 'No', 'Yes')  
from 
   dba_queues
where 
   sharded = 'TRUE';

prompt
prompt **********************************************
prompt Detect replication
prompt **********************************************

col sname      format a20 head "SchemaName"
col masterdef  format a10 head "MasterDef?"
col oname      format a20 head "ObjectName"
col gname      format a20 head "GroupName"
col object     format a35 trunc
col dblink     format a35 head "DBLink"
col message    format a25
col broken     format a6 head "Broken?"

select 
  sname, 
  masterdef, 
  dblink
from
See code depot for full script
  sys.dba_repschema;

prompt

prompt =====================================================

prompt This detects extra-cost options installed and used

prompt =====================================================

prompt

prompt *******************************************************

prompt This script will detect if partitioning is

prompt installed and used

prompt *******************************************************

col c1 heading 'Partitioning|Installed' format a20

select

decode(count(*), 0, 'No', 'Yes') Partitioning

See code depot for full script
from ( select 1

from

dba_part_tables

where owner not in ('SYSMAN', 'SH', 'SYS', 'SYSTEM')

and rownum = 1 );

col c1 heading 'Partitioning|Used' format a20

select

decode(count(*), 0, 'No', 'Yes') c1

from

dba_tables

where

partitioned = 'YES'

and

owner not in ('SYS','SYSTEM','AUDSYS');

col name format A30

col detected_usages format 999,999

select

u1.name,

u1.detected_usages,

u1.currently_used,

u1.version,

decode(count(*), 0, 'No', 'Yes') Partitioning

from
See code depot for full script

dba_feature_usage_statistics u1

where

u1.version = (

select

MAX(u2.version)

from

dba_feature_usage_statistics u2

where

u2.name = u1.name)

and

u1.detected_usages > 0

and

u1.dbid = (SELECT dbid FROM v$database)

and

lower(u1.name) like '%partitioning%'

group by

u1.name,

u1.detected_usages,

u1.currently_used,

u1.version

order by

name;

prompt *******************************************************

prompt This script will tell if the database is running RAC

prompt *******************************************************

select

name,

value

from
See code depot for full script

v$parameter

where

name='cluster_database';

prompt
prompt *******************************************
prompt Detect RAC One Node
prompt *******************************************

select 
See code depot for full script
   decode(count(*), 1, 'No', 'Yes') c1
from 
   v$thread;

prompt ****************************************
prompt Count number of RAC nodes
prompt ****************************************

select count(*) from gv$instance;

prompt *******************************************

prompt checking for OLAP features installed

prompt *******************************************

col c1 heading 'OLAP|Installed' format a20

select

decode(count(*), 0, 'No', 'Yes') c1

from

v$option

where

parameter = 'OLAP';

prompt *******************************************

prompt checking for OLAP features used

prompt *******************************************

col c1 heading 'OLAP|Used' format a20

select

decode(count(*), 0, 'No', 'Yes') c1

from
See code depot for full script

dba_feature_usage_statistics

where

name like '%OLAP%'

and

first_usage_date is not null;

prompt *******************************************

prompt Checking for Data Mining features installed

prompt *******************************************

col c1 heading 'Data Mining|Installed' format a20

select

decode(count(*), 0, 'No', 'Yes') c1

from

dba_feature_usage_statistics

where

lower(name) like '%mining%';

prompt *******************************************
prompt checking for Oracle Spatial installed
prompt *******************************************

select
   decode(count(*), 0, 'No', 'Yes') Spatial
from ( select 1
       from
        all_sdo_geom_metadata
where
   rownum = 1 );

spool off;

prompt
prompt *******************************************
prompt Detect for Advanced Compression Option
prompt *******************************************

select 
   decode(count(*), 0, 'No', 'Yes') 
from 
See code depot for full script
   dba_tables
where
   compression <> 'DISABLED';

prompt
prompt *******************************************
prompt Is Database Vault Installed?
prompt *******************************************

select 
   decode(count(*), 0, 'No', 'Yes') 
from 
   dba_tables
where
owner = 'DVSYS';

prompt
prompt *******************************************
prompt Is Label Security used?
prompt ******************************************* 
select 
   decode(count(*), 0, 'No', 'Yes') 
from 
   dba_sa_user_levels;

prompt
prompt *******************************************
prompt Is Exadata Used?
prompt *******************************************

select
   decode(count(*), 0, 'No', 'Yes') 
from
   v$cell_thread_history;

prompt
prompt *******************************************
prompt Is Oracle Spatial Used?
prompt *******************************************

col c1 heading 'Schema|Owner' format a15

select
   decode(count(*), 0, 'No', 'Yes') 
from
   user_sdo_geom_metadata;

spool off

For a complete license review to see if your database can run using standard edition, contact us and we can analyze your existing database and see if you can migrate to SE.

References:

- Differences Between Enterprise, Standard and Personal Editions [ID 465455.1]

Oracle Training from Don Burleson

The best on site "Oracle training classes" are just a phone call away! You can get personalized Oracle training by Donald Burleson, right at your shop!

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals. Feel free to ask questions on our Oracle forum.
Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.
Errata? Oracle technology is changing and we strive to update our BC Oracle support information. If you find an error or have a suggestion for improving our content, we would appreciate your feedback. Just e-mail: and include the URL for the page.

Burleson Consulting

The Oracle of Database Support
Oracle Performance Tuning
Remote DBA Services

Copyright © 1996 - 2016

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.

��