Transcript SQL Developer

*
Unofficial Guide
*
Part 1 – The Basics
Connection setup and running basic queries
Playing with query results
sorting, filtering and exporting
Part 2 – A little more advanced
Bind variables, explain, finding objects, schema browsing
Part 3 – New features in Version 3
Query Builder
New Schema Browser
Exporting to PDF
Other Handy Stuff
Recommended preference settings
Some handy keyboard shortcuts
Questions
*
Initial Startup Window – Consists of a connections window, find database
object (this may not be there initially) and the editor worksheet (large
area on the right). To add connections click the plus button in the
connections window.
That will bring up the connection setup.
Type your connection name (whatever you want) and
Userid. Click TNS for the connection type and select the
appropriate database under Network Alias.
To test the connection click Test and it will display
Success if the connection was successful, then click
save to save your connection settings. This will save it
to your connection list. Once all your connections are
set up click cancel or connect to connect to one of
them. You should not save your password due to
security considerations and should use something like
Keepass to store them.
SQL developer password prompt
You can use something like
Keepass to store your database
passwords which has a master
password and then use a global
autotype key sequence to
autotype the password. To the
right is an example setup for
Keepass. Note: To do this you
must use the same password for
all databases because the
connection window is generic for
each connection. I can provide
more info on setup on this
offline if people are interested.
Keepass entry
Once your connections are set you can click the + next to a connection to connect
to the Database. This will open up a list of objects for your Schema. Generally
you won’t need these unless you are creating tables or views under your id. You
can collapse this list back after connecting unless you want to browse the schema
which I will talk about later.
In the Editor Worksheet window type a query. You can Run in a few different ways.
You can click the green arrow or hit Ctrl+Enter. If you have more than one query
you should put a slash or semicolon to indicate the end of the prior query. This
allows the editor to recognize the end of a query, otherwise you need to highlight
the query with your mouse to run (which can be handy also if you just want to run a
part of a query and not utilize all of the where criteria)
Completion Insight – This is Oracle’s auto-complete feature which will suggest
things as you type. There are settings in the preferences to set how quick this is
and to turn it off if you find it completely annoying. I usually adjust it so the
popup speed is a second or greater so I have to hesitate a bit before it will suggest
something.
SQL Developer will return a limited set of rows initially and as you scroll down it
will return more. If you want to get a count of how many were returned you can
right click in the results window and click count rows. Or you can click in the
results and do a Ctrl+Page Down to go to the bottom of the results (not
recommended for large tables since it will load many rows into memory and slow
down your machine).
Update/Insert/Delete SQL will run as a script and display the results in a script
output window. To commit, click the symbol above with the checkbox,
or
click the symbol with arrow to rollback
Also, F11 is commit and F12 is rollback.
Multiple database connections results in multiple tabs. You can open a new
connection to a new or the same database by clicking the sql dropdown
above the tab group.
You can switch your tab to another database by changing it on the
right side. This is helpful if you want to run the same query in
another database.
At the bottom you should see SQL History. This is a history of prior queries that
you have run. If you double click on a SQL in the history it will transfer up to
the editor window wherever your cursor is currently positioned. The history is
also searchable, and it shows you number of times executed and an average of
how long it took.
*
Double clicking on a column will sort the results, it will toggle between
ascending and descending order. An arrow will show the direction.
To sort by multiple columns,
right click on the column
headers and select sort. It will
bring up a dialog box where
you can select the columns
and the direction to sort by.
The result will show the sort
order number and direction.
You can also filter your results by right clicking on the column header of the
column you want to filter. A dialog box will be presented where you can
select or type what you want to filter on. You can filter on multiple
columns.
Here is an example of two columns being filtered (notice the funnel). You
can clear all filters or redo a column filter by right clicking again on the
column header and selecting filter column or clear all filters. If you just
want to clear one column just select it to filter and erase your filter.
To save a query result for later reference you can ‘pin’ it by clicking the
pushpin button in the result window above the column headers. This
will open subsequent queries in a new query tab.
Once you pin your query a handy thing you can do is rename the tab to
something meaningful for later reference, you can right click on the tab
and click rename to do this. Note: The pinned queries hang around for
the session only.
Exporting Data – You can quickly export data out to various formats by right
clicking on the result grid and clicking Export. This will export the result of
your query to various formats including Excel, csv and several more.
To the right is a list of formats you can export to. You can export to
a file(s), clipboard or many other options. The Table Name is used
when creating insert statements and some of the other file types, it
defaults to EXPORT_TABLE so you would want to rename it for those
file types. If going to Excel the table name doesn’t really matter.
Note: Files will be saved to the remote apps drives.
*
Instead of hard coding values for a query you can use bind variables. This
is handy if you are using the same variable multiple times. When you run
the query it will prompt you for the variables. It remembers your
variables during your session and across queries and connections so you
can just adjust a value once and have it affect multiple things you are
checking.
Explain Plan – To do an explain to find out the relative cost of your query and
how the query is getting your data, you can execute an explain plan by
clicking on the explain button
or F10
Popup Describe - You can do a describe of a table by clicking on the name and
then either right click and click Popup Describe or press Shift F4. It gives quite a
lot of information as shown above. Some of the more useful is the column info,
constraints, and index information.
One of the better things to have available is the find Database Object. You can
make it viewable by clicking View->Find DB Object. It will open a new window
and I usually drag it below my connections.
In the window you can select the connection to
search and then some or all of the name. I
usually keep the % checked which adds a % on
the end for a like search. It will return many
database object types so you need to know
which ones are which. Here is a quick (not all
inclusive guide)
Table
View
Public Synonym
Index
A Single click will open up a new tab with
information on the object.
s
Clicking the More button allows you to be more specific in your search.
However, the Schema tends to default to your schema when you do
this so you will want to switch that to ALL so that you can find all
occurrences. Set the type to whatever you are looking for, TABLE for
example. Now the fun part of this.
If you open a table from Find DB object you can edit it. You can update
columns add new rows, delete rows, filter the results. Click the commit
or rollback buttons to save or cancel your changes.
A handy option is the Duplicate row which you can invoke by right
clicking on a row and selecting Duplicate row. This allows you to
copy a row and then modify the key fields to make a new row.
Useful if you want to test a specific condition which might be
hard to create otherwise.
Schema Browsing – To look at all objects for a user
other than yourself you can expand the ‘Other
Users’ tree under your connection. I would
recommend setting up filters for these since the
lists can be large and take a long time for the tool
to query. These stick around until you unset them.
Once you have the users you want to look at you can
look at their objects, again filters are recommended
Drag and drop SQL from the schema
browser. Once you have a list you
can drag tables/views over to the
editor to create SQL statements for
you.
Example of a dragged Update statement.
*
A new feature in version 3 is the query builder. This can be handy for
doing quick joins. Because of performance it is usually better to start a
query in the worksheet with the tables you want then click the query
builder tab and you will see something like the lower picture.
You can then click and drag to the columns from one table to the other to
create your join keys.
By default an inner join will be done. However you can right click on the
join arrows and select an outer join.
When you click back to the worksheet you will see your SQL has been altered
to reflect what you set up in the query builder.
You can also add criteria and do other things such as getting counts and
sorting. Also you can select the columns you want by checking them in the
table layout above which will add them in the lower pane. You can also set
where criteria in the lower pane as illustrated above.
So setting the criteria like it was shown on the previous slide results in
the following SQL in the worksheet.
The Query Builder can be a quick handy way to start a SQL for later
refining. I usually only do the join criteria to get a start and then
manually add my own other criteria but there is the capability to do
more than that. Feel free to play around with it.
New Schema Browser – There is an enhanced schema browser in
version 3. If you right click on your connection and click Schema
Browser you can invoke it.
The schema browser will create a new tab next to connections. From
there you can select the schema and objects you are looking for. You can
also use the filter at the bottom to narrow down your search.
From there you can do the same things as the old schema browser such
as drag and drop statements or opening the object to look at
properties or update data. I believe this is a big improvement to the
old schema browser.
Another thing added in version 3 was the ability to export
data to PDF. By right clicking on the results window and
clicking export, you can then select the format of PDF.
You get a window that looks like this.
This produces a PDF. It is somewhat limited since it defaults to portrait
so columns can overflow to other pages and be hard to match up. It is
really only good for small numbers of columns at this point, hopefully
they will provide enhancements to this in future releases.
*
Some settings I would recommend under Tools->Preferences
Show Line numbers adds line numbers to your editor window
which can be handy if you get an error and it references a
specific line
By default a date is displayed as a DD-MON-RR format
which doesn’t show time. You can change this in the NLS
section of the preferences to your preferred format. I
prefer to see it as shown above (personal pref).
Look and feel. I prefer the windows theme. You can try
both and see what you like.
To keep multiple objects windows open when you are doing a Find DB
Object click the Freeze Object Viewer checkbox. I would also
recommend unchecking Open Object on a Single Click which requires
a double click to open an object. If you double click without this
then you get a connection busy popup which can be annoying.
Handy shortcut Keys in the editor window
Ctrl + / - will comment out lines you have selected. It is a toggle so it
will uncomment as well.
Ctrl + Enter – To run a query
F10 – Will do an explain on your query
F11 – will do a commit (be careful of this one)
F12 – will do a rollback
Ctrl + F7 – will format sql (it does a fairly decent job and is somewhat
customizable in the preferences).
There are many more functions that are available to the DBA’s
such as:
Database Copy
Database Diff
Database Export
Many more
There other features that I am sure that I have missed but
hopefully this is a good start for most everyone.
*