DBeaver Documentation

DOWNLOAD pdf

Oracle

Overview

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

Before you start, you must create a connection in DBeaver and select Oracle. If you haven't done this, please refer to our Database Connection article.

DBeaver interacts with the Oracle server using a specific driver. It supports all versions of Oracle, but the correct driver must be selected: use 'Oracle (Legacy)' for versions 8.x, 9.x, 10.x and 'Oracle' for version 11 and later. It 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

In this subsection, we'll outline the settings for establishing a direct connection to a Oracle database using DBeaver. Correctly configuring your connection ensures seamless interaction between DBeaver and your Oracle database.

1) The first page of the connection settings requires you to fill in specific fields to establish the initial connection.

Field Description
Host If you're connecting via host, enter the host address of your Oracle database here.
Database Enter the name of the Oracle database you want to connect to.
Port Enter the port number for your Oracle database. The default Oracle port is 1521.
Connection identifiers Choose whether you want to connect using a Service name or a SID identifier.
Authentication Choose the type of authentication you want to use for the connection. For detailed guides on authentication types, please refer to the following articles:

- Oracle Database Native
- OS Authentication
- DBeaver Profile Authentication
- Oracle Kerberos Authentication
- Oracle Wallet

You can also read about security in DBeaver PRO.
Role Choose whether you want to connect using a Normal, SYSDABA or a SYSOPER role. For more details, you can refer to the official Administering User Accounts and Security article.
Connection Details Provide additional connection details if necessary.
Driver Name This field will be auto-filled based on your selected driver type.
Driver Settings If there are any specific driver settings, configure them here.

2) The second page of the connection settings offers additional options that allow you to customize your further connection to the Oracle database.

Field Description
Language Specify the Session Language.
Territory Specify the Session Territory.
NLS Date Format Specify NLS (National Language Support) Date Format. The default value of the NLS_DATE_FORMAT parameter is determined by the TERRITORY parameter.
NLS Timestamp Format Specify NLS Timestamp format. The default value of the NLS_TIMESTAMP_FORMAT parameter is determined by the TERRITORY parameter.
Length semantics specify the length semantics for VARCHAR2 and CHAR table columns, user-defined object attributes, and PL/SQL variables in database objects created in the session. The length semantics can be specified as either BYTE or CHAR.
Currency symbol Specify the Currency symbol.You can find out which currency symbol your current session uses by querying the V$NLS_PARAMETERS view.
Show only connected user schema Show only a scheme of a connected user in the Database Navigator.
Hide empty schemas Check existence of objects within schema and do not show empty schemas in tree. Enabled by default but it may cause performance problems on databases with very big number of objects.
Always show DBA objects Always shows DBA-related metadata objects in tree even if user do not has DBA role.
*Always use `DBA_ views** | Use DBA* views instead of ALL*` views wherever it is possible
Use SYS schema prefix Use SYS schema prefix in all metadata queries. Otherwise use view names without explicit schema.
Simple constraint reading query Use simple metadata queries. May work slower but it is more stable for all Oracle versions.
Use UNION for table metadata reading Use legacy table metadata query. With UNION instead JOIN. It helps in some cases speed up reading of table data.
Search metadata in synonyms Search for metadata in synonyms among other places. May significantly slow down metadata search as well as autocompletion.
Use RULE hint for system catalog queries Adds RULE hint for some system catalog queries (like columns and constraints reading).It significantly increases performance on some Oracle databases (and decreases on others).
Show DATE values specifically as DATE Show DATE data type values specifically as DATE, not as a TIMESTAMP.This setting will not work with disabled date/time formatting.
Use metadata queries optimizer Use metadata queries optimizer. May significantly improve metadata reading performance on some systems.

Connection details

The Connection Details section in DBeaver allows to customize your experience while working with Oracle database. This includes options for adjusting the Navigator 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.

You can customize the Oracle driver in DBeaver via the Edit Driver page, accessible by clicking on the Driver Settings button on the first page of the driver settings. This page offers a range of settings that can influence your Oracle database connections. For a comprehensive guide on these settings, please refer to our Database drivers article.

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:

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
Data Organization Oracle Partitions
Database Management Oracle Dependencies
Performance Tuning Oracle Performance Reports
Oracle Execution Plans

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