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

    Menu: about  ::  contents   ::   apps  ::  contact  ::  twitter  ::  github
    © Roqet 2014 :: 2014-11-24 10:40:46