Have you experienced breaking an app because you forgot to add or update new database objects? That’s one hell of a disruption to your users. Doing some Oracle schema compare could have avoided the problem.
Well, that kind of mistake happened to me in my early years. And it’s a lesson I will never forget.
Forgetting a simple new feature like adding a new table column can cause outages. I vividly remember the stress I got and the annoyance of users. If you had the same experience, I’m with you. Though it was easy to fix, that day was not so cool.
But that’s history.
I moved forward and made sure it didn’t happen again. You might decide the same as well.
We can’t rely on our memory to remember all the schema updates we did. If you’re in a team, every member relying on their memory is even riskier. In addition, relying on memory won’t serve you well when working in a regulated industry. It dictates documented evidence of the changes you made. Or the auditors will come after you.
So, that calls for using a reliable but easy-to-use tool to do Oracle schema compare.
That’s what we are here for. We will examine hand-picked Oracle tools that are good for comparing database structures and even the data in them.
Here are the tools that are in store for you:
- Oracle SQL Developer by Oracle,
- dbForge Studio for Oracle by Devart,
- dbForge Schema Compare for Oracle by Devart,
- dbForge Compare Bundle for Oracle, again by Devart, and
- Using plain ‘ol PL/SQL scripts
But What’s the Big Deal?
Doing Oracle schema compare is not like playing Spot the Difference. That fun classic game gives you two almost identical images and you need to spot visual discrepancies between them.
Losing to that game has low risk. But with database version updates, there is a lot at stake. When one or more objects are not migrated to production, outages result. It will give you and your users stress. Users also experience a loss of productivity. But it gives you more work as you need to fix it fast. Worst, if it comes at a very bad time, you can get a bad reputation and lose clients.
So, when do you need to do some Oracle schema comparison? The following are some of the reasons:
Detecting Changes
Sometimes you just want to know what changes happened from one version to the next. So, you compare two Oracle schemas and see what tables, procedures, and what-not changed. It’s like a kid checking his box of cookies if it has the same number as his brother’s box.
Ensuring Consistency
During a new schema migration, you need to make sure that development and production have the same structure – the same tables, indexes, functions, and everything. If not, you need to sync them. Sometimes you also need some portion of the data to sync. Or else, production will go haywire. It’s like a parent making sure each box of cookies has the same number for their two kids – fair and square.
Troubleshooting
During testing and development, you want to know what causes the runtime errors. While debugging and trace tools will pinpoint a missing column. A sort of “second opinion” is to do an Oracle schema comparison. It’s like a parent finding out who cheated so that a cookie in a box has a bite and stops the little ones fighting for it.
Comparing schemas is not about being nitpicky. You just need to be sure everything is in order and will work as expected.
The next section will give you the tools to fill the above needs.
Using Oracle SQL Developer
Oracle SQL Developer is a free integrated development environment (IDE) for development and management of Oracle databases on-premises and in the cloud. It offers many features for developers and administrators alike. This tool is fine for a one-man database hero or a team of database elites.
One of the useful features is the Database Diff tool. And it’s for Oracle schema comparison.
It uses a graphical user interface (GUI) and a wizard-type workflow to set up the Oracle schema comparison.
What you need is simple: Two (2) Oracle database connections. Just like the one below:
If you only have one (1), the Database Diff tool will be disabled from the menu.
Steps to Compare Oracle Schemas
Here’s how to use it:
First, click Tools from the main menu and select Database Diff.
The wizard starts by letting you choose the Source and the Target Connections. For example, your source connection is the TEST_ENVIRONMENT and your target is the PROD_ORACLE_CLOUD. When synced, PROD_ORACLE_CLOUD will be updated.
Then, click Next and select the standard database objects to compare. The default is all objects. If you only need to compare Tables, unselect the rest.
Then, click Next and select from specific object names to include.
Then, click Next to see the summary.
Finally, click Finish. A progress window will show up so you know what’s going on, like this one:
Then, a report will be available to you. Click the one you want to see the changes.
Pros
- Comprehensive comparison of all standard Oracle database objects.
- Graphical user interface and a wizard-type workflow.
- Side-by-side comparison of differences with color highlights.
- Includes other useful database features aside from Database Diff.
- Free to download and use.
Cons
- Does not allow changing comparison options after the Diff Report is available. You have to start over again.
- Paste the output script to an SQL worksheet to run it.
- If you need to do the same comparison later, you have to start the wizard from step 1 again.
Using dbForge Studio for Oracle
dbForge Studio for Oracle is a cool, comprehensive IDE provided to us by Devart. It’s the IDE for all things Oracle with cool features including Query Profiler, next-level code completion, and yes, Schema Compare. It’s a tool of choice for Oracle database design and modeling, PL/SQL development, administration, and analysis.
This tool is gold whether your job role has a lot of slashes in it (Designer/Developer/DBA/etc.) or you’re a member of your company’s upper-class database team.
But whatever project that involves Oracle databases, there will always be schema comparisons to be done.
That’s why we’re showing you the easy steps to do it in dbForge Studio for Oracle.
Steps to Compare Oracle Schemas
First, click Comparison from the top-level menu and select New Schema Comparison.
Then, choose the Source and Target connections. You can use two (2) connections for the comparison or use the same connection for the source and target. If you decide to use the same connection, you need to compare two different schemas to make comparisons that make sense. Below is an example of this.
Then, click Next and change the options you want like Ignore indexes present only in Target. Or just click Next. Below is a sample of the options you can change.
If you click Next, you will see the database objects you can compare. The default is all. But you can choose the only ones you need to compare. See below.
Then, you can click Compare to start the comparison. If the database structure is large and you choose to compare all objects, you will see a status window like the one below.
Otherwise, you will see the comparison report in a snap, like the one below:
At this point, you can click Synchronize to create the script to update the Target. Or click Edit Comparison if you need to change the options again.
Synchronizing Schemas
If you choose to synchronize, another wizard will open to give you more options for the final script. Below is the start of the Synchronization wizard.
You can click Next to change further options.
Then, click Next to add scripts to run before and after the final sync script (optional).
Clicking Next will let you see a summary of the Action Plan. See a sample below.
Finally, click Synchronize to create the script. A new SQL window will appear to let you review and edit the script.
Below is a sample script.
From here, you can run the script.
If you need a report of the comparisons in a file format like HTML, Excel, or XML, you can do so. Click Comparison ->Generate Comparison Report. Then, you will see a window to let you choose the file format like the one below:
If you don’t have anything else to change in the options, click Generate.
Automating Comparisons and Synchronizations
Now, what if you need to do the same comparisons between two databases again and again?
You can automate the entire comparison and synchronization you made in dbForge Studio for Oracle.
If we’re going to use the same example we did earlier, we can do this by clicking Edit Comparison. Then, Save the options used in the comparison for Command Line usage. Here’s an example:
There are 4 steps as shown above. For the third step, you may need to add the source and target passwords for the accounts you used, as in my case. You may also want to compare only and not synchronize so you can uncheck the Synchronization option. The resulting file is a batch file (.bat) you can run using Windows File Explorer. See the illustration below when I saved the comparison:
Then, using Windows File Explorer, double-click the batch file and let it compare and synchronize. A Terminal window will appear similar to the one below:
Finally, you can use Windows Task Scheduler to run the batch file to your desired schedule and frequency, if you wish. Below is an example that runs the comparison every 12:00 PM:
Pros
- Comprehensive comparison of all standard Oracle database objects.
- Graphical user interface and a wizard-type workflow to compare schemas.
- Comprehensive options to compare and sync.
- A side-by-side comparison with color highlights.
- Allow saving schema compare options to re-run the same comparison and save time.
- Automate schema changes through a command-line interface.
- Generate comparison reports in HTML, Excel, and XML file formats.
- Logs success, warnings, and errors in the Output Window.
- Automatically opens the final script to an SQL window – ready to run or edit further.
- Includes other cool features beyond Oracle schema compare.
Cons
- Heavy if you only need to compare schemas.
- Free Express edition does not include Schema Compare.
Using dbForge Schema Compare
Devart offers a lightweight version if you only want to compare schemas and nothing else. Meet the dbForge Schema Compare.
The steps and user interface are almost the same except that you can start the process by clicking the New Schema Comparison from the toolbar.
Pros
- Lightweight as it only includes schema comparison.
- Comprehensive comparison of all standard Oracle database objects.
- Graphical user interface and a wizard-type workflow to compare schemas.
- Comprehensive options to compare and sync.
- Allow saving schema compare options to re-run the same comparison and save time.
- Automatically opens the final script to an SQL window – ready to run or edit further.
Cons
- You need another tool for other database tasks.
Using dbForge Compare Bundle
If you need a comprehensive compare tool for both schema and data, the dbForge Compare Bundle is for you.
The bundle includes the dbForge Schema Compare and dbForge Data Compare.
Pros
- Also lightweight compared to dbForge Studio for Oracle.
- The same benefits offered by both dbForge compare tools.
- Up to 31% savings on bundle purchases compared to buying dbForge Data Compare and dbForge Schema Compare separately.
Cons
- You need another tool for database tasks outside of schema and data comparisons.
Using Plain ‘Ol PL/SQL Scripts
You can use PL/SQL to compare database structures. In other words, you will code to create a script that will do the comparison. Scripting can give you the maximum flexibility to compare schemas.
One way to do it is using the DBMS_METADATA.GET_DDL function.
The above script uses the same scenarios and tables from the previous examples. Involved here are 2 CUSTOMER tables from the same database but with different schemas (ADMIN and DBO). The GET_DDL function returns the structure of the 2 tables into CLOB variables. Then, it compares them. Because there are differences between the 2 tables, the logic passes the ELSE block and displays both structures.
See the output below in dbForge Studio for Oracle:
The GET_DDL function is so simple as it gets the structure of one table only. You can get all tables at once using USER_ALL_TABLES.
While GET_DDL works on the same database instance, it doesn’t mean you can’t use them across different servers. One technique is to use a database link to connect to the remote server and then run GET_DDL.
The above only tells you if the two tables have different structures or not. You need to code further if you want to get specific differences. Note that you can also use GET_DDL for other database object types like materialized views and more.
How About DBMS_COMPARISON
Oracle’s DBMS_COMPARISON package can also perform comparisons between two database objects. Can you use this for schema comparisons?
The first step to using this package is to create a comparison using the CREATE_COMPARISON procedure.
Let’s try comparing the same database objects from earlier.
Running the above gave me an error. See below:
The table shape in the error message tells us that the structure of the two tables is not the same. That’s correct because using different methods and tools earlier tells us the same.
DBMS_COMPARISON is for comparing data within two database objects. The error above tells us we can’t proceed with the comparison of the data. But we proved one thing: The two tables have different table structures. At this point, we have to look for specific differences for ourselves either manually or using other methods.
Using DBMS_COMPARISON to get schema differences is using the wrong tool for the job. It’s like using a hammer to cut wood into two. Yes, the hammer can cut it eventually. But we know there’s a better tool for that.
Pros and Cons in Using PL/SQL Script to Compare Schemas
Pros
- Maximum flexibility in ways to compare schemas.
- Use your preferred tool to create and run PL/SQL script.
Cons
- Needs expertise in PL/SQL coding.
- There are more productive ways to compare schemas using GUI tools like the ones discussed earlier.
Conclusion
We discussed 5 ways to do an Oracle schema compare. Various tools like Oracle SQL Developer and dbForge Studio for Oracle will give you faster results.
The main point to remember is why you’re comparing. You want to avoid outages in your target server when upgrading your database structure. So, development, test, and production environments should be consistent. You can still troubleshoot, though. But that is too late.
So, take your pick from the tools provided here. All of them will give you amazing comparisons to achieve consistent environments.
This content is brought to you by the FingerLakes1.com Team. Support our mission by visiting www.patreon.com/fl1 or learn how you send us your local content here.