Oracle
Table of contents
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:
- Connection Details Configuration
- Database Navigator
- Security Settings Guide
- Filters Settings Guide
- Connection Initialization Settings Guide
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
- Tables
- Global metadata
- Types
- Public Synonyms
- Public Database Links
- User Recycle bin
- Storage
- Tablespaces
- Files
- Objects
- Tablespaces
- 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 | |
| Script execution | Execute scripts with SQL Plus |
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 |