As Published In
Oracle Magazine
September/October 2008

TECHNOLOGY: SQL Developer


Document, Monitor, and Manage

By Sue Harper

Take advantage of new features in Oracle SQL Developer 1.5.

Oracle SQL Developer 1.5 introduces a vast array of new functionality. Even features that might seem minor at first glance can aid your day-to-day work significantly. This column explores Oracle SQL Developer 1.5 features that help you document and manage your Oracle Database objects and schemas. You’ll learn how to

 

  • Easily share object details with other participants in your projects
  • Use instant reports to get details about your database sessions and tablespaces, terminate sessions, and shut down the database
  • Take advantage of copy and export utilities to make working with multiple schemas easier


Getting Started

This column’s examples require Oracle SQL Developer 1.5.1. If you have the production release of Oracle SQL Developer 1.5 installed, open it and use Help -> Check For Updates to update in place to Oracle SQL Developer 1.5.1. Otherwise, download the full Oracle SQL Developer 1.5.1 install from OTN and unzip it into a new empty folder. (Do not unzip it into an existing Oracle SQL Developer folder.)

You can migrate your database connections and preferences from Oracle SQL Developer 1.2.x or 1.5 to Oracle SQL Developer 1.5.1 during installation. If you do not want to migrate preferences, you can still import database connections from any earlier release after installation. To import connections

1. Start the earlier release of Oracle SQL Developer
2. Select Connections in the Connections Navigator
3. Right-click and select Export Connections
4. Browse to a suitable location, enter a filename such as connections.xml , and click Save
5. Shut down the previous release, and start Oracle SQL Developer 1.5.1
6. Select Connections in the Connections Navigator
7. Right-click and select Import Connections
8. Browse to the file you just saved, click Open , and click OK

For this column, you also need access to the HR and OE sample schemas in an Oracle Database instance.

Generating Database Documentation

You can generate documentation about your schema in HTML format for your own review or to share with others. Follow these steps to generate and view schema documentation:

1. If you don’t already have a connection to the HR schema, create one and name it HR_ORCL. (For detailed information on creating a connection, see “Creating a Database Connection” in “Next Steps” and also see “Making Database Connections,” in the May/June 2008 issue of Oracle Magazine.)
2. Right-click the HR_ORCL connection, and select Generate DB Doc.
3. Select or create a suitable location for the generated files, such as \working. If you plan to share the generated files with others, use a shared file server location. (You can also move or copy the generated files.)

An index.html file should open automatically in your default browser. If it doesn’t, navigate in a browser to the \working\index.html file and open it.

To see the details for any database object in the HTML documentation, select the object type in the schema panel at the top left. A list of all objects of that type then appears in a panel below the schema panel. Select an object there to display its details in the central panel. For example, to display details of the EMPLOYEES table, select Tables from the top panel and EMPLOYEES from the lower panel (see Figure 1).

 

figure 1
Figure 1: Generated documentation for the HR schema


Monitoring and Managing with Reports

Oracle SQL Developer’s View -> Reports function lets you select several standard system reports to view details about your database and schemas. Two reports are also available from the Tools menu and Connections Navigator, respectively, for easy access. Both are suitable for privileged users such as SYSTEM or SYS. (You can also run them as a nonprivileged user, such as HR, with some limitations.)

The Sessions report shows details about the current active and inactive sessions. Follow these steps to display the Sessions report:

1. Create a new connection called SYSTEM_ORCL for the SYSTEM user.
2. Select Tools -> Monitor Sessions.
3. Select SYSTEM_ORCL in the Select Connection dialog box and click OK to open the report.

Privileged users can terminate a session from the Sessions report—for example, when a user’s session has not closed cleanly. (The default HR schema cannot terminate sessions.) If the HR connection is still active from the preceding exercise, for example, select the HR session in the Sessions report you just generated, right-click, select Kill Session , and click Apply.

The other report available at this level is the Manage Database report. Right-click the SYSTEM_ORCL connection in the Connections Navigator, and select Manage Database . The report displays details about the tablespaces in your database. If you run this report from a SYS connection, you can shut down and restart the database from within Oracle SQL Developer. (The Shutdown button is not available to nonprivileged users.)

Copying Objects to a New Schema

Working with multiple schemas often involves copying objects and their data from one schema to another. There are many ways to do this in Oracle SQL Developer, including the following:

 

  • Copy objects step by step, by first creating and executing the data definition language (DDL) to create the table and then running a series of insert statements to insert the new data.
  • Use Table -> Copy to create a copy of a table with its data.
  • Use Tools -> Database Copy to create a copy of a database.
  • Use the Database Export wizard to create the DDL and insert statements for multiple tables and other database objects.


In the following exercise, you’ll use each of the four methods to compare their strengths and limitations:

1. Create a new database connection called OE_ORCL for the OE schema.
2. Select the OE_ORCL connection, and expand the Tables node.
3. Right-click the CATEGORIES table, and select Export DDL -> Save to Worksheet (see Figure 2).

 

figure 2
Figure 2: Export DDL to SQL worksheet


The SQL that appears in the SQL worksheet includes the OE schema name, so it is not appropriate for running in a new schema. (The syntax for this SQL is gathered using the DBMS_METADATA package and is driven by a set of preferences.) To regenerate the SQL without the OE schema name, follow these steps:

1. Select Tools -> Preferences , expand the Database node in the tree, and select ObjectViewer Parameters.
2. Uncheck the Show Storage and Show Schema options, and check Show Constraints as Alter.
3. Click OK.
4. Clear the SQL Worksheet, and repeat the earlier steps: Right-click the CATEGORIES table, and select Export DDL -> Save to Worksheet . Note that the SQL code in the SQL worksheet no longer includes the OE prefix.

Now copy the CATEGORIES table and its data to the HR_ORCL schema, by following these steps:

1. Select the HR_ORCL connection in the SQL worksheet Connections list and click Run Script (or press F5) to execute the displayed DDL in the HR schema.
2. Expand the HR_ORCL node, and review the new CATEGORIES table. Note that it contains no data.
3. Right-click the OE_ORCL connection’s CATEGORIES table in Connections Navigator, and select Export Data -> Insert.
4. In the Export Data dialog box, send the output to the clipboard and click Apply.
5. Open a new SQL worksheet for the HR_ORCL user, and type Ctrl-V to paste the clipboard contents.
6. Click Run Script (or type F5) to execute the SQL.

7. Click the Commit button (or type F11), and review the CATEGORIES table data in the HR_ORCL connection.

The preceding steps copy only a single table and its data. A quicker alternative for copying a single object and its data is the Copy context-menu command:

1. Right-click the OE_ORCL connection’s INVENTORIES table, and select Table -> Copy.
2. In the Copy dialog box, select HR as the new table owner, enter INVENTORIES for New Table Name , and check Include Data.
3. Click Apply.
4. Refresh the HR_ORCL connection’s Tables node to see the new INVENTORIES table.

To create the DDL code for multiple tables and their data, use the Database Export wizard. To copy a group of tables from the OE schema to the HR schema, follow these steps:

1. Select Tools -> Database Export . Browse to a suitable file location, and accept the default filename, export.sql . (You can establish a default path for this file by selecting Tools -> Preferences , selecting the Database node in the tree, and setting the Select default path to store export in preference.)
2. In the Export Wizard, select the OE_ORCL connection and ensure that the Storage and Show Schema options are unchecked. Check Include Drop Statement and Automatically Include Dependent Objects . Click Next.
3. On the Types to Export screen, uncheck Toggle All and check Tables and Data. Click Next.
4. On the Specify Objects screen, under the OE list, click Go to populate the list of tables to select. Shuttle just the OE.ORDER_ITEMS table to the right-hand panel. Click Next.
5. On the Specify Data screen, click Go to populate the list of tables. Shuttle just the OE.ORDER_ITEMS table to the right-hand panel, and select it to highlight the table. In the empty box below, enter order_id < 2355 and click Apply Filter (see Figure 3). Click Next and then Finish.

 

figure 3
Figure 3: Database export


Note that the export.sql script that now appears in the SQL worksheet includes additional tables. This is because the constraints, not created here, are dependent on these tables. Also note the restricted set of data returned.

Select HR_ORCL from the SQL Worksheet’s connections list, and run the script. Commit the changes, and then refresh the HR_ORCL node to see the tables the Database Export wizard has copied from the OE_ORCL schema.

Finally, using Database Copy in Oracle SQL Developer is a highly efficient way to copy objects to another schema. Instead of producing a script of insert statements, Database Copy inserts data into the new table in the background. Database Copy also copies BLOBs and CLOBs to the new schema.

Next Steps



 READ more Oracle SQL Developer

LEARN more about
Oracle SQL Developer
Creating a Database Connection

 DISCUSS Oracle SQL Developer

 DOWNLOAD script to build OE and HR schemas



To complete this comparative exercise, use Database Copy to copy a set of objects to the HR schema:

1. Select Tools -> Database Copy . Select OE_ORCL for Source Connection and HR_ORCL for Destination Connection . Note that the only choices you have here are to create new objects, truncate the data in existing objects (to be replaced with the new data), or drop (and replace) the objects.
2. Select Truncate Objects , and click Next . The Copy Summary indicates that all tables will be truncated. This is not what you want, so click Back , select Create Objects , and click Next . This will ensure that existing objects are not dropped or truncated.
3. Click Finish.
4. Review the tables and data created in the HR_ORCL connection.

Database Export and Database Copy differ in two significant ways. Database Export lets you select the object types to export and, within each category, restrict the individual object instances. Also, with Database Export, you can elect to generate GRANT statements, include DROP statements, and create INSERT statements, thus gaining the capability to produce a script that you can rerun at will for new or existing schemas.

Conclusion

This column has explored a selection of the features introduced in Oracle SQL Developer 1.5. You can enhance your productivity with its new ways to view and share database details, monitor and manage sessions, and copy database objects across schemas.

 



Sue Harper
is an Oracle senior principal product manager based in London. Her technical blog, sueharper.blogspot.com, addresses Oracle SQL Developer features.

Send us your comments