Database Instance Options and Features (voption.sql)

Use at Your Own Risk

This script is for educational purposes only. Always test in development environments before production use. See our disclaimer for full terms.

What This Script Does

This script queries Oracle views to provide database information via the voption.sql script.

The Script

rem  voption.sql
rem
ttitle 'Instance Options'
rem
col banner format a64 heading 'BANNER'
col value format a5 heading 'VALUE'
col parameter format a40 heading 'PARAMETER'
rem
select banner
  from v$version;
rem
set embedded on
rem
select value, parameter
  from v$option
 order by parameter;
rem
set embedded off

What This Script Does

This script provides a comprehensive overview of the Oracle database version and all available instance options and features. It’s essential for understanding what features are enabled in your database installation, verifying licensing compliance, and determining which advanced functionality is available for use in applications and administration.

Key Features

• Version Information: Displays complete Oracle version and build details
• Feature Inventory: Lists all available Oracle options and features
• Licensing Verification: Shows which licensed features are installed
• Availability Check: Indicates which advanced features can be used
• Alphabetical Listing: Organized display for easy reference

Usage

@voption.sql

No parameters required - shows all instance options and version information.

Required Privileges

SELECT on V$VERSION
SELECT on V$OPTION

Sample Output

                                          Instance Options

BANNER
----------------------------------------------------------------
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.8.0.0.0

VALUE PARAMETER
----- ----------------------------------------
TRUE  Advanced Analytics
FALSE Advanced Compression
TRUE  Advanced Index Compression
TRUE  Advanced Security
FALSE Application Continuity
TRUE  Automatic Storage Management
FALSE Big Data SQL
TRUE  Bit Vector Indexes
TRUE  Block Change Tracking
TRUE  Database In-Memory
FALSE Database Vault
TRUE  Data Mining
TRUE  Data Pump Export
TRUE  Data Pump Import
FALSE Exadata Hybrid Columnar Compression
TRUE  Export/Import Utilities
TRUE  FlashBack Query
TRUE  Function-based Indexes
FALSE GoldenGate
FALSE HDB
TRUE  Join index
FALSE JSON
FALSE Label Security
TRUE  Load Balancing Advisory
FALSE Management Repository
TRUE  OLAP
TRUE  Oracle Database Extensions for .NET
FALSE Oracle Machine Learning for SQL
TRUE  Oracle Spatial
FALSE Oracle Text
TRUE  Parallel backup and recovery
TRUE  Parallel execution
FALSE Parallel Statistical Functions
TRUE  Partitioning
FALSE Password File
TRUE  Point-in-time tablespace recovery
FALSE Rapid Application Development
TRUE  Real Application Clusters
FALSE Real Application Testing
TRUE  Result Cache
FALSE SecureFiles Compression and Deduplication
FALSE Server Flash Cache
TRUE  SQL Access Advisor
FALSE SQL Tuning Advisor
FALSE Transparent Data Encryption
FALSE Trial Recovery
TRUE  Unified Auditing
FALSE XStream

Key Output Sections

Version Banner

• Oracle Edition: Enterprise, Standard, Express editions
• Version Number: Major.minor.patch.build format
• Platform Information: Operating system and architecture details

Option Values

• TRUE: Feature is installed and available for use
• FALSE: Feature is not installed or not licensed
• Parameter: Official Oracle feature/option name

Understanding Oracle Options and Features

Core Database Features (Usually TRUE)

Basic Functionality

• Export/Import Utilities: Data export/import tools
• Function-based Indexes: Indexes on expressions and functions
• Parallel execution: Parallel query and DML operations
• Point-in-time tablespace recovery: Granular recovery capabilities

Standard Analytics

• FlashBack Query: Query data as of past point in time
• Result Cache: SQL and PL/SQL result caching
• SQL Access Advisor: Index and materialized view recommendations

Licensed Options (May Require Additional Licensing)

Advanced Security Features

• Advanced Security: Encryption and redaction capabilities
• Database Vault: Database access controls and separation of duties
• Label Security: Row-level security based on data classification
• Transparent Data Encryption: Column and tablespace encryption

Performance and Scalability

• Advanced Compression: Data compression across multiple algorithms
• Database In-Memory: In-memory column store
• Partitioning: Table and index partitioning
• Real Application Clusters: Multi-node clustering

Analytics and Data Management

• Advanced Analytics: Data mining and statistical functions
• OLAP: Online analytical processing capabilities
• Oracle Spatial: Geographic information system support
• Oracle Text: Full-text search and document management

Enterprise-Specific Features

High Availability

• Application Continuity: Transparent application failover
• Real Application Testing: Database replay and SQL performance analyzer
• Automatic Storage Management: Cluster file system and volume manager

Integration and Mobility

• GoldenGate: Real-time data integration and replication
• XStream: High-performance data streaming
• Big Data SQL: Integration with Hadoop and NoSQL systems

Common Use Cases

1. Licensing Compliance

   • Verify which features are installed and in use
   • Ensure compliance with Oracle licensing agreements
   • Document licensed vs. unlicensed features
   • Plan for additional licensing needs

2. Feature Planning

   • Determine availability of advanced features for projects
   • Plan application architecture based on available options
   • Assess upgrade benefits and requirements
   • Validate feature requirements before implementation

3. System Documentation

   • Document database capabilities for teams
   • Create system specification documentation
   • Support capacity planning and architecture decisions
   • Maintain configuration management records

4. Troubleshooting and Support

   • Verify feature availability when issues arise
   • Provide information for Oracle support cases
   • Validate configuration during problem diagnosis
   • Confirm feature status after upgrades or patches

License Management Considerations

Identifying Licensed Features

Automatically Licensed (Enterprise Edition)

-- Features included with Enterprise Edition:
-- Partitioning, Advanced Security, OLAP
-- Spatial, Data Mining, Advanced Compression
-- Real Application Clusters (with RAC license)

Separately Licensed Options

-- Features requiring additional licensing:
-- Database In-Memory, Real Application Testing
-- Advanced Compression, Label Security
-- Oracle Text, GoldenGate

Feature Usage Tracking

-- Monitor actual feature usage:
-- DBA_FEATURE_USAGE_STATISTICS view
-- AWR reports for feature utilization
-- Regular audits of enabled vs. used features

Compliance Best Practices

Regular Auditing

-- Monthly reviews:
-- Compare installed vs. licensed features
-- Check for new feature usage
-- Document any changes in licensing needs
-- Maintain compliance documentation

Change Management

-- Before enabling new features:
-- Verify licensing status
-- Obtain necessary approvals
-- Document business justification
-- Update licensing inventory

Feature Assessment and Planning

Performance Features

Determine High-Impact Options

-- High-value performance features:
-- Database In-Memory (significant query acceleration)
-- Advanced Compression (storage and I/O reduction)
-- Partitioning (performance and manageability)
-- Result Cache (query response improvement)

Assess Current Utilization

-- Check if performance features are being used:
SELECT * FROM dba_feature_usage_statistics
WHERE detected_usages > 0
ORDER BY last_usage_date DESC;

Security and Compliance Features

Data Protection Capabilities

-- Security-related options:
-- Transparent Data Encryption (data at rest protection)
-- Advanced Security (network encryption, redaction)
-- Database Vault (privileged user access controls)
-- Label Security (row-level security)

Compliance Requirements

-- Map compliance needs to features:
-- GDPR: TDE, Data Redaction, Audit Vault
-- SOX: Database Vault, Audit capabilities
-- HIPAA: TDE, access controls, audit trails

Version-Specific Considerations

Oracle 19c Features

• Enhanced Automatic Indexing
• Real-time statistics maintenance
• Improved In-Memory performance
• Enhanced JSON support

Oracle 18c Features

• Autonomous Database capabilities
• Active Data Guard DML redirection
• Polymorphic table functions

Oracle 12c Features

• Multitenant architecture
• In-Memory Column Store
• Native JSON support
• Identity columns

Advanced Analysis

Feature Interdependencies

-- Some features require others:
-- Real Application Testing requires Enterprise Edition
-- Database In-Memory works best with compression
-- Partitioning enhances parallel processing
-- Advanced Security complements Database Vault

Cost-Benefit Analysis

-- Evaluate feature value:
-- Performance improvement potential
-- Administrative efficiency gains
-- Security and compliance benefits
-- Total cost of ownership impact

Migration Planning

-- Consider when upgrading:
-- New features available in target version
-- Deprecated features to avoid
-- Licensing changes between versions
-- Migration path requirements

Troubleshooting Common Issues

Feature Not Available

Verify Installation

-- Check if feature shows as FALSE:
-- May not be installed with current edition
-- Could require separate installation
-- Might need additional licensing

Edition Limitations

-- Standard Edition restrictions:
-- Limited parallel processing
-- No partitioning
-- No advanced compression
-- Reduced management features

Licensing Confusion

Documentation Review

-- Consult Oracle documentation:
-- Licensing Information User Manual
-- Feature comparison matrices
-- Edition-specific capabilities
-- Current licensing policies

Oracle Support

-- Contact Oracle for clarification:
-- Licensing compliance questions
-- Feature availability verification
-- Upgrade path recommendations
-- Contract-specific terms

Integration with Other Scripts

Feature Usage Monitoring

-- Combine with usage statistics:
SELECT o.parameter, o.value, f.detected_usages, f.last_usage_date
FROM v$option o
LEFT JOIN dba_feature_usage_statistics f
  ON UPPER(o.parameter) = UPPER(f.name)
WHERE o.value = 'TRUE'
ORDER BY f.detected_usages DESC NULLS LAST;

Licensing Audit

-- Create comprehensive licensing report:
-- Current installed options
-- Actual feature usage statistics
-- License compliance status
-- Recommendations for optimization

Related Scripts

• vdb.sql - Database status and configuration
• vlicense.sql - Session license limits
• vsga.sql - SGA configuration information
• vfixdef.sql - Fixed view definitions

Best Practices

1. Regular Documentation

   • Run monthly to track changes
   • Document licensing status
   • Maintain feature inventory
   • Update after patches/upgrades

2. Compliance Management

   • Verify licensing before using features
   • Monitor actual feature usage
   • Maintain compliance documentation
   • Regular audits and reviews

3. Planning and Assessment

   • Evaluate feature benefits vs. costs
   • Plan for licensing needs
   • Consider feature interdependencies
   • Align with business requirements