Finding table differences in Oracle
Often there is a need to compare two databases and see the differences. I come across this a lot when releasing a new build into an existing environment - the new code runs on the development database, but the test environment needs a schema upgrade before the code will run.
There are tools that will compare schemas for you, but sometimes I just need to quickly find out what columns are missing from the target schema (this is particularly useful when comparing different development schemas and you want to quickly implement changes - production releases need much better quality control than this).
The sql below provides one solution - showing the columns that are missing from 'owner1' when compared to 'owner2' for tables that match the prefix:
This will ofcourse identify missing tables. It's primative, but its an easy way to see basic structural differences using SQL - without the need for expensive tools. Since it is basic sql, you could include this as part of an environment verification test suite, or include in your application startup procedure - providing you have access to a reference schema for comparison.
There are tools that will compare schemas for you, but sometimes I just need to quickly find out what columns are missing from the target schema (this is particularly useful when comparing different development schemas and you want to quickly implement changes - production releases need much better quality control than this).
The sql below provides one solution - showing the columns that are missing from 'owner1' when compared to 'owner2' for tables that match the prefix:
This will ofcourse identify missing tables. It's primative, but its an easy way to see basic structural differences using SQL - without the need for expensive tools. Since it is basic sql, you could include this as part of an environment verification test suite, or include in your application startup procedure - providing you have access to a reference schema for comparison.