Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 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 s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 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

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,
   1;
 
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!

Oracle training
 
 


  Oracle consulting and training

 

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.


 

��  
 
 
Oracle Training at Sea
 
 
 
 
oracle dba poster
 

 
Follow us on Twitter 
 
Oracle performance tuning software 
 
Oracle Linux poster