Options, Features and Properties of your Database

There are a lot of options which can be used in database. Not all are available in your database. It depends which version of database you are using (standard/enterprise or else)  or if a given option is active/tuned off/on. You can find details in V$OPTION.

select * from v$option;

PARAMETER                                 VALUE
----------------------------------------- -----
Active Data Guard                         FALSE  
Advanced Compression                      TRUE  
Backup Encryption                         TRUE  
Basic Compression                         TRUE  
Spatial                                   TRUE  
Streams Capture                           TRUE  
Unused Block Compression                  TRUE  
XStream                                   TRUE  

 64 rows selected

If you want to check which from above options were used in your database check DBA_FEATURE_USAGE_STATISTICS. Remember some of options are extra paid if you don’t use them turn them off and save money.

select * from dba_feature_usage_statistics;

View DBA_HIGH_WATER_MARK_STATISTICS displays information about database high-watermark statistics like:

  • Number of User Tables

  • Size of Largest Segment (Bytes)

  • Maximum Number of Partitions belonging to an User Table

  • Maximum Number of Partitions belonging to an User Index

  • Number of User Indexes

  • Maximum Number of Concurrent Sessions seen in the database

  • Maximum Size of the Database (Bytes)

  • Maximum Number of Datafiles

  • Maximum Number of Tablespaces

  • Maximum Number of CPUs

  • Maximum Query Length

  • Maximum Number of Services

select * from dba_high_water_mark_statistics;

DBA_CPU_USAGE_STATISTICS displays database CPU usage statistics

select * from dba_cpu_usage_statistics;

You can produce very nice and detailed reports about options, statistics and cpu usage using package DBMS_FEATURE_USAGE_REPORT in html

select output from table(dbms_feature_usage_report.display_html);

or text form

select output from table(dbms_feature_usage_report.display_text);

Information about database properties you can find in view DATABASE_PROPERTIES. It’s very useful source of standard settings for your database.

select * from database_properties;

PROPERTY_NAME                  PROPERTY_VALUE                       
------------------------------ -------------------------------------
DICT.BASE                      2                                    
DEFAULT_TEMP_TABLESPACE        TEMP                                 
DEFAULT_PERMANENT_TABLESPACE   USERS                                
DEFAULT_EDITION                ORA$BASE                             
Flashback Timestamp TimeZone   GMT                                  
DST_UPGRADE_STATE              NONE                                 
DST_PRIMARY_TT_VERSION         14                                   
DST_SECONDARY_TT_VERSION       0                                    
DEFAULT_TBS_TYPE               SMALLFILE                            
NLS_LANGUAGE                   AMERICAN                             
NLS_TERRITORY                  AMERICA                              
NLS_CURRENCY                   $                                    
NLS_ISO_CURRENCY               AMERICA                              
NLS_NUMERIC_CHARACTERS         .,                                   
NLS_CHARACTERSET               AL32UTF8                             
NLS_CALENDAR                   GREGORIAN                            
NLS_DATE_FORMAT                DD-MON-RR                            
NLS_DATE_LANGUAGE              AMERICAN                             
NLS_SORT                       BINARY                               
NLS_TIME_FORMAT                HH.MI.SSXFF AM                       
NLS_TIME_TZ_FORMAT             HH.MI.SSXFF AM TZR                   
NLS_DUAL_CURRENCY              $                                    
NLS_COMP                       BINARY                               
NLS_LENGTH_SEMANTICS           BYTE                                 
NLS_NCHAR_CONV_EXCP            FALSE                                
NLS_NCHAR_CHARACTERSET         AL16UTF16                            
GLOBAL_DB_NAME                 ORA11G.DBAORA.COM                    
EXPORT_VIEWS_VERSION           8                                    
DBTIMEZONE                     00:00

Have a fun 🙂





Leave a Reply

Your email address will not be published. Required fields are marked *

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