Database Connections

A connection is a SQL Developer object that specifies the necessary information for connecting to a specific database as a specific user of that database. You must have at least one database connection (existing, created, or imported) to use SQL Developer.

You can connect to any target Oracle database schema using standard Oracle database authentication. Once connected, you can perform operations on objects in the database. You can also connect to schemas for selected third-party (non-Oracle) databases, such as MySQL, Microsoft SQL Server, Sybase Adaptive Server, Microsoft Access, and IBM DB2, and view metadata and data.

When you start SQL Developer and whenever you display the database connections dialog box, SQL Developer automatically reads any connections defined in the tnsnames.ora file on your system, if that file exists. You can specify the tnsnames.ora location in the Database: Advanced preferences.By default, tnsnames.ora is located in the $ORACLE_HOME/network/admin directory, but it can also be in the directory specified by the TNS_ADMIN environment variable or registry value or (on Linux systems) the global configuration directory. On Windows systems, if the tnsnames.ora file exists but its connections are not being used by SQL Developer, define TNS_ADMIN as a system environment variable. For information about the tnsnames.ora file, see the "Local Naming Parameters (tnsnames.ora)" chapter in Oracle Database Net Services Reference.

You can create additional connections (for example, to connect to the same database but as different users, or to connect to different databases). Each database connection is listed in the Connections navigator hierarchy.

To create a new database connection, right-click the Connections node and select New Database Connection. Use the dialog box to specify information about the connection (see Create/Edit/Select Database Connection). You can also create a new database connection by selecting an existing connection in that dialog box, changing the connection name, changing other connection attributes as needed, and clicking Save or Connect.

To create (automatically generate) a database connection for each unlocked user account in the Oracle database instance on the local system, right-click the Connections node and select Create Local Connections. The connections are placed in a folder named Auto-Generated Local Connections. Note that for these autogenerated connections (except for the one named system-<database-name>), you will always be prompted for the password when you connect, and you cannot edit the user name or password in the connection properties dialog box.

To edit the information about an existing database connection, right-click the connection name in the Connections navigator display and select Properties. Use the dialog box to modify information about the connection, but do not change the connection name. (See Create/Edit/Select Database Connection.)

To organize connection groups using folders, see Using Folders to Group Connections.

To export information about the existing database connections into an XML file that you can later use for importing connections, right-click Connections in the Connections navigator display and select Export Connections. Use the dialog box to specify the connections to be exported (see Export/Import Connection Descriptors).

To import connections that had previously been exported (adding them to any connections that may already exist in SQL Developer), right-click Connections in the Connections navigator display and select Import Connections. Use the dialog box to specify the connections to be imported (see Export/Import Connection Descriptors).

To perform limited database management operations if you are connected AS SYSDBA, right-click the connection name in the Connections navigator display and select Manage Database. You can click to refresh the read-only display of memory (SGA and PGA) and tablespace information. If a listener is running with a static listener configured for the database, you can also click to start and stop the database.

To perform remote debugging if you are using the Java Platform Debugger Architecture (JPDA) and you would like the debugger to listen so that a debuggee can attach to the debugger, right-click the connection name in the Connections navigator display and select Remote Debug. Use the dialog box to specify remote debugging information (see Debugger - Attach to JPDA).

To estimate or compute statistics for objects in a database schema, right-click the connection name in the Connections navigator display and select Gather Schema Statistics. Statistics are used to optimize SQL execution.

To generate documentation in HTML format (comparable to Javadoc for Java classes) about a schema, right-click the connection name in the Connections navigator display and select Generate DB Doc. To view the generated documentation, open the index.html file in the output directory that you specified.

(Note that Generate DB Doc is also used for migration projects: right-click the migration project name in the Migration Projects navigator display and select Migration Project Doc; you can specify whether to include child nodes of the selected node and child master-detail per-row reports.)

To reset an expired password for the database user associated with a connection, right-click the connection name in the Connections navigator display and select Reset Password (enabled only if an OCI (thick) driver is available).

To rename a connection, right-click the connection name in the Connections navigator display and select Rename Connection.

To delete a connection (that is, delete it from SQL Developer, not merely disconnect from the current connection), right-click the connection name in the Connections navigator display and select Delete. Deleting a connection does not delete the user associated with that connection.

To connect using an existing connection, expand its node in the Connections navigator, or right-click its name and select Connect. A SQL Worksheet window is also opened for the connection (see Using the SQL Worksheet). To create a separate unshared worksheet for a connection, click in the worksheet and use Ctrl+Shift+N.

To disconnect from the current connection, right-click its name in the Connections navigator and select Disconnect.

To reconnect to a connection where the session had been terminated, right-click its name in the Connections navigator and select Reconnect.

To specify a preference for using an OCI (thick, Type 2) driver (if available) instead of a JDBC (thin) driver for basic and TNS (network alias) database connections, enable the Use OCI/Thick driver option under the Database: Advanced user preferences.

Related Topics

Using Folders to Group Connections

Sharing of Connections

Advanced Security for JDBC Connection to the Database

Connections with Operating System (OS) Authentication

Connections with Proxy Authentication

Create/Edit/Select Database Connection

Export/Import Connection Descriptors

SQL Developer Concepts and Usage

Using Folders to Group Connections

You can use folders in the Connections navigator to organize connections into groups: for example, one folder for connections on your local system, another for connections on the test system, and another for connections on the production system.

To create a folder to hold connections, right-click the name in the Connections navigator of a connection to be added to the folder, select Add to Folder and then New Folder, and specify the folder name (such as Local Connections).

To add more connections to a folder, right-click the name in the Connections navigator of a connection to be added to the folder, and select Add to Folder and then the name of the folder into which to add the connection.

To move a connection from one folder to another folder, right-click the connection name under its current folder, select Add to Folder, and then either the name of the destination folder or New Folder to move the connection to a new folder to be created.

To remove a connection from the folder, right-click the connection name under the folder and select Remove from Folder. (This does not delete the connection; it is moved to the top level in the Connections navigator hierarchy display.)

To remove a folder, right-click the folder name select Remove Folder. (This does not delete any connections that are in the folder; these connections are moved to the top level in the Connections navigator hierarchy display.)

To rename a folder, right-click the folder name, select Rename Folder, and specify the new name.

Sharing of Connections

By default, each connection in SQL Developer is shared when possible. For example, if you open a table in the Connections navigator and two SQL Worksheets using the same connection, all three panes use one shared connection to the database. In this example, a commit operation in one SQL Worksheet commits across all three panes. If you want a dedicated session, you must duplicate your connection and give it another name. Sessions are shared by name, not connection information, so this new connection will be kept separate from the original.

Advanced Security for JDBC Connection to the Database

You are encouraged to use Oracle Advanced Security to secure a JDBC or OCI connection to the database. Both the JDBC OCI and the JDBC Thin drivers support at least some of the Oracle Advanced Security features. If you are using the OCI driver, you can set relevant parameters in the same way that you would in any Oracle client setting. The JDBC Thin driver supports the Oracle Advanced Security features through a set of Java classes included with the JDBC classes in a Java Archive (JAR) file and supports security parameter settings through Java properties objects.

For more information about using Oracle Advanced Security, see Oracle Database JDBC Developer's Guide.

Connections with Operating System (OS) Authentication

When you create a connection to an Oracle database that is using operating system (OS) authentication, you can omit the user name and password; that is, specify a connection name and all the other necessary information, except do not specify a user name or password. For information about using external authentication, including the use of the OS_AUTHENT_PREFIX and REMOTE_OS_AUTHENT database initialization parameters, see Oracle Database Security Guide.

If you omit the user name and password trying to create a connection to a system that is not configured for external authentication, an error message is displayed.

Connections with Proxy Authentication

Proxy authentication enables one JDBC connection to act as a proxy for other JDBC connections. If you use the Proxy Connection option when you create a database connection, the connection will be used to connect as the specified user for the connection, but authenticated using the user name and either the password or distinguished name of the proxy user. For information about using a middle tier server for proxy authentication, see Oracle Database Security Guide.

For example, to create connection for a user named PROXY_USER but connecting using the user name and password of existing database user SCOTT, follow these steps.

  1. Create the proxy user and grant it the appropriate privileges:

    CREATE USER proxy_user IDENTIFIED BY <password>;
    ALTER USER proxy_user GRANT CONNECT THROUGH scott AUTHENTICATED USING PASSWORD;
    GRANT create session TO proxy_user;
    . . .<Grant other privileges as needed.>
    
  2. Create a new database connection. For example: connection name = proxy_conn, user name = scott, password = <password for scott>.

  3. Enable (check) Proxy Connection.

  4. In the Oracle Proxy Connection dialog box, select User Name for Proxy Type

  5. For Proxy User, enter PROXY_USER; and for Proxy Password, enter the password for the PROXY_USER database user.

  6. Click OK to close the Oracle Proxy Connection dialog box.

  7. Complete any other necessary connection information, and click Connect to create the connection.

In this example, when you connect using the proxy_conn connection, the user name and password for user SCOTT are used to connect to the database, but the connection sees those database objects that the PROXY_USER user is permitted to see.

The preceding instructions cause two sessions to be started (one for the proxy user and one for the proxy client) when the connection is opened. If you want to have a single session (with no second password or distinguished name required) started, you can follow these steps instead after you create the proxy user and grant it appropriate privileges:

  1. Create a new database connection. For example: connection name = proxy_conn, user name = proxy_user[scott], password = <password for proxy_user>.

  2. For Connection Type, specify TNS.

    (Note: Do not enable Proxy Connection.)

  3. For Network Alias, select the network alias for the database for the connection.

  4. Complete any other necessary connection information, and click Connect to create the connection.