3 ways to compare tables data in Oracle

There are three different ways to compare Oracle data in two tables. The first one is rather complicated as it requires being familiar with SQL query writing. The second one requires using the DBMS_COMPARISON package, which doesn't make it much easier. Fortunately, there is a third way to compare data between two tables in Oracle - dbForge Data Compare. This intelligent and user-friendly tool helps find differences between two tables.

This tutorial shows how easy it is to compare tables in Oracle with dbForge Data Compare.

Save time comparing the differences in data between Oracle databases
Reduce system downtime caused by tables data replication errors
Compare 2 tables in Oracle to detect changes
Compare columns and rows in the same table
Find differences between two tables and analyze them
Generate data comparison reports
Generate custom scripts to synchronize databases effortlessly

The first way - SQL Query

One can look at databases as living and dynamic entities since they constantly change and adapt to any situation. That is why it is important to keep an eye on those changes by comparing the data between two tables in Oracle.

If you are an experienced developer, you will be able to adjust the query according to your current needs. As an example, let us compare the tables in the OLYMPIC_GAMES database:

ALTER SESSION SET CURRENT_SCHEMA =OLYMPIC_GAMES;

SELECT S.SPORTS_ID,SOD.SPORTS_ID AS OLD_SPORTS_ID,
       S.STYPE,SOD.STYPE AS OLD_STYPE,
       S.SNAME,SOD.SNAME AS OLD_SNAME,
       S.DISTANCE,SOD.DISTANCE AS OLD_DISTANCE,
       S.TEAM_TYPE, SOD.TEAM_TYPE AS OLD_TEAM_TYPE,
       S.GENDER, SOD.GENDER
        FROM SPORTS S,SPORTS_OLD_DATA SOD
;

dbForge Data Compare for Oracle: Comparing databases with SQL Query

The second way - using DBMS_COMPARISON

Oracle Database 11g has introduced integrated the DBMS_COMPARISON package that can be used for database comparison. As an example of DBMS_COMPARISON, let us use the query that will show how many differences are there in two tables:

SET SERVEROUTPUT ON
DECLARE
  l_scan_info  DBMS_COMPARISON.comparison_type;
  l_result     BOOLEAN;
BEGIN
  l_result := DBMS_COMPARISON.compare (
                comparison_name => 'test_compare_1',
                scan_info       => l_scan_info,
                perform_row_dif => TRUE
              );

  IF NOT l_result THEN
    DBMS_OUTPUT.put_line('Differences found. SPORTS_ID=' || l_scan_info.scan_id);
  ELSE
    DBMS_OUTPUT.put_line('No differences found.');
  END IF;
END;

dbForge Data Compare for Oracle: Comparing databases with DBMS

The third way - using
dbForge Data Compare

This tutorial will take you through the comparison process of the two Oracle tables, showing you a quick and convenient approach to finding differences in data by means of the dbForge tool, Data Compare for Oracle.

See for yourself how much easier it is to work with dbForge Data Compare compared to using SQL queries and DBMS_COMPARISON. dbForge team has carefully catered the tool to give you a lot more options and make your experience better.

dbForge Data Compare for Oracle: Visual data comparison

1. Opening Data Comparison Wizard

Click New Data Comparison on the Start page to launch Data Comparison Wizard. You can also start other common tasks in one click without searching for a required command in the menu.

Tip: To open previous comparison projects, double-click them.

dbForge Data Compare for Oracle: Opening Data Comparison Wizard

2. Selecting database connections

To create a database connection, click the New button in the wizard. The Database Connection Properties window appears. You can use either a direct or an Oracle client connection.

Select the Direct checkbox to connect directly. Specify the host, SID, port, user and password to connect to the Oracle server. Click OK to finish. Now you can select this connection in the drop-down list of the Connection field in the wizard.

dbForge Data Compare for Oracle: Selecting database connections

3. Selecting Source and Target for comparison

Source is the database whose contents are inserted into Target during synchronization. Target is the database whose contents are compared and synced with Source. Click the Connection fields and select required connections for Source and Target from the drop-down list.

To swap Source and Target, click the Swap button. To set up custom mapping, click the Next button. To start the comparison, click Compare.

dbForge Data Compare for Oracle: Selecting Source and Target for comparison

4. Selecting comparison options

You can customize comparison behavior, e.g. ignore certain objects or properties. Go to the Options wizard page and select required checkboxes.

Tip: Save the comparison settings as a command line arguments file by clicking the link at the top. You can use this file later for auto-comparison and synchronization via command line.

Tip: To save the current selection of options as your defaults, click Save As My Defaults.

dbForge Data Compare for Oracle: Selecting comparison options

5. Mapping custom objects

dbForge Data Compare offers both automatic and manual (custom) mapping. Use custom mapping to map tables, columns, and views that cannot be mapped automatically.

Tip: To discard custom mapping and restore to the default one, click the Reset button.

dbForge Data Compare for Oracle: Mapping custom objects

Mapping tables and views

You can map tables and views with different data structures manually in several clicks.

Click the Map Objects button. Select Source and Target objects in the open window, then click OK. The selected pair of objects will appear in the grid on the Mapping wizard page and be compared.

Tip: To map multiple pairs of Source and Target objects without closing and opening the window multiple times, click the Map button after the first selected pair of objects. When you manually select all required objects, click OK to see them in the grid on the Mapping wizard page.

dbForge Data Compare for Oracle: Mapping tables and views

Mapping columns

See how many columns of each object can be compared in the Comparison field of the grid.

Click the ellipsis button for the required object. A window will open and show all the columns of the selected object, as well as additional information.

Automatically mapped columns are marked with a green icon. Incompatible columns are marked with a red icon. Select required Target columns from the drop-down list and click OK.

dbForge Data Compare for Oracle: Mapping columns

Mapping custom queries

Click the Custom Query button and write required queries for Source and Target in the corresponding SQL fields. You can select to map the query result with a table. Click Map to perform mapping and OK to close the window.

Tip: To validate queries, click the Validate button or select the Validate item from the context menu in the required SQL field.

dbForge Data Compare for Oracle: Mapping custom queries

6. Analyzing comparison results

See the full info about the compared objects in the data comparison document. The top grid shows all the compared objects. The bottom grid shows the records of the selected objects, automatically grouped by status - Identical, Different, Only in Source, and Only in Target.

Select required objects or separate records of objects to synchronize.

Tip: For better understanding, filter the objects in the top grid, e.g. to see Only in Source objects.

dbForge Data Compare for Oracle: Analyzing comparison results

7. Synchronizing data

Use Data Synchronization Wizard to generate a standards-driven synchronization script and tune up the results according to your needs. Click the Synchronize button to generate the synchronization script immediately.

Tip: To customize synchronization, click Next and proceed to other wizard pages.

dbForge Data Compare for Oracle: Synchronizing data

Customizing synchronization

On the Options page, you can customize synchronization with a variety of options.

To see the synchronization actions and the list of warnings generated during the mapping stage, go to the Summary page.

dbForge Data Compare for Oracle: Synchronization options

Checking warnings

On the Summary page, you can check the synchronization action plan and the list of warnings generated during the mapping stage.

dbForge Data Compare for Oracle: Synchronization plan

Executing the sync script

Now Data Synchronization Wizard provides you with the synchronization script. Click the Execute button on the toolbar to execute it.

dbForge Data Compare for Oracle: Synchronization script

8. SQL script management

Use the SQL editor to view and edit synchronization scripts, create queries and execute them against the selected database.

Click the New SQL button to open the SQL editor.

Tip: When the query is executed, see the query results in the Data window. You will find rich data management options in the right-click menu.

dbForge Data Compare for Oracle: SQL script management

Conclusion

This is how you can compare tables in Oracle quickly and easily, find any differences at a glance, and take corresponding actions. Go through individual records or group them together. Create SQL scripts to deploy data modifications between your environments.

dbForge Data Compare for Oracle

Get your free 30-day trial of dbForge Data Compare to evaluate its capabilities under a sleek user interface.

Get free trial