Thursday, June 6, 2013

SchemaCrawler for Oracle DB diff

I want to be able to trace all the modifications to my CMDB, and email the diff report on a regular basis. Lots of tools there but none seems to be a) free b) simple to use. SchemaCrawler doesn't do the diff, but you can export the whole schema and data into a txt file, and do a diff.

Download from http://schemacrawler.sourceforge.net/ and unzip.

Copy C:\Oracle\Middleware\wlserver_10.3\server\ext\jdbc\oracle\11g\ojdbc6_g.jar into the $SC_HOME/lib folder.

This will NOT display data, only structure:
sc -host=dbhost.acme.com -port=1522 -database=mydb.acme.com -user=CMDBTRUNK -infolevel=maximum -password=blablabla -c=details > cmdbtrunk.txt

This will display data (dump):
sc -host=dbhost.acme.com -port=1522 -database=mydb.acme.com -user=CMDBTRUNK -infolevel=maximum -password=blablabla -c=details,dump > cmdbtrunk.txt

This one will dump tables ACME but NOT tables ACME_BLA:

./sc.sh -host=mydb.acme.com -port=1522 -database=mydb.com -user=CMDBTRUNK -infolevel=standard -password=blablabla -schemas=.*CMDBTRUNK.*  -tabletypes=TABLE -tables=(?!.*\.ACME_BLA.*)(.*\.ACME.*) -c=details,dump > CMDBTRUNK.txt





No comments: