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 URLclick 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:
Connecting Using TNS Namesclick 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 Spreadsheetright click on a table in the tree select "Export Data" choose XLS
Importing data from an Excel Spreadsheetright 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
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).
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;
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