Skip to content

Oracle

This guide provides instructions for setting up and using Oracle with DBeaver.

To connect to Oracle, enter Oracle in the search field on the initial Connect to a database page, then select the Oracle data source. On the next page, select Oracle (Legacy) for versions 8.x, 9.x, and 10.x or Oracle for version 11 and later. For information on creating a connection, see Database Connection.

DBeaver also supports Oracle cloud database extensions such as Oracle Cloud JSON and Oracle NetSuite, both of which can be connected through Cloud Explorer.

Setting Up

This section provides an overview of DBeaver's settings for establishing a direct connection and the configuration of secure connections using SSH, proxies, and the driver setup for Oracle.

Oracle connection settings

  1. On the Main tab, configure the connection settings.

    Field Description
    Connection Types Basic: Connect directly by using host, port, and database details
    TNS: Connect through aliases in tnsnames.ora
    Custom: Connect with a complete JDBC URL
    LDAP: Connect with LDAP
    Authentication Available authentication methods depend on the selected connection type
    Connection Details Additional connection details, if needed
    Driver Name Driver name, filled automatically based on the selected driver type
    Driver Settings Oracle driver properties

  2. (Optional) Use the Oracle Properties page to configure Oracle-specific settings.

    Setting Description
    Session settings
    Language Sets the session language
    Territory Sets the session territory
    NLS Date Format Sets the session date format
    NLS Timestamp Format Sets the session timestamp format
    Length semantics Sets the session length semantics
    Currency symbol Sets the session currency symbol
    Use metadata queries optimizer Enables the metadata query optimizer
    Optimizer version Selects the Oracle optimizer version used for metadata queries
    Use RULE hint for system catalog queries Adds the RULE hint to supported system catalog queries
    Simple constraint reading query Uses simpler metadata queries that can be slower but more stable across Oracle versions
    Use UNION for table metadata reading Uses the legacy metadata query with UNION instead of JOIN; it can improve table metadata performance in some environments
    Read column comments in query metadata Loads Oracle column comments with table metadata for result-set column tooltips; this can slow metadata reading
    Search metadata in synonyms Includes synonyms in metadata search and autocompletion; this can significantly slow these operations
    Search metadata in sequences Includes sequences in metadata search and autocompletion; this can significantly slow these operations
    Show only connected user schema Shows only the connected user's schema
    Hide empty schemas Checks schema contents and hides schemas without objects
    Always show DBA objects Shows DBA-related metadata objects even when the connected user does not have the DBA role
    Always use DBA_* views Uses DBA_* views instead of ALL_* views where possible
    Use SYS schema prefix Adds the SYS schema prefix to metadata queries
    Show DATE values specifically as DATE Displays Oracle DATE values as DATE instead of TIMESTAMP; unavailable when date and time formatting is disabled

Connection details

The Connection Details section in DBeaver allows to customize your experience while working with Oracle database. This includes options for adjusting the Connections View, setting up Security measures, applying Filters, configuring Connection Initialization settings, and setting up Shell Commands. Each of these settings can significantly impact your database operations and workflow. For detailed guides on these settings, please refer to the following articles:

Oracle driver properties

The settings for Oracle Driver properties enable you to adjust the performance of the Oracle driver. These adjustments can influence the efficiency, compatibility, and features of your Oracle database.

For a complete walkthrough on setting up Oracle JDBC driver properties, you can refer to the official Oracle JDBC documentation. These guide detail each driver's properties and how they can be used to optimize Oracle database connections.

To open the Edit Driver page, select Driver Settings on the first page of the driver settings. For information on these settings, see Driver manager.

Secure Connection Configurations

DBeaver supports secure connections to your Oracle database. Guidance on configuring such connections, specifically SSH, Proxy, Kubernetes, AWS SSM and SSL connections, can be found in various referenced articles. For a comprehensive understanding, please refer to these articles:

Secure Storage with Secret Providers

DBeaver supports various cloud-based secret providers to retrieve database credentials. For detailed setup instructions, see Secret Providers.

Powering Oracle with DBeaver

DBeaver provides a host of features designed for Oracle databases. This includes the ability to view and manage databases, along with numerous unique capabilities aimed at optimizing database operations.

Oracle database objects

DBeaver lets you view and manipulate a wide range of Oracle database objects. DBeaver has extensive support for various Oracle metadata types, allowing you to interact with a wide variety of database objects, such as:

  • Schemas
    • Tables
      • Columns
      • Constraints
      • Foreign Keys
      • References
      • Triggers
      • Indexes
      • Partitions
      • Dependencies
    • Views
    • Materialized Views
    • Indexes
    • Sequences
    • Queues
    • Types
    • Packages
    • Procedures
    • Functions
    • Synonyms
    • Schema Triggers
    • Table Triggers
    • Database Links
    • Java
    • Jobs
    • Scheduler
      • Jobs
      • Programs
    • Recycle bin
  • Global metadata
    • Types
    • Public Synonyms
    • Public Database Links
    • User Recycle bin
  • Storage
    • Tablespaces
      • Files
      • Objects
  • Security
    • Users
    • Roles
    • Profiles
  • Administer
    • Session Manager
    • Locks Manager

Oracle features in DBeaver

DBeaver isn't limited to typical SQL tasks. It also includes numerous unique features specifically for Oracle. Beyond regular SQL operations, DBeaver provides a range of Oracle-specific capabilities, such as:

Category Feature
Data Types Oracle Nested Tables
PL/SQL Support PL/SQL Procedures
PL/SQL Functions
Security Oracle Permissions
Oracle Profiles
Oracle Roles
Changing Password
User Management Create/Clone Users
Data Organization Oracle Partitions
Database Management Oracle Dependencies
Performance Tuning Oracle Performance Reports
Oracle Execution Plans
Script execution Execute scripts and commands with SQL Plus syntax

Additional features compatible with Oracle, but not exclusive to it:

Category Feature
Data Transfer Data Import
Data Export
Session Management Session Manager
Backup and Restore How to Backup/Restore data
Schema Management Schema Compare
Data Visualization GIS Guide
ERD Guide