Connect to Autonomous Database Using Oracle Database Tools

Oracle Database Tools such as SQL Developer, SQLcl and SQL*Plus can be used with the Autonomous Database.

The following sections provide step-by-step instructions for connecting to Autonomous Database using these tools.

Connect with Oracle Database Actions

Tip:

For a "try it out" alternative that demonstrates these instructions, run Lab 14: Database Actions Console in the Oracle Autonomous Database Dedicated Workshop.
Oracle Database Actions, a browser-based application that provides many database development, management and monitoring features is built into dedicated Autonomous Databases. Therefore, you can use it without downloading or installing additional software on your system. Database Actions was earlier known as SQL Developer Web.

By default, only the ADMIN database user has access to Database Actions. Before you can sign in as another database user, the ADMIN user must enable access for that user, as described in Enabling User Access to Database Actions in Using Oracle Database Actions.

Note:

To sign into Database Actions, the system you are using must have network access to the dedicated Autonomous Database, as described in Configure a Development System to Access the Database.
  1. In your web browser, sign in to Oracle Cloud and navigate to the Details page for the dedicated Autonomous Database.

    For instructions, see View Details of a Dedicated Autonomous Database.

  2. Open the Tools tab.

  3. Click Open Database Actions in the Database Actions tile.

  4. In the Database Actions dialog, click the Copy link in the Access URL field to copy the URL to your clipboard.



  5. If you are an application developer, paste the URL into a text editor and then change "admin" to the user name of the database user you are signing in as. Copy the edited URL and paste it into your web browser's address bar.

    However, as a database administrator, you can paste the copied URL into your web browser's address bar directly.

Connect with Oracle SQL Developer Version 18.2 or Later

Tip:

For a "try it out" alternative that demonstrates these instructions, run Lab 8: Configuring a Development System in the Oracle Autonomous Database Dedicated Workshop.
Oracle SQL Developer is a free integrated development environment that simplifies the development and management of Oracle Database in both traditional and cloud deployments.

Note:

To connect Oracle SQL Developer to a dedicated Autonomous Database, the system running Oracle SQL Developer must have network access to the database, as described in Configure a Development System to Access the Database.

To create a new connection to a dedicated Autonomous Database, do the following:

  1. Download the zip file containing client credentials for your database to a secure directory on system.
    This zip file is available for download from the database's Details page in the Oracle Cloud console. If you have an Oracle Cloud user account that permits you to access this page, download the credentials as described in Download Client Credentials. Otherwise, you will need to get the zip file from the administrator of the database, together with the password that was used to protect the zip file.
  2. Start Oracle SQL Developer and in the connections panel, right-click Connections and select New Database Connection....
  3. Choose the Connection Type Cloud Wallet.
  4. Enter the following information:
    • Connection Name: Enter the name for this connection.

    • Username: Enter the database username. You can either use the default administrator database account (ADMIN) provided as part of the service or create a new schema, and use it.

    • Password: Enter the password for the database user.

    • Connection Type: Select Cloud Wallet (with the older version, SQL Developer 18.2, this is Cloud PDB)

    • Configuration File : Click Browse, and select the client credentials zip file.

    • Service: Enter the service name. The client credentials file provides the service names.

  5. Click Connect to connect to the database.

Connect with Oracle SQL Developer Versions Earlier Than 18.2

Oracle SQL Developer is a free integrated development environment that simplifies the development and management of Oracle Database in both traditional and cloud deployments.

Note:

To connect Oracle SQL Developer to a dedicated Autonomous Database, the system running Oracle SQL Developer must have network access to the database, as described in Configure a Development System to Access the Database.

Oracle recommends that you use Oracle SQL Developer version 18.2 or later; however, earlier versions of SQL Developer will work with Autonomous Database.

To create a new connection to a dedicated Autonomous Database, do the following:

  1. Download the zip file containing client credentials for your database to a secure directory on system.
    This zip file is available for download from the database's Details page in the Oracle Cloud console. If you have an Oracle Cloud user account that permits you to access this page, download the credentials as described in Download Client Credentials. Otherwise, you will need to get the zip file from the administrator of the database, together with the password that was used to protect the zip file.
  2. Start Oracle SQL Developer and in the connections panel, right-click Connections and select New Connection.
  3. Choose the Connection Type Cloud PDB.
  4. Enter the following information:
    • Connection Name: Enter the name for this connection.

    • Username: Enter the database username. You can either use the default administrator database account (ADMIN) provided as part of the service or create a new schema, and use it.

    • Password: Enter the password for the database user.

    • Connection Type: Select Cloud PDB.

    • Configuration File : Click Browse, and select the client credentials zip file.

    • Keystore Password: Enter the password generated while downloading the client credentials from Autonomous Database.

    • Service: Enter the service name. The client credentials file provides the service names.

Connect with Oracle SQLcl

Tip:

For a "try it out" alternative that demonstrates these instructions, run Lab 8: Configuring a Development System in the Oracle Autonomous Database Dedicated Workshop.
SQLcl (Oracle SQL Developer Command Line) is a command-line interface for Oracle Database. It allows you to interactively or batch execute SQL and PL/SQL. SQLcl provides in-line editing, statement completion, and command recall for a feature-rich experience, all while also supporting your previously written SQL*Plus scripts.

Note:

To connect Oracle SQLcl to a dedicated Autonomous Database, the system running Oracle SQLcl must have network access to the database, as described in Configure a Development System to Access the Database.

You can use SQLcl version 4.2 or later with Autonomous Database. Download SQLcl from oracle.com.

SQLcl can connect to a dedicated Autonomous Database using either an Oracle Call Interface (OCI) or a JDBC thin connection.

SQLcl with Oracle Call Interface

To connect using Oracle Call Interface, use the –oci option, supply the database user name, a password, and the database service name provided in the tnsnames.ora file. For example:

sql -oci

SQLcl: Release 19.1 Production on Mon Jan 18 09:28:38 2021

Copyright (c) 1982, 2021, Oracle.  All rights reserved.

Username? (''?) sales_trans@atpc1_low
Password? (**********?) **************
Last Successful login time: Mon Jan 17 2019 15:29:19 -07:00

Connected to:
Oracle Database 19c EE Extreme Perf Release 19.0.0.0.0 - Production
Version 19.9.0.0.0

SQL>

When connecting using Oracle Call Interface, the Oracle Wallet is transparent to SQLcl.

SQLcl with a JDBC Thin Connection

To connect using a JDBC Thin connection, first configure the SQLcl cloud configuration and then connect to the dedicated Autonomous Database.

  1. Start SQLcl with the /nolog option.
    sql /nolog
    
  2. Configure the SQLcl session to use your Oracle Wallet:
    SQL> set cloudconfig directory/client_credentials.zip
    Wallet Password:  **********
  3. Connect to the Autonomous Database:
    SQL> connect username@servicename
    password

    For example:

    sql /nolog
    
    SQLcl: Release 19.1 Production on Mon Jan 18 09:28:38 2021
    
    Copyright (c) 1982, 2021, Oracle.  All rights reserved.
    
    SQL> set cloudconfig /home/atpc/wallet_ATPC.zip
    Operation is successfully completed.
    Operation is successfully completed.
    Using temp directory:/tmp/oracle_cloud_config3643858537577169472
    
    SQL> connect admin@atpc_medium
    Password? (**********?) ************
    Connected.

For more information, on the connection types specified in tnsnames.ora, see Predefined Database Service Names for Autonomous Databases.

For information on SQLcl, see Oracle SQLcl.

Connect with SQL*Plus

Tip:

For a "try it out" alternative that demonstrates these instructions, run Lab 8: Configuring a Development System in the Oracle Autonomous Database Dedicated Workshop.
SQL*Plus is a command-line interface used to enter SQL commands. SQL*Plus connects to an Oracle database.

Note:

To connect SQL*Plus to a dedicated Autonomous Database, the system running SQL*Plus must have network access to the database, as described in Configure a Development System to Access the Database.

To install and configure the client and connect to a dedicated Autonomous Database using SQL*Plus, do the following:

  1. Prepare to connect by following the instructions in Prepare for Oracle Call Interface (OCI), ODBC, and JDBC OCI Connections.
  2. Connect using a database user, password, and database service name provided in the tnsnames.ora file.

    For example:

    sqlplus sales_trans@atpc1_low  
    
    SQL*Plus: Release 18.0.0.0.0 - Production on Mon Jan 18 09:50:13 2021
    Version 18.5.0.0.0
    
    Copyright (c) 1982, 2018, Oracle.  All rights reserved.
    
    Enter password:
    Last Successful login time: Mon Jan 18 2021 09:33:57 +00:00
    
    Connected to:
    Oracle Database 19c EE Extreme Perf Release 19.0.0.0.0 - Production
    Version 19.0.0.0.0
    
    SQL>

    Note:

    The Oracle Wallet is transparent to SQL*Plus because the wallet location is specified in the sqlnet.ora file. This is true for any Oracle Call Interface (OCI), ODBC, or JDBC OCI connection.