SQLDeveloper

Oracle SQL Developer is a free graphical tool for database development and data manipulation. With SQL Developer, you can browse database objects, run SQL statements and SQL scripts, and edit and debug PL/SQL statements. You can also run any number of provided reports, as well as create and save your own, import data from Excel spreadsheets, and export data to Excel too.

Important Note: The easiest method of connection is to use the JDBC URL (see below), but historically, most people connect using use the tnsnames.ora file, this is found in this folder if you're using the Oracle Client: c:\oracle\product\10.2.0\client\network\admin (this path can differ from pc to pc or if you have installed a different version). There is a bug in SQL Developer that Oracle says it's working on that limits the amount of tns entries that SQL Developer imports, so if you are trying to connect, and you don't see your database in the connection box, then edit your tnsnames.ora file by hand, and cut'n'paste the database entries you need to the top of the file, save it, and try to find it in the connection box in SQL Developer again. Considering the issues with the TNS entries, I recommend connecting using the JDBC URL method.

Connecting Using JDBC URL

click on the "New Connection" button

select the "Advanced" connection type radio button

type in your connection string into the "Custom JDBC URL" box, the format is as follows:

jdbc:oracle:thin:@SERVERNAME:PORT:INSTANCE

enter your connection name, username (schema), and password

click on the "Save Password" checkbox

click on the "Test" button

if all is well, click on the "Save" button (so you don't have to do this next time)

click on the connect button

Connecting Using TNS Names

click on the "New Connection" button

select the "TNS" connection type radio button

choose your instance from the "Network Alias" pull-down

enter your connection name, username (schema), and password

click on the "Save Password" checkbox

click on the "Test" button

if all is well, click on the "Save" button (so you don't have to do this next time)

click on the connect button

Exporting data to an Excel Spreadsheet

right click on a table in the tree

select "Export Data"

choose XLS

Importing data from an Excel Spreadsheet

right click on a table in the tree

select "Import Data"

select file to import

choose "Columns" "Data Types" and "DML" (you need to click through each of these tabs for this to work)

click on "Insert"

Comparing Two Schemas

This dialog box is displayed if you click Tools, then Schema Diff. Use this box to find differences between objects of the same type and name (for example, tables named CUSTOMERS) in two different schemas, and optionally to update the objects in one schema (destination) to reflect differences in the other schema (source). Use the Source tab to specify one database connection, and use the Destination tab to identify the database containing the schema with objects to be compared with the source schema. The database for the destination schema can be the same as, or different from, the database for the source schema. Source tab Connection: Database

Unable to see SQL Source for Views

If you see the following error when trying to see the source for a View:

DBMS_METADATA was unable to generate SQL. Now using internal DDL generator

then refer to this forum entry

Object Search

Go to Reports -> Data Dictionary Reports -> PL/SQL -> Search Source Code. You can search by name and/or source.

reset a password with sql developer

Either right-click on the (disconnected) connection and select "reset password", or (if the password hasn't already expired):

alter user YOURUSERNAME identified by NEWPASSWORD replace OLDPASSWORD;

(They don't need to have the grants to change their own password).

Debugging

In order to debug stored procedures or functions, the DBA needs to grant permissions to that user:

GRANT DEBUG ANY PROCEDURE, DEBUG CONNECT SESSION TO UserName;

Adding Extensions

Here's an example (look at the second answer) on how to add an extension to add foreign-key lookup (referential integrity) to the table explorer. You can use the example to build other extensions.

Where to Learn More

Watch : SQL Developer the movie! (please note that they use a different connection method in the movie, but it's quite useful to watch).

Browse : The SQL Developer Homepage

Search Forum : The SQL Developer Forum