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.)
Figure 2: Export DDL to SQL worksheet
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:
O 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.
O Use Table -> Copy to create a copy of
a table with its data.
O Use Tools -> Database Copy to create
a copy of a database.
O 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).
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.
