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:

SELECT table_name, column_name, data_type FROM all_tab_cols
WHERE lower(owner)=lower(:owner1)
AND table_name LIKE :prefix
AND column_name NOT LIKE 'SYS%'
AND table_name||'-'||column_name NOT IN
(SELECT table_name||'-'||column_name FROM all_tab_cols
WHERE lower(owner)=lower(:owner2)
)
ORDER BY table_name, column_name;
view raw gistfile1.sql hosted with ❤ by GitHub


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.

Popular posts from this blog

AspectJWeaver with JDK 7 - error - only supported at Java 5 compliance level or above

I came across this problem with an older project - when I switched from jdk6 to jdk7, when the application started I encountered: nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'org.springframework.transaction.annotation.AnnotationTransactionAttributeSource#0': Initialization of bean failed; nested exception is java.lang.IllegalArgumentException:   error annotation type patterns are only supported at Java 5 compliance level or above I didn't want a purely maven solution because during development I run the app in Tomcat via the IDE (Intellij IDEA). In the end I just upgraded my aspectjweaver dependency to 1.7.2 (it was 1.5.4) <dependency>    <groupId>org.aspectj</groupId>    <artifactId>aspectjweaver</artifactId>    <version>1.7.2</version> </dependency> Everything seems okay now.
Read more

JUnit parameterized test with Spring autowiring AND transactions

I've been writing JUnit Parameterized tests (a nice intro here - also see Theories ) to do some data driven API testing. Now, when introducing Spring into the mix there are a couple of extra things to do . I came unstuck though because I was trying to do Transactional tests - since I'm now using @RunWith(Parameterized.class) and setting up my Spring TestContextManager manually the @Transaction annotations caused an exception: java.lang.IllegalArgumentException: Superclass has no null constructors but no arguments were given I couldn't find any built in solution, so I've gone with manual transaction management in my test, using doInTransaction: @RunWith(Parameterized.class) @ContextConfiguration(locations = "classpath*:/testContext.xml") public class MyTest { @Autowired PlatformTransactionManager transactionManager; private TestContextManager testContextManager; public MyTest (... parameters for test) { // store parameters in instance v...
Read more

Intellij tip - Joining multiple lines into one

There are times when you have some code with each statement on its own line, and you want to move it all onto one line. CSS is where I've needed this the most. You start off with some code like this: And really want this: Intellij has a very simple way to do this by using Ctrl-Shift-J - for more information, see http://www.jetbrains.com/idea/webhelp/joining-lines-and-literals.html
Read more