How to connect to various DBs with SQL Developer
Introduction
SQL Developer is a useful GUI tool provided by Oracle. It supports not only Oracle DB but also other various DBs. Now I'd like to show you how to connect to various DBs with it.
Basic information on SQL Developer
Latest version:20.4.1 (Till March 2021)
Supported OS:Windows/Linux/MacOS/Other
Price: Free
Connect to various DBs
DB Type | JDBC Driver | Default Port | Comment |
---|---|---|---|
Oracle DB | No need to add | 1521 | For 11gR2, 12c, 18c, 19c, 20c (On-P/Cloud) |
Autonomous DB | No need to add | 1522 | Need Wallet file |
MySQL | mysql-connector-java-8.0.23.jar | 3306 | The driver is also applicable to MariaDB. |
MariaDB | As above | 3306 | |
PostgreSQL | postgresql-42.2.19.jar | 5432 | |
DB2 | db2jcc.jar, db2jcc4.jar | 5000 | |
SQL Server | jtds-1.3.1.jar | 1433 | The driver is also applicable to Sybase. |
※JDBC driver version is as of March 2021.
You can change the UI Language Setting and Font Size in SQL Developer. There is a guide at the last part of this blog.
How to Change Language Setting in SQL Developer
How to Change Font Size in SQL Developer
Download SQL Developer
URL:https://www.oracle.com/tools/downloads/sqldev-downloads.html
This time, I use the Windows version (with JDK8). (An Oracle account is required to download.)
You can start it by unzipping the Zip file and then running sqldeveloper.exe. (No installation required)
If you want to import the settings of the previous version, click Yes and proceed.
Connect to Oracle DB
I connect to DB19c(VM) on Oracle Cloud.
-
Create a new connection
Database Type: Select "Oracle" (By default)
Username, Password: Enter your username and password.
Hostname: Enter the Hostname or the Host IP address.
SID or Service Name: Enter either DB SID or the service name. (Enter PDB service name if you want to connect to PDB.)
If you are not sure about the DB Service Name, please check this: How to get the Service Name of OCI DBCS.
Save Password: If checked, you can skip entering the password the next time you connect.
Press "Test" button to test. If the test connection is OK, then press "Connect" button set up the connection.
Connection Issues
When failed to connect to target database, here are some common reasons, just for your reference.
- Invalid User and Password.
- Wrong User Role.
- DB instance is not started yet.
- The listener port (TCP 1521) is not open on the firewall of target DB server.
- For database on Cloud, the listener port (TCP 1521) is not added to the Ingress Rule. (Check the security list or NSG.)
Connect to ADB
-
Create a new connection
The wallet file is required, please download it from OCI console in advance. After downloading, you do NOT need to unzip it.
Database Type: Select "Oracle" (By default)
Connection Type: Select "Cloud Wallet"
Service: Specify from the list of XXXX_high, XXXX_medium, XXXX_low
You can get the Service information from tnsnames.ora (compressed in wallet ZIP file).
-
Connect to MySQL
- Download JDBC Driver
URL: https://dev.mysql.com/downloads/connector/j/
An Oracle account is required to download.
You can choose the platform according to your needs.
After downloading, unzip the ZIP file.
- Add JDBC Driver
MENU -> Tools -> Preferences -> Database -> Third Party JDBC Drivers -> Add Entry
- Add client's IP Address
Please note that you will not be able to connect unless you allow the IP address of the SQL Developer client.
Error Message:Failure - message from server:"Host '<IP Address>' is not allowed to connect to this MySQL server'
Solution:Execute the following command on the MySQL server side. (Example of root user)
mysql> create user 'root'@'\' identified by '\';
mysql> grant all privileges on \*.* to 'root'@'\';
Create a new connection
The wallet file is required, please download it from OCI console in advance. After downloading, you do NOT need to unzip it.
Database Type: Select "Oracle" (By default)
Connection Type: Select "Cloud Wallet"
Service: Specify from the list of XXXX_high, XXXX_medium, XXXX_low
You can get the Service information from tnsnames.ora (compressed in wallet ZIP file).
- Download JDBC Driver
URL: https://dev.mysql.com/downloads/connector/j/
An Oracle account is required to download.
You can choose the platform according to your needs.
- Add JDBC Driver
MENU -> Tools -> Preferences -> Database -> Third Party JDBC Drivers -> Add Entry
- Add client's IP Address
Please note that you will not be able to connect unless you allow the IP address of the SQL Developer client.
Error Message:Failure - message from server:"Host '<IP Address>' is not allowed to connect to this MySQL server'
Solution:Execute the following command on the MySQL server side. (Example of root user)
mysql> create user 'root'@'\' identified by '\';
mysql> grant all privileges on \*.* to 'root'@'\';
After adding IP address, check with the following command.
- Create a new connection
Database Type: Select "MySQL"
- After connection
Connect to MariaDB
The MySQL JDBC driver can be used for MariaDB.
- Add client's IP Address
Please note that you will not be able to connect unless you allow the IP address of the SQL Developer client.
Error Message:Failure: Access denied for user '<User>'@'<IP Address>'
Solution:Execute the following command on the MariaDB server side. (Example of root user)
MariaDB [(none)]> create user 'root'@'\' identified by '\';
MariaDB [(none)]> grant all privileges on \*.* to 'root'@'\';
MariaDB [(none)]> select host,user from mysql.user;
- Create a new connection
Database Type: Select "MySQL"
- After connection
Connect to PostgreSQL
-
Download JDBC Driver
URL:https://jdbc.postgresql.org/download.html
-
Add JDBC Driver
MENU -> Tools -> Preferences -> Database -> Third Party JDBC Drivers -> Add Entry
- Add client's IP Address
Please note that you will not be able to connect unless you allow the IP address of the SQL Developer client.
Error Message:Failure -Test failed: FATAL: no pg_hba.conf entry for host "<IP Address>", user "<Username>", database "<DB Name>"
Solution:Edit the file below and add an entry.
C:\Program Files\PostgreSQL\13\data\pg_hba.conf
- Create a new connection
Database Type: Select "PostgreSQL"
- After connection
Connect to DB2
-
Download JDBC Driver
URL:https://www.ibm.com/support/pages/db2-jdbc-driver-versions-and-downloads
IBMid is required to download, please have it ready in advance.
Select the appropriate driver for your DB2 version.
(※This example uses db2jcc4.jar.)
After downloading, unzip the ZIP file.
-
Add JDBC Driver
MENU -> Tools -> Preferences -> Database -> Third Party JDBC Drivers -> Add Entry
- Create a new connection
Database Type: Select "DB2"
- After connection
Connect to SQL Server
- Download JDBC Driver
URL: https://www.oracle.com/database/technologies/appdev/sqldev/thirdparty.html
The driver version listed on the above official site is a little old. If you want the latest version, please download it from the following site.
URL: https://sourceforge.net/projects/jtds/files/
Unzip the ZIP file.
- Add JDBC Driver
MENU -> Tools -> Preferences -> Database -> Third Party JDBC Drivers -> Add Entry
- Create a new connection
Database Type: Select "SQLServer"
If there is a connection error, please refer to the following documents.
https://docs.microsoft.com/en-us/troubleshoot/sql/connect/resolving-connectivity-errors
* After connection
How to Change Language Setting in SQL Developer
Download JDBC Driver
URL:https://jdbc.postgresql.org/download.html
Add JDBC Driver
MENU -> Tools -> Preferences -> Database -> Third Party JDBC Drivers -> Add Entry
Please note that you will not be able to connect unless you allow the IP address of the SQL Developer client.
Error Message:Failure -Test failed: FATAL: no pg_hba.conf entry for host "<IP Address>", user "<Username>", database "<DB Name>"
Solution:Edit the file below and add an entry.
C:\Program Files\PostgreSQL\13\data\pg_hba.conf
Database Type: Select "PostgreSQL"
-
Download JDBC Driver
URL:https://www.ibm.com/support/pages/db2-jdbc-driver-versions-and-downloads
IBMid is required to download, please have it ready in advance.
Select the appropriate driver for your DB2 version.
(※This example uses db2jcc4.jar.)
After downloading, unzip the ZIP file.
-
Add JDBC Driver
MENU -> Tools -> Preferences -> Database -> Third Party JDBC Drivers -> Add Entry
- Create a new connection
Database Type: Select "DB2"
- After connection
Connect to SQL Server
- Download JDBC Driver
URL: https://www.oracle.com/database/technologies/appdev/sqldev/thirdparty.html
The driver version listed on the above official site is a little old. If you want the latest version, please download it from the following site.
URL: https://sourceforge.net/projects/jtds/files/
Unzip the ZIP file.
- Add JDBC Driver
MENU -> Tools -> Preferences -> Database -> Third Party JDBC Drivers -> Add Entry
- Create a new connection
Database Type: Select "SQLServer"
If there is a connection error, please refer to the following documents.
https://docs.microsoft.com/en-us/troubleshoot/sql/connect/resolving-connectivity-errors
* After connection
How to Change Language Setting in SQL Developer
URL: https://www.oracle.com/database/technologies/appdev/sqldev/thirdparty.html
The driver version listed on the above official site is a little old. If you want the latest version, please download it from the following site.
URL: https://sourceforge.net/projects/jtds/files/
Unzip the ZIP file.
MENU -> Tools -> Preferences -> Database -> Third Party JDBC Drivers -> Add Entry
Database Type: Select "SQLServer"
By default, the display language in SQL Developer is the same as the OS language. You can change it as your need.
For example, you are using a Non-English language, and you want to switch the UI to English.
Here is the way to change language setting.
Edit following file:
C:\sqldeveloper\ide\bin\ide.conf
Add following lines to the end of the file, then restart SQL Developer.
AddVMOption -Duser.language=en
AddVMOption -Duser.country=US
How to Change Font Size in SQL Developer
Edit following file:
C:\Users\<username>\AppData\Roaming\SQL Developer\system20.4.1.407.0006\o.sqldeveloper\ide.properties
The string of system20.4.1.XXXXX may differs depending on the version.
Edit the following lines depending on the language you are using. After editing, restart SQL Developer.
End
Related Blogs
Personal Blogs on Oracle Cloud Infrastructure
How to connect to Oracle DB with OCI Database Tools
How to get the Service Name of OCI DBCS
SQL Developerで様々なDBに接続する方法 (Japanese version)
Author And Source
この問題について(How to connect to various DBs with SQL Developer), 我々は、より多くの情報をここで見つけました https://qiita.com/liu-wei/items/e570d02f694a04a5dcc8著者帰属:元の著者の情報は、元のURLに含まれています。著作権は原作者に属する。
Content is automatically searched and collected through network algorithms . If there is a violation . Please contact us . We will adjust (correct author information ,or delete content ) as soon as possible .