In SQL Developer, one of the most important navigation windows is the Connections window pane. This is the window where we have all our predefined database connections. Once we have added the required connection definitions to the databases, we can quickly launch the connections to the databases and start working. Another important fixed window after making database connections is the SQL Worksheet window. The SQL Worksheet provides a scratch pad for all SQL, PL/SQL, and SQL*Plus statements and commands. You can run individual statements or a collection of commands. You can also run scripts and execute PL/SQL. In essence, you can do all you might do in a command-line interface like SQL*Plus. The SQL Worksheet provides more; it offers code templates, code snippets, assists with code insight and completion, and maintains a history of commands. In this chapter, we will look at the connections window and other features offered in the SQL Worksheet and how to use them. We will use SQL and SQL*Plus commands to illustrate features, using PL/SQL only where needed specifically for a feature.
The Connections navigator lists all our database connections created by us. A double-click on each of those database connections will open the database connection for us using the saved credentials for that particular database. Objects are grouped into nodes in the Connections navigator to reflect their types. They are ordered by most commonly used with Tables, Views, and Indexes at the top of the list. You can refer to the following screenshot to see the grouping, order, and some of the currently available types displayed in the Connections navigator. The selection of browsable object types available increases with each release as the SQL Developer team adds support for more features. SQL Developer allows you to explore the contents of your database using the connection tree.
A complete list of supported database object types can be seen in the following screenshot:
To open a connection in the navigator, follow these steps:
- Under the Connections navigator, expand any of the existing database connection nodes to establish the database connection.
- The second option is to right-click on any of the database connection nodes under the Connections navigator and select Connect from the context menu.
The first time you connect to a database schema, whether you open an existing connection or click on Connect in the New Database Connections dialog, SQL Developer automatically expands the connection in the Connections navigator and opens a SQL Worksheet. This automatic opening of the SQL Worksheet is controlled by a preference: Open a Worksheet on connect. Go to Tools | Preferences, expand the Database node, and select Worksheet.
Tip
Reconnecting users
When doing administrative work with users, it can help to disconnect the user you are working with before making the changes and reconnect them afterwards. Some actions, such as dropping users or renaming connections, will not be possible without first disconnecting the connection.
Database schema or user?
The Oracle Concepts Guide states: A schema is a collection of database objects. A schema is owned by a database user and has the same name as that user.
Throughout the text, we use schema and user interchangeably. For the most part, we refer to the SYSTEM and HR schemas, meaning the collection of database objects. When closely or directly related to an activity we use "user", as the code does this. For example, consider DROP user HR cascade. It is a piece of code that drops all of the objects in the schema and the user itself.