Select the system SQL Worksheet tab.

If the SQL Worksheet is closed, right-click the system connection in the navigator and select Open SQL Worksheet.

In SQL Worksheet type the following command to create a proxy user and click F5 or the Run Script button.

CREATE USER proxy_user IDENTIFIED BY pass;

Alter the proxy_user so that you can connect to the user using the username and password for the user HR:

ALTER USER proxy_user GRANT CONNECT THROUGH HR AUTHENTICATED USING PASSWORD;

Click F5 or the Run Script button to execute the statement.

Grant create session privilege to the user proxy_user.

GRANT CREATE SESSION to proxy_user;

Click F5 or the Run Script button to execute the statement.

In the Connection navigator, click New Connection button.

In the New Connection window enter the following details:

Connection Name: Proxy_conn
Username: hr
Password: hr
Hostname: localhost
Port: 1521
SID: orcl

Check Save Password

Even though you have called the Connection Proxy_conn, you are providing the HR username and password.

Click Connect and expand the new Proxy_conn connection. Expand the Tables node.

Right-click the Proxy_conn and select Properties to invoke the Connection Dialog.

Check the Proxy Connection checkbox , Oracle Proxy Connection window appears. In Oracle Proxy Connection window enter the following details:

Username: proxy_user
Password: pass
Click OK.

In the Connection window, click Test and Connect.

Expand Connections > Proxy_conn >Tables. Note that although proxy_conn connects using the credentials of the user hr, it actually connects to the schema proxy_user, and so does not see the tables of hr schema under proxy_conn.

Open SQL Developer. Click Tools > Database Export

Click Browse to select the location and name of the export file.

Browse the files directory, accept the default file name, export.sql. Click Save.

In the Connection drop down list, select the connection from which you want to export objects . In this example you choose the connection hr.

Under the DDL options the Terminator and Pretty Print DDL options are selected by default. Review the rest of the options available, leaving them unchecked. Click Next.

The Types to Export screen includes the Object Types that you want to be exported from the connection hr. By default all the object types are selected. Click Toggle All check box to deselect all the object types.

Select only the object type Tables and then click Next.

Note: You must select at least one object type.

Click the Go button to search all objects that meet the selection criteria that you specified in step 6.

In this example clicking Go lists all tables owned by hr.

Click the tables EMPLOYEES and DEPARTMENTS and then use the arrow keys to move them to the list box on the right hand side of the window. Click Next.

You can review the information that will be used to create the output file, which will contain statements to export database objects and data according to your specifications. To create the output file, click Finish.

The file is also displayed in a SQL Worksheet window, where you can edit it and run it as a script.

In this example you run the script export.sql to create the tables EMPLOYEES and DEPARTMENTS in the Test schema. Click Run Script.

The script is not yet associated with any user. Select Test from the drop list and click OK.

In the Connections navigator, select Test and click Refresh to see the newly added tables EMPLOYEES and DEPARTMENTS.

Click Tools > Database Copy

In the Source Connection drop-down list select the database connection for the source schema. Select hr as source.

In the Destination Connection drop-down list, select the database connection for the source schema. Select Test as the destination.

Select Create Objects. Click Next.

Notice in the Copy Summary that all the tables in HR will be created in Test, even the EMPLOYEES and DEPARTMENTS.

Review the alternatives and the differences in the Copy Summary for each by clicking the Back button to return to the first panel and select each of the other two options in turn, clicking Next to review the Copy Summary for each.

For example, notice how the Truncate optin assumes all tables exist and does not create new tables.

Once you have reviewed the differences between the options, select Create Objects a final time, click Next and Finish.

The result of the comparison is displayed in a SchemaCopy log file in the SQL Developer window.

Review the full results log. Do you expect the errors you find?

Note: DEPARTMENTS and EMPLOYEES were already created.

Select the Test connection and click Refresh to see the newly copied objects.

Select File -> Open.

Open the file PROC_emp_cursor.sql.

Click the Run Script to execute PROC_emp_cursor.sql.

Select hr in the Select Connection window. Click OK.

Click View > Extended Search.

In the Extended Search window enter following details:

Connection: hr
Name: c_emp_cursor
Type: All
Usage: All

Click Lookup

All occurrences of c_emp_cursor are displayed.

Click each occurrence of c_emp_cursor to view and edit it in PL/SQL Editor.

You can also use wild cards while searching database objects. Searching for %emp_cursor displays all occurrences of the cursor c_emp_cursor and also the procedure Proc_emp_cursor.

You can apply a filter on the search results.

• Filter on the basis of Usage. Select Declaration for Usage and click Lookup.

• Filter on the basis of Type. Change Usage back to All. Enter %dept% for Name and select View for Type and click Lookup.

Select the menu Tools > Preferences.

Click Database > Worksheet Parameters.

Change the Drag and Drop effects to Insert. Click OK.

In Connections navigator, right-click HR and select Open SQL Worksheet.

Expand the HR connection Expand Tables. Select and drag the JOBS table to the SQL Worksheet area.

You will find an Insert statement for the table, JOBS in the SQL Worksheet area.

Select the menu Tools > Preferences.

Click Database > SQL Editor Code Templates.

Click the Add Template button to add a new template.

In the text box labeled Id type excep. This is the text that you want to be replaced by the template.

To enter the template code, click the ellipses.

In the Template Editor window, type in the text you want to replace the id with.

Use the following code:

EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE ('The query did not return a result set');

Click Close.

Click OK to close the Preferences window.

Expand the HR Procedures node in the Connections navigator and select PROC_EMP_CURSOR, the procedure you created in the Extended Search exercise.

Double-click to open the procedure in the PL/SQL Editor. You may need to click the second PROC_EMP_CUROSR tab to see the editor.

Type excep in SQL Worksheet, in a new line below END LOOP;

Hit the hot keys Ctrl+Shift+t. The template code replaces the id, excep.

Save the changes. Click File > Save.