Creating your first connection
To complete this quick walk-through, you need to know the username and password
of the SYSTEM user. You also need to know the location of the database, whether
this is the machine name or the IP address, and the database SID.
To begin, start SQL Developer. The very first time you start SQL Developer, you’ll
be asked if you want to migrate from a previous version. Select No and allow the
tool to start up.
The first thing you need to do after you have started SQL Developer for the first time
is to create your initial connections.
Create a connection for SYSTEM:
- Select Connections, right-click and select New Connection. This invokes
the New Database Connection dialog. You can edit and control all of the
connection details using this dialog.
- Complete the details, as seen in the following screenshot, relevant to your
- Click on Test to ensure you have the connection details correct and click
You are now connected as SYSTEM. Use this connection to verify your other users,
by continuing as follows:
- Select the new connection you have created, expand the node, and scroll
down to Other Users.
- Expand Other Users and find the user HR. Right-click and select Edit
User. Verify that the account for HR is unlocked and the Password has not
expired, that is, the properties Account is Locked and Password Expired are
deselected. If either of these is selected, deselect them. You can change the
password for HR at this point too. It’s good practice to modify the passwords
of the shipped sample schemas once you have unlocked them.
Now you are really ready to begin!
- Once again, select Connections, right-click and select New Connection.
- Give the connection a name (for example, HR_11g).
- Provide the Username (HR) and a Password. If you are working on Oracle
Database 11g, be aware that passwords are now case sensitive.
- Select the Save Password checkbox. This makes life easy while you are
working with SQL Developer. Passwords are stored in an encrypted file.
However, you should always be aware of saving passwords and possible
security implications this may have.
- Use the Basic connection. This requires no more detail than the location
of the database and the SID, details you have.
- Click on Test to test the connection.
- Click on Connect.
Using basic commands in the SQL Worksheet
As soon as you connect to a user, SQL Developer opens an SQL Worksheet.
You may have started working with Oracle using the SQL*Plus command line,
or even the GUiwindow. Either way, you’d start with a selection of SQL*Plus and
Enter the following into the SQL Worksheet:
SELECT * FROM DEPARTMENTS;
Press the F5 key (or use the Run Script button).
The output of both commands appears in the Script Output tab, which appears
below the SQL Worksheet (as seen in the previous screenshot). Both commands
are handled by a few simple clicks of the mouse in SQL Developer.
Select and expand the HR_11g connection in the Connections navigator. Expand
the Tables node and select DEPARTMENTS.
The DEPARTMENTS tab now opens, displaying a list of the column names
and details. These are the same details as given by the DESC (describe) SQL*Plus
command that you entered in the SQL Worksheet. It also provides additional detail,
such as the Primary Key and column comments.
Select the Data tab and notice that you now see the output from your second
command. These two tabs are included with a number of other tabs, each with
additional details about the DEPARTMENTS table. You would need to write a
number of SQL queries in order to get the additional detail from the data dictionary
if you were working in SQL*Plus.
Select the EMPLOYEES table. Notice that the new table, EMPLOYEES, immediately
replaces the previous DEPARTMENTS table with its details. Select the Triggers tab,
and select one of the triggers. The trigger and related trigger detail is displayed in a
master-detail wind ow:
Browsing and updating data
Return to the EMPLOYEES data by again selecting the Data tab. The data grid
that is displayed provides a variety of options. To get started with the data grid,
double-click on an item or field, such as the name of one of the employees, and
change it. Tab out of the field and notice that the change is applied to the data grid
and an asterisk (*) fl ags the record. Commit and Rollback buttons are available to
send the change to the database, or to undo your action. Roll back the changes.
Once again you get feedback, this time in the Data Editor log, as shown in the
Select the Reports navigator and expand the Data Dictionary Reports node. Expand
the Table node and review the available reports. Expand Constraints and select the
Unique Constraints report. As you select the report, a dialog displays requesting
the Connection name. Select the connection you created, HR_11g, and click on OK.
An Enter Bind Values dialog now appears, requesting the table name as an input
parameter. Click on Apply to accept the default, which in this case, means all tables:
Run the same report for any user by selecting the Connections
drop-down list on the right-hand side.
Navigating around SQL Developer
SQL Developer has a selection of windows, navigators, and tabs. On start-up,
you are presented with the main navigator toolbars and menus:
The two main navigators: Connections and Reports, are presented in a tabbed
window. These and other navigators, such as the Versioning Navigator, are
available through the main View menu. You can also open windows such as
Snippets, Recent Objects, and Find DB Objects using the View menu.
Any navigators that you open during a session, and that are
still open when you close the product, are automatically opened
when you restart the product.
Managing SQL Developer windows
With the exception of the SQL Worksheet and its associated tabs, all of the main
tabbed dialogs can be minimized or maximized and accessed while docked or
undocked. These menu controls are available through context menus in t he tabs:
You can rearrange tabbed windows by selecting and dragging the tab into place.
Once any window is minimized, roll your mouse over the minimized tab to display
a fl oating window that stays active while your mouse lies over it and rolls back
into place when you move off. This is very useful when working with temporary
windows such as Snippets and Find DB Object. The following screenshot shows
the fl oating window for the Snippets dialog. If you roll the mouse over the area,
you can work in the window (for example, navigating about until you have located
the snippet of code you are after, and then drag the code onto the worksheet). The
window will minimize out of the way once you have moved off it.
You can undock the fl oating window, move it off to one side, and keep it undocked
while you work with the SQL Worksheet. In a dual-monitor setup, you can drag the
fl oating window onto one monitor, while working with the SQL Worksheet on the
Once you start working with connections, you have more windows and tabs to deal
with, especially if you have more than one connection created. Select the HR_11g
connection created in the previous section, expand the connection and Tables node,
and select EMPLOYEES. In the table definition window, select the pin button, as
shown below, to free ze the view.
Now, select the DEPARTMENTS table. A second table definition window opens
to display the details from the new table. Select the DEPARTMENTS tab and drag
it down to the lower portion of the screen. Notice the shape of the dragged object
change as you drag it slightly to the left, to the center, and the lower portion of
the window. Each of the shapes represents a different layout position. Release the
mouse to secure the new position. The screenshots, which follow, display two of
the availab le positions:
When you tile windows, you can compare the details of two tables. However, as each
table has a selection of tabs, it’s useful to be able to review details in the tabs without
having to switch back and forth between tabs. As is true for other layout features,
you can split the document using a menu, or by drag-and-drop. Each of the object
definitions tabbed displays has a drag bar on the top and bottom right that you can
select and drag to split the window horizontally, or vertically:
Almost all of the tabs in SQL Developer will maximize when double-clicked. There
are a few that do not follow this rule, such as the tabs related to the SQL Worksheet.
In general, this works for top-level tabs, which is any tab you can undock and move
about, and not for secondary tabs. To maximize a tab, double-click on the tab. A
second double-click will reverse the process.
Double-click on the tab to maximize a top-level tab.
Double-click again to revert to the previous layout.
Resetting the window layout
If you move your windows about a great deal, you may find that you want to get
things back to the default settings.
The example in the following screenshot displays the standard docked Connections
and Reports windows to the left. We have also opened the Versioning Navigator,
which by default docks below the connections. We have also docked the Snippets
window to the right. These windows fill the columns to the left and right, leaving a
central window for the editors and log files.
The layout is controlled by the window layout set in the preferences. Select
Tools | Preferences, under the Environment node in the tree select Dockable
Windows. The default layout, and the one that matches the example in the
previous screenshot, is shown in the following screenshot:
Each of the little curved arrows on the diagram is clickable, and as such controls
the positioning of the windows. Clicking on the arrow extends or contracts the area
taken up by the docked window.
In our example, and in the default SQL Developer environment, there is no
full-docked window across the top of the screen. However, if you drag a window
into the docked position below the main tool bar, it would stretch across the screen,
as shown in the following screenshot:
If you find your windows are in a muddle, first verify that the Dockable Windows
layout is what you want, and then drag the various dockable windows back into
place. Some suggestions on the SQL Developer forum are to remove the system
folder (it works, but that’s an extreme solution).
Finding more help
SQL Developer has a site on the Oracle Technology Network, http://www.oracle.
com/technology/products/database/sql_developer/index.html. This provides
links to current and past magazine articles, white papers, and team blogs. It also has
links to brief product demonstrations and longer hands-on exercises.
There is an active user forum on OTN, http://forums.oracle.com/forums/
forum.jspa?forumID=260, which is monitored by the development team and
The SQL Developer Exchange, http://sqldeveloper.oracle.com, is a site where
anyone using SQL Developer can log feature requests and vote on other requests
already posted. In addition to posting feature requests, the site hosts reports and
You’ve started and should now have SQL Developer installed. You should have a
few connections created and an initial idea of how to navigate around the product.
You are now set to learn a lot more about SQL Developer. From here you can dip
into different chapters, focusing on the areas you’re most interested in.
In the next chapter, we’ll show you how to browse different types of objects, and use
SQL Developer to look at them in greater detail. We’ll review the different editors
and dialogs available and how you can manage what you see using preferences.
We’ll also show you the different ways you can create objects and how to manipulate