TECHNOLOGY: SQL DEVELOPER
Make the Easy MoveBy Jeff Smith
Quickly copy, compare, and synchronize databases with Oracle SQL Developer.
Several methods are available for moving database objects and data from one location to another. DBAs and developers have had access to Oracle Database’s SQL*Loader feature, IMP/EXP, Oracle Data Pump, and Create Table As SELECT (CTAS) over DB_LINKs for many years. However, many of these technologies do not lend themselves easily to ad hoc tasks, and frequently database users lack the database privileges and resources to use them effectively.
With this in mind, Oracle SQL Developer 3.1 introduces new and improved wizards for copying database objects and their data, performing object definition comparisons, and generating scripts to synchronize environments. These features are supported across all releases of Oracle Database, with no additional license packs required. Oracle SQL Developer also supports Oracle Database, Express Edition, which is free to develop, deploy, and distribute.
This article will teach you how to copy selected objects and data from the HR schema to a new schema. After making a few changes to the new objects, you’ll also see how to compare the original schema objects with the new ones. The article steps you through the process of running the Oracle SQL Developer Database Copy and Database Diff wizards to copy and compare database objects.
To step through the example, you need to download and install Oracle SQL Developer 3.1, available as a free download from Oracle Technology Network. You also need access to an Oracle Database instance with the HR sample schema installed.
With Oracle SQL Developer 3.1 installed and launched and a connection to the HR sample schema created, create a new Oracle schema that will contain the copied database objects and data. Connect as SYSTEM or as another user with CREATE USER privileges to create a new schema; then run the following script in in the SQL Worksheet feature of Oracle SQL Developer (Tools -> SQL Worksheet and press F5 to execute the script) to create the new HR_COPY schema. Replace the password, USERS, and TEMP with the preferred password and tablespaces for objects and temporary space.
CREATE USER HR_COPY IDENTIFIED BY password DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP; GRANT CONNECT, RESOURCE to HR_COPY;
The “user HR_COPY created” report on the Script Output tab indicates that the CREATE USER statement was successful.
Next, right-click Connections on the Connections Navigator tab, select New Connection, and enter information in the New/Select Database Connection dialog box to create a connection to the new HR_COPY schema. The HR_COPY connection will be used in the next step.
Using the Database Copy Wizard
Select Tools -> Database Copy to launch the Database Copy wizard. In the Source/Destination wizard step, shown in Figure 1, choose a connection to the HR schema for Source Connection and the connection defined above—to HR_COPY—for Destination Connection. The rest of this article uses Source and Destination to reference the HR and HR_COPY schemas, respectively.
Leave the defaults under Copy Options to copy object structures and their data. Click Next to proceed to the next step.
In the Object Types wizard step, select the types of objects you want to copy. You will be copying the entire Source schema (HR), so leave all the Object Types options selected. Click Next to proceed to the next step.
In the Specify Objects wizard step, specify the database objects to be copied. Because you are logged in as a user other than HR (the source), you will need to use the object selector to specify the tables and views to be copied. Click More, choose HR from the Schema menu and TABLE from the Type menu, and click Lookup.
Click the blue double right arrow (>>) button to move all objects from the available-objects box to the selected-objects box.
Now choose View from the Type menu, click Lookup, and click the blue double right arrow (>>) button to move all objects from the available-objects box to the selected-objects box. Click Next to proceed to the next step.
In the Specify Data wizard step, you now have the option to copy only a subset of the table data. For this exercise, you will copy only employees whose salary is less than 10,000.
In the bottom panel of the Specify Data wizard step, click in the Object Where cell for the HR.EMPLOYEES row and click the Edit button in the cell. Now supply the WHERE clause to limit the data being copied from the Employees table. Type the following into the Object Where input field:
SALARY < 10000
Click Go to preview the data to be copied, and then click OK. Click Next to proceed to the next step.
In the Copy Summary wizard step, before clicking Finish, first expand the tree items to confirm your selections, as shown in Figure 2. You should see the proper source and destination connections, the tables selected previously in the Specify Objects wizard step, and the salary filter applied for copying the Employees table data. After you have confirmed your selections, click Finish.
A Copying dialog box opens and stays open until the copy process is complete. Once the copy task has finished, a log of the operation will open in a new SQL Worksheet. Peruse the log and confirm that there are no critical error messages.
Confirm the Database Copy
Using the connection previously defined for the Destination schema, connect to HR_COPY in Oracle SQL Developer. Expand the tables list in the connection tree, and open the Employees table.
On the Data tab, you will notice that the table has been populated with the original table data from HR. To confirm that only the employees making less than 10,000 have been copied, type salary > 10000 in the Filter field and press Enter. You should see that no rows are returned.
Creating Some Differences
Before performing a Database Diff, create some differences between the Destination and Source schemas. Create two changes in the Destination (HR_COPY) schema:
Connected as HR_COPY, run the following script in the SQL Worksheet and press F5 to execute the script to create the changes in the HR_COPY schema:
alter table hr_copy.employees modify (salary number(9,3) DEFAULT 1000); Comment On Column hr_copy.jobs.min_ salary is 'Minimum salary for a job title is now 1000 or higher'; Comment On Column hr_copy.jobs.max_ salary is 'Maximum salary for a job title is now 10000 or lower';
After executing the script, you should see the following on the Script Output tab:
table HR_COPY.EMPLOYEES altered. Comment on column hr_copy.jobs.min_ salary 'MINIMUM SALARY FOR A JOB TITLE IS NOW 1000 OR HIGHER' succeeded. Comment on column hr_copy.jobs.max_ salary 'MAXIMUM SALARY FOR A JOB TITLE IS NOW 10000 OR LOWER' succeeded.
You are now ready to compare the Source and Destination schemas.
Using the Database Diff Wizard
Select Tools -> Database Diff to launch the Database Diff wizard—it is very similar to the Database Copy wizard.
In the Source/Destination wizard step, start the database comparison by first selecting the Source and Destination schemas, as shown in Figure 3. Your connection to the HR sample schema is the Source Connection, and the HR_COPY connection is the Destination Connection.
Deselect everything but Comments in the DDL Generation Options section. In the DDL Comparison Options section, select the following options:
In the Types to Diff wizard step, select Tables and Views in the Object Types section. Click Next to proceed to the next step.
In the Specify Objects wizard step, click More to display the Schema and Type menus. Choose HR from the Schema menu and TABLE from the Type menu, click Lookup, and click the blue double right arrow (>>) button to move all objects from the available-objects box to the selected-objects box. Choose View from the Type menu, click Lookup, and click the blue double right arrow (>>) button to move all objects from the available-objects box to the selected-objects box. Click Next to proceed to the next step.
In the DIFF Summary wizard step, expand the tree items to confirm your selections and click Finish.
A Comparing dialog box opens and stays open until the comparison process is complete. Once the comparison task has finished, the Diff Report tab presents the results, as shown in Figure 4.
Interpreting the Diff Report
In the Diff Report, objects that exist in both the Source and Destination schemas and contain differences display a warning icon in the form of a yellow triangle with an exclamation mark. You can see this icon on the Employees and Jobs tables in Figure 4.
Click Employees. The DDL tab displays the DDL script for the different Employees tables side by side. Differences are highlighted for easy identification. Select the Script tab to see the ALTER, CREATE, and DROP statements.
This article has stepped you through the process of using Oracle SQL Developer’s Database Copy and Database Diff features to copy and compare database objects. You’ve seen how the Database Copy and Database Diff wizards enable you to answer a few questions, have objects created and data copied, and then have one schema compared with another and detailed differences logged. You can see how Oracle SQL Developer makes ad hoc object management easily achievable in your development and testing environments without third-party tools or hard-to-obtain database privileges and options.