TECHNOLOGY: SQL Developer
Document, Monitor, and ManageBy 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
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
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.)
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).
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.
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:
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.
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.
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.
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.
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.)
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.
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.
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.
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.