DBDiff for Oracle by DKG Advanced Solutions, Inc.

DKG has introduced a bundle of DBA tools "OraPowerTools" that includes DBDiff for Oracle, DBScripter for Oracle and OraEdit PRO.

Two great features that I found in new version are, Upgrade script and SQL comparison. Using these features you can upgrade your "old" database by running the script generated by DBDiff. SQL comparison facilitates you to compare the results of your SQL statements.

In order to access your databases, you need to have an Oracle client installed on your machine, and appropriate entries in your NET8 (tnsnames.ora or sqlnet.ora).

• Databases comparison: To find out the difference(s) between two Oracle databases, schemas, or any database objects. DBDiff for Oracle is also capable of comparing data in tables or views and code in functions, procedures and packages.

• Upgrade Script: To keep a database consistent through different development phases could be a hazardous job, upgrade scripts generated by DBDiff simplify this job.

• SQL comparison: To find out the difference(s) between result of two SQL statements on same or different databases. By specifying a select statement yourself, you can control which pieces of information will participate in the comparison.

• Cross database difference analysis: Such analysis is especially useful after various back-end upgrades and migrations, after for example some new or existing product is migrated from development to production environment. The result of analysis is used to find missing or incorrectly migrated/upgraded objects as well as help in troubleshooting different database behavior.

• Database(s) change analysis: As DBDiff for Oracle generates reports in a structured way and those reports can be printed or saved on disk for future references. The current reports can be viewed to find changes in one or both databases with the passage of time.

• Database Analysis. Although the basic function of product is to compare two Oracle databases but it can also be used to analyze a database by giving the same database in both parameters. This is useful for viewing / saving a statistical report about your objects.

Databases don't even need to be on the same machine, as long as they are both accessible from the machine running DBDiff for Oracle.

In order to access your databases, you need to have an Oracle client installed on your machine, and appropriate entries in your NET8 (tnsnames.ora or sqlnet.ora). DBDiff for Oracle can access any database that you can setup either using an SID or a Service Name.

After connecting to both of databases you are ready to start the comparison. But this is not all that you can do with DBDiff for Oracle.

If you do not have read rights for any of the objects that you are comparing, DBDiff generates an error.

The product makes good use of resources. It actually stores very little information about each database in memory and the rest of the comparison is done by interactively interrogating the databases. The databases being compared do not need to be inactive; you can still interact with them though it slows the process.

I made a number of comparisons of databases on the same machine and the time taken ranged from 5 sec to 1min depending on the size of databases. I also tried running comparisons over the network, time taken was from 20 sec to 15 min.

To make a comparison:

There is a form comprised of checkboxes, lists and radio buttons to set up the parameters to compare the two Oracle databases. The form gives options to include or exclude the objects in comparison. There are two main classifications, 1) Objects., and 2) Source.

In Objects frame you can select to…

• Compare the privileges
• Compare the sequences
• Compare the table structures
• Compare the table indexes
• Compare the table constraints
• Compare the table data
• Compare the table triggers
• Compare the views structure
• Compare the views data

DBDiff for Oracle only attempts to compare if the tables are similar in name and number of columns. If the data types are different it will still compare them, but with different names or different number of columns it won't as it is assumed that all the rows would be different.

You can tell DBDiff to ignore some columns while comparing.

In Source frame you can select to…

• Compare the functions
• Compare the packages
• Compare the packages body
• Compare the stored procedures
• Compare the type
• Compare the type body

In "General" options you can specify the output on screen or text file, "Save incremental" option gives you the facility to free up resources, it saves the report to text file after every specified number of lines and deletes the output from screen.

The report generated by DBDiff for Oracle is formatted in a user-friendly manner. The different colors of two databases and different objects make it easily readable.

Upgrade Script: You can choose whether you want a comparison report or upgrade script. Selecting upgrade script generates a script that you can run in target database giving you the options to include PCTFREE, PCTUSED, INITRANS, MAXTRANS and storage clauses. However when generating upgrade script you don't have option to compare data.

SQL Comparison: can be made to compare results of SQL statements executing on different databases/objects. It particularly solves the problem of comparing differently named/structured objects and you can specify which piece of data will participate in comparison.

Configuration Files: this feature allows you to save current configuration of connection and comparison in a file with extension "dbo" that can be used for running DBDiff from command prompt and for future automation of comparison as well.

Some tips to get results faster

• Minimize the screen when not looking at it. This will stop DBDiff from dealing with the User Interface and therefore run a lot faster.

• If you are running Windows 98, make sure you have the "Animate windows, menus and lists" option set off, this slows down the output to the control panel. You can set this option off by going to the Windows Control Panel => Display => Effects dialog box and unchecking that option. If you do not want to set this option off, then minimize the DBDiff Control Panel while running a comparison.

• If you have very many records that you expect to be different, especially when doing data comparison, then run the report directly to a file instead of the screen, this will make the run go many times faster.

• If possible, run DBDiff from the machine where at least 1 database is physically located, this will help by lowering the network traffic. If you have both your databases running on the same machine, then running DBDiff on that machine will definitely be a big improvement.

For new features I suggest a web version that should compare two databases over the internet, also ability to create database files to enable "off-line" comparisons, a browser; enabling users to browse their databases would be of great use.

DKG Advanced Solutions Inc.
3830 Valley Center Dr. Suite 705-294
San Diego, CA 92130
USA

Toll-free: (877) 4 DKG USA
Tel: (858) 509 3776
Fax: (858) 777 3451