To install and start SQL Developer, you download a ZIP file and unzip it into a desired parent directory or folder, and then type a command or double-click a file name.

When you start SQL Developer, the SQL Developer window appears.

The SQL Developer window generally uses the left side for navigation to find and select objects, and the right side to display information about selected objects.

Description of the illustration sqld_main_page.gif

The menus along the top of the page contain standard entries, plus entries for features specific to SQL Developer.

The left side of the SQL Developer window has tabs and panes for the Connections and Reports navigators, icons for performing actions, and a hierarchical tree display for the currently selected navigator.

In the figure, the HR database connection appears in the Connections navigator, and the schema objects for the HR schema appear in the metadata tree.

The metadata tree in the Connections navigator displays all the objects (categorized by object type) accessible to the defined connections. To select an object, expand the appropriate tree node or nodes, then click the object.

The right side of the SQL Developer window has tabs and panes for objects that you select or open. For example, the object pane in the figure displays information about a table named EMPLOYEES. (If you hold the mouse pointer over the tab label -- EMPLOYEES in this figure -- a tooltip displays the object's owner and the database connection.)

A database connection is a SQL Developer object that specifies the necessary information for connecting to a specific database as a specific user of that database. You must have at least one database connection (existing, created, or imported) to use SQL Developer.

1. In the Connections navigator in SQL Developer, right-click the Connections node and select New Connection.

   The New / Select Database Connection dialog box appears, with the Oracle tab displayed.

2. Enter the following information:

   • In the Connection Name field, enter the name to use for this database connection.

   • In the Username field, enter the name of the user for whom this database connection is being created.

   • In the Password field, enter the password for the user.

   • In the Connection Type field, select the database connection type.

     The connection types are:

     • Basic

     • TNS

     • LDAP

     • Advanced

     • Local/Bequeath

     When you choose a connection type, the fields below will change to be appropriate for the selected connection type. This example describes the fields for the Basic connection type.

   • In the Role field, select Default or SYSDBA, based on the role assigned to the user.

   • In the Hostname field, enter the name of the host where the database is located.

   • In the Port field, enter the port for the database.

   • In the SID field, enter the SID for the database (when the database connection is for a non-CDB user or for a multitenant container database (CDB) user):

     
     Description of the illustration sqld_create_connection4.gif

     When a database connection to a non-CDB or CDB is created for an administrative user such as SYS, SYSDBA is typically specified in the Role field for the connection.

   • In the Service name field, enter the service name for the pluggable database (PDB), including the domain name (when the database connection is for a PDB user):

     
     Description of the illustration sqld_create_pdb_conn_new.gif

     When a database connection to a PDB is created for an administrative user such as SYS, SYSDBA is typically specified in the Role field for the connection.

3. Optionally, click Test to test that the data you provided will allow the specified user to connect to the database.
4. When you are finished, click Connect to connect using the database connection, or click Save to save the database creation.

When you create a table, you specify the table type, and define its columns and constraints. Constraints are rules that help preserve data integrity.

This section contains the following topics:

• About Table Types

• About Table Column Attributes

• About Table-Level Constraints

• Other Table Creation Considerations

You can use SQL Developer to list all the tables in a specified schema, and to view the definitions of individual tables.

1. In the Connections navigator in SQL Developer, navigate to the Tables node for the schema that includes the table you want to display.

   If the view is in your own schema, navigate to the Tables node in your schema.

   If the table you want to display is in another user's schema, navigate to the Other Users node, expand it, find the name of the schema the table is in, and navigate to the Tables node.

   Examples of schema names include SYS and HR.

   Note:

   You must have the necessary privileges to view other schemas and the objects in those schemas.

2. Open the Tables node.

   The list of tables in the schema appears.

3. Click the name of the table that you want to display.

   A tab with the table name appears in the object pane, with the Columns subtab displayed. You can view the table definition on this tab.

Description of the illustration sqld_view_table.gif

See Also:

"About Tables"

1. In SQL Developer, search for a table as described in "Viewing Tables". For example, search for the tables in the HR schema.
2. Select the table that contains the data.

   For example, select countries.

   A tab with the table name appears in the object pane, with the Columns subtab displayed.

3. In the object pane, click the Data subtab.

   The data for the table appears on the Data subtab.

   
   Description of the illustration sqld_view_table_data.gif
4. (Optional) Click a column name to sort the data by that column.
5. (Optional) Click the SQL subtab to view the SQL statement that defines the table.

You can use SQL Developer to create a table.

1. In SQL Developer, navigate to the Tables node in the schema where you want to create the new table, as described in "Viewing Tables".
2. Right-click the Tables node and select New Table.

   The Create Table dialog box appears, with the Table tab displayed.

   You can create simple tables quickly using only the default options on the Table tab. If you want to create more advanced tables, select the Advanced checkbox, which provides many more options for table creation. Unselect the Advanced checkbox to return to the default options on the Table tab.

3. In the Schema field, select HR.
4. In the Name field, enter PURCHASE_ORDERS.
5. In the Columns section, enter column information for each of the columns in the PURCHASE_ORDERS table as specified in the table in the introduction to this topic. For example, for the first column in the PURCHASE_ORDERS table, enter the name PO_NUMBER and the data type NUMBER, select the Not Null check box, and click in the PK column to indicate that PO_NUMBER is the primary key for the table. Then click the green plus sign icon to add information for the next column. Continue in this manner until you have added the information for all of the columns to PURCHASE_ORDERS.
   
   Description of the illustration sqld_create_table_new.gif
6. Click OK.

   The PURCHASE_ORDERS table appears under the Tables node in the Connections navigator.

• Example: Adding Table Columns

• Example: Deleting a Table Column

• Example: Adding a New Table Constraint

• Example: Modifying an Existing Table Constraint

• Example: Deleting a Table Constraint

You can use SQL Developer to load data into a table. You can load data from an .xls file or a .csv file into the table.

1,Office Equipment,25-MAY-2012,1201,13-JUN-2012
2,Computer System,18-JUN-2012,1201,27-JUN-2012
3,Travel Expense,26-JUN-2012,1340,11-JUL-2012

1. In SQL Developer, navigate to the PURCHASE_ORDERS table in the HR schema, following the instructions in "Viewing Tables".
2. Right-click the PURCHASE_ORDERS table and select Import Data.

   The Open dialog box appears.

3. Navigate to and select the load.csv file that includes the data you want to import into the table, and then click Open.

   The Data Import Wizard appears, with the Data Preview page displayed.

4. Make sure that Header is deselected, Format is set to csv, Line Terminator is set to environment default, and that Left Enclosure and Right Enclosure are set to none. Then click Next.
   
   Description of the illustration sqld_import_wiz_1of5_new.gif

   The Import Method page appears.

5. On this page, select:

   • Insert in the Import Method field.

   • PURCHASE_ORDERS in the Table Name field.

   • A value in the Import Row Limit field that is greater than the number of rows in your .csv file.

6. Click Next.

   The Choose Columns page appears.

7. Move the columns that you want to import into the Selected Columns list, and arrange them in the order you want.
8. Click Next.

   The Column Definition page appears.

9. Map the source data from the .csv file to the existing table. For each source data column on the left, select the column on the right to import this data into.

   For example, in the Source Data Columns table on the left, select the first column, and then in the Target Table Columns table on the right, select in the Name field the name of the column in the database table that will store that data. Map the data for each of the columns in the Source Data Columns table to the appropriate column in the Target Table Columns table.

   The figure shows the data in the last column in the Source Table Columns table being mapped to the last column (PO_DATE_RECEIVED) in the Target Table Columns table. At this point, the previous four columns in the Source Table Columns table have already been mapped to the appropriate columns in the Target Table Columns table.

   
   Description of the illustration sqld_data_import_new.gif
10. Click Next.

    The Finish page appears.

11. Click Finish.

    The data is imported into the table.

If you no longer need a table, then you can delete it using SQL Developer. When you delete a table, the database deletes the data and dependent objects of the table (such as indexes), and removes the table from the data dictionary.

1. In SQL Developer, navigate to the PURCHASE_ORDERS table in the HR schema, following the instructions in "Viewing Tables".
2. Right-click the PURCHASE_ORDERS table and select Table and then Drop.

   The Drop dialog box appears.

3. Select Cascade Constraints and Purge.
4. Click Apply.

   A confirmation message appears.

Indexes are optional schema objects that are associated with tables. You create indexes on tables to improve query performance. Just as the index in a guide helps you to quickly locate specific information, an Oracle Database index provides quick access to table data.

You can create as many indexes on a table as you need. You create each index on one or more columns of a table. For example, in a purchase orders table, if you create an index on the vendor number column, then you can sequentially access the rows of the table in vendor number order, without having to actually sort the rows. Additionally, you can directly access all purchase orders issued to a particular vendor without having to scan the entire table.

After an index is created, it is automatically maintained and used by the database. Changes to the data or structure of a table, such as adding new rows, updating rows, or deleting rows, are automatically incorporated into all relevant indexes. This is transparent to the user.

Some indexes are created implicitly through constraints that are placed on a table. For example, the database automatically creates an index on the columns of a primary key constraint or unique key constraint.

The following topics provide more background information about indexes:

• Indexes and Performance

• Index Attributes

You use SQL Developer to view the indexes in your database.

1. In the Connections navigator in SQL Developer, navigate to the Indexes node for the schema that includes the index you want to view.

   If the index is in your own schema, navigate to the Indexes node in your schema.

   If the index you want to view is in another user's schema, navigate to the Other Users node, expand it, find the name of the schema the index is in, and navigate to the Indexes node.

   Examples of schema names include SYS and HR.

   Note:

   You must have the necessary privileges to view other schemas and the objects in those schemas.

2. Open the Indexes node.

   The names of the indexes in the selected schema appear below the Indexes node.

3. Click the name of the index you want to view.

   A tab with the index name appears in the object pane, with the Columns subtab displayed. You can view the index definition on this tab.

When you create an index using SQL Developer, you specify one or more table columns to be indexed and the type of index to create.

1. In SQL Developer, view the tables in the SH schema, by following the instructions in "Viewing Tables".
2. Right-click the PRODUCTS table, and select Index, and then Create Index.

   The Create Index dialog box appears, with the Definition tab displayed.

3. At the top of the dialog box, confirm that SH appears in the Schema field and that PROD_DESC_IDX appears in the Name field.
4. On the Definition tab:

   • In the Table Schema field, enter SH.

   • In the Table field, select PRODUCTS.

   • In the Index Type field, select Non-Unique.

   • Click the green check mark item in the Expressions section, and in the row that displays, enter PROD_DESC in the Expressions column, and <Not Specified> in the Order column.

     If your index were to consist of multiple columns (a concatenated index), then you would add a second column in the Expressions section to add it to the list of columns in the Index list. Then you would use the up arrow and down arrow icons to arrange the index columns in the order you want.

     If the index includes multiple columns, choose either ASC (ascending) or DESC (descending) in the Order field. The value you choose determines the sort order to be used for the index.

   
   Description of the illustration sqld_create_index_new.gif
5. Click OK to create the index.

If you no longer need an index, then you can delete it using SQL Developer.

1. In SQL Developer, view the tables in the SH schema, by following the instructions in "Viewing Tables".
2. Right-click the PRODUCTS table, and select Index, and then Drop.

   The Drop dialog box appears, with the Prompts tab displayed.

3. In the Drop Index field, select PROD_DESC_IDX.
4. Click Apply.

   A confirmation message appears.

Similar to tables, views can be queried, updated, inserted into, and deleted from, with some restrictions. All operations performed on a view actually affect the base tables of the view. Views can provide an additional level of security by restricting access to a predetermined set of rows and columns of a table. They can also hide data complexity and store complex queries.

Many important views are in the SYS schema. There are two types: static data dictionary views and dynamic performance views. Complete descriptions of the views in the SYS schema are in Oracle Database Reference.

You can use SQL Developer to list the views in a specified schema. You can also display the view definitions.

1. In the Connections navigator in SQL Developer, navigate to the Views node for the schema that includes the view you want to display.

   If the view is in your own schema, navigate to the Views node in your schema.

   If the view you want to display is in another user's schema, navigate to the Other Users node, expand it, find the name of the schema the view is in, and navigate to the Views node.

   Examples of schema names include SYS and HR.

   Note:

   You must have the necessary privileges to view other schemas and the objects in those schemas.

2. Open the Views node.

   The list of views in the schema appears.

3. Click the name of the view that you want to display.

   A tab with the view name appears in the object pane, with the Columns subtab displayed. You can view the view definition on this tab.

1. In the Connections navigator in SQL Developer, navigate to the Views node in the HR schema and find the KING_VIEW, by following the instructions in "Displaying Views".
2. Click the KING_VIEW.

   A tab with the view name appears in the object pane, with the Columns subtab displayed.

3. Click the Data subtab in the object pane.

   The data selected by the view appears.

4. (Optional) You can also test the view by submitting the following SQL statement in SQL*Plus or SQL Developer:

   SELECT * FROM hr.king_view
   

If you no longer need a view, then you can delete it using SQL Developer.

1. In the Connections navigator in SQL Developer, navigate to the Views node in the HR schema and find the KING_VIEW, by following the instructions in "Displaying Views".
2. Right-click the KING_VIEW, and then select Drop.

   The Drop dialog box appears.

3. Click Apply.

   A confirmation message appears.

• PL/SQL packages, procedures, functions, and triggers

• Java source code (Java sources) and compiled Java classes

The actions that you can perform include creating, compiling, creating synonyms for, granting privileges on, and showing dependencies for these code objects. You can also edit and debug PL/SQL code objects using SQL Developer. You access administration pages for these objects by clicking links in the Programs section of the Schema subpage.

Note that creating and managing program code objects is primarily the responsibility of application developers. However, as a DBA you might have to assist in managing these objects. Your most frequent task for program code objects might be to revalidate (compile) them, because they can become invalidated if the schema objects on which they depend change or are deleted.

1. If the Reports navigator does not appear in SQL Developer, choose the Reports option from the View menu to display the Reports navigator.

   The Reports navigator appears.

2. In the Reports navigator, expand the All Reports node, then expand the Data Dictionary Reports node, then expand the All Objects node, and then click Invalid Objects.

   The Select Connection dialog box appears.

3. In the Select Connection dialog box, select the connection to use, or create a new connection.

   If you want the invalid objects report to include information about only the invalid objects in your own schema, use a connection for your own schema.

   If you want the invalid objects report to include information about invalid objects throughout the database, use a connection for a privileged user, such as SYS. In this example, the connection chosen is for the SYS user.

4. Click OK.

   The Enter Bind Values dialog box appears.

5. Click Apply.

   The Invalid Objects tab appears in the object pane. This tab lists the invalid objects in your schema or in the database (depending on the connection you specified in the Select Connection dialog box).

6. When you right-click the row for a particular invalid object on the Invalid Objects tab, the Compile option appears. Select that option to recompile the invalid object.
   
   Description of the illustration sqld_invalid_objs_new.gif

   Remember that it is not always possible to make an object valid by recompiling it. See the Note at the beginning of this section.