Skip to content

Instantly share code, notes, and snippets.

@monzdrpower
Created October 30, 2014 12:41
Show Gist options
  • Save monzdrpower/b27dd19ffaa489f0c012 to your computer and use it in GitHub Desktop.
Save monzdrpower/b27dd19ffaa489f0c012 to your computer and use it in GitHub Desktop.
import groovy.sql.Sql
import java.sql.ResultSet
import oracle.jdbc.OracleDriver
/**
* compare objects in two Oracle database schemas
* edit database credentials: <hostname>, <sid> and USERNAME:PASSWORD map entries and run as script
* result will be printed to console
*/
def username1 = "SCOTT", target1 = "TEST"
def username2 = "SCOTT", target2 = "PROD"
def skipPrefix = ['TMP','CTL','LDG','STG','REJ','BIN','BKP'] // if needed
// all possible connections
def config = [
DEV : [
URL: 'jdbc:oracle:thin:@<hostname>:1521:<sid>',
SCOTT: 'tyger',
SYSTEM:'MANAGER'
],
TEST:[
URL: 'jdbc:oracle:thin:@<hostname1>:1521:<sid1>',
SCOTT: 'tyger1',
SYSTEM:'MANAGER1'
],
PROD : [
URL: 'jdbc:oracle:thin:@<hostname2>:1521:<sid2>',
SCOTT: 'tyger2',
SYSTEM:'MANAGER2'
]
]
def url1 = config."$target1".URL
def url2 = config."$target2".URL
def password1 = config."$target1"."$username1"
def password2 = config."$target2"."$username2"
def driver = OracleDriver.class.name
def sql1 = Sql.newInstance(driver: driver, user: username1, url: url1, password: password1)
def sql2 = Sql.newInstance(driver: driver, user: username2, url: url2, password: password2)
def types = [
[
selectable: true,
objtype:'TABLE',
table: 'user_tables',
column:'table_name',
filter: '',
comparator: "select COLUMN_NAME cr from USER_TAB_COLS where TABLE_NAME = 'XXX' and COLUMN_NAME not like 'SYS%'"
],
[
selectable: true,
objtype:'VIEW',
table: 'user_views',
column:'view_name',
filter: '',
comparator: "select TEXT cr from USER_VIEWS where VIEW_NAME = 'XXX'"
],
[
selectable: true,
objtype:'SYNONYM',
table: 'user_synonyms',
column:'synonym_name',
filter: '',
comparator: ''
],
[
selectable: false,
objtype:'TRIGGER',
table: 'user_triggers',
column:'trigger_name',
filter: '',
comparator: "select trigger_body cr from USER_TRIGGERS where TRIGGER_NAME = 'XXX'"
],
[
selectable: false,
objtype:'CONSTRAINT',
table: 'user_constraints',
column:'constraint_name',
filter: " where generated <> 'GENERATED NAME' ",
comparator: "select CONSTRAINT_NAME cr from USER_CONSTRAINTS where CONSTRAINT_NAME = 'XXX'"]
]
class Info {
def name, rc, criteria
}
def getInfo(sql2, type, objectName){
def rc, criteria = ''
try {
if( type.selectable ){
try {
rc = sql2.rows("select count(*) rc from $objectName" as String).rc[0]
rc = rc as String
} catch (e){
rc = "ERROR" // for bad synonyms
}
} else {
rc = "-"
}
if(type.comparator){
def rs = sql2.rows(type.comparator.replace('XXX', objectName)).cr
criteria = rs.sort().join(",")
def objtype = type.objtype
if(objtype == 'TABLE' && criteria.toUpperCase().contains('IS_DELETED')){
def rcActive = sql2.rows("select count(*) rc from $objectName where nvl(is_deleted, 0) = 0" as String).rc[0]
rc = "$rcActive/$rc"
}
}
} catch (e) {
rc = e.message
criteria = ''
}
new Info(name:objectName, rc: rc, criteria: criteria)
}
def start = Calendar.getInstance().timeInMillis
def totalDiff = 0
types.each { type ->
println "==============================================================================="
println " $target1 $username1 $target2 $username2"
println "================================ $type.objtype ================================\n"
def query = "select $type.column objname from $type.table "+type.filter+" order by 1" as String
// ---------------- 1 -----------------------
Map data1 = [:]
def th = Thread.start {
def objectNames = sql1.rows(query).objname
objectNames.each { objectName ->
if(objectName[0..2] in skipPrefix)
return;
data1."$objectName" = getInfo(sql1, type, objectName)
}
}
// ---------------- 2 -----------------------
Map data2 = [:]
sql2.rows(query).objname.each { objectName ->
if(objectName[0..2] in skipPrefix)
return;
data2."$objectName" = getInfo(sql2, type, objectName)
}
th.join()
// total
def allTables = (data1.keySet() + data2.keySet()).unique().sort()
if(!allTables)
return
def padding = allTables*.size().max() + 1
def diffCount = 0
def total = allTables.collect{ t ->
[
data1.containsKey(t) ? t : "",
data1.containsKey(t) ? data1."$t".rc : "",
data1.containsKey(t) ? data1."$t".criteria : "",
data2.containsKey(t) ? t : "",
data2.containsKey(t) ? data2."$t".rc : "",
data2.containsKey(t) ? data2."$t".criteria : ""
]
}
//println total
total.each { t ->
print t[0].padLeft(padding, " ")
print t[1].padLeft(14, " ") + " "
print t[3].padLeft(padding, " ")
print t[4].padLeft(14, " ") + " "
if(t[0] && t[3] && t[1] != t[4]){
// row count only
print "< rc".padRight(12, ' ')
} else if(!t[0] || !t[3]){
// object missing
diffCount++
print"<--- ".padRight(12, ' ')
}
def t2 = t[2].toUpperCase().replaceAll(/\s+/,' ')
def t5 = t[5].toUpperCase().replaceAll(/\s+/,' ')
if(t[0] && t[3] && t2 != t5 ){
diffCount++
print ""+[t2, t5]
}
println ""
}
println "\nDiff: $diffCount"
totalDiff += diffCount
}
def grant(row, map1, map2, username, skipPrefix) {
if(row.TABLE_NAME[0..2] in skipPrefix)
return;
if(row.GRANTEE == username){
def key = (row.GRANTOR+" - "+row.TABLE_NAME)
if(map1[key]){
map1[key] = [map1[key], row.PRIVILEGE].sort().join(",")
} else {
map1[key] = row.PRIVILEGE
}
}
if(row.GRANTOR == username){
def key = (row.GRANTEE+" - "+row.TABLE_NAME)
if(map2[key]){
map2[key] = [map2[key], row.PRIVILEGE].sort().join(",")
} else {
map2[key] = row.PRIVILEGE
}
}
}
def grantSql(username) {"""select GRANTEE, TABLE_NAME, GRANTOR, PRIVILEGE
from user_tab_privs
where grantee like '$username%' or grantor like '$username%'""" as String
}
def granted1 = [:], received1 = [:]
sql1.eachRow(grantSql(username1)) {
grant(it, received1, granted1, username1, skipPrefix)
}
def granted2 = [:], received2 = [:]
sql2.eachRow(grantSql(username2)) {
grant(it, received2, granted2, username2, skipPrefix)
}
def diffCount = 0
def show = { left, right ->
def allGrants = (left.keySet() + right.keySet()).unique().sort()
def padding = allGrants*.size().max() + 1
def total = allGrants.collect{ t ->
[
left.containsKey(t) ? t : "",
left.containsKey(t) ? left."$t" : "",
right.containsKey(t) ? t : "",
right.containsKey(t) ? right."$t" : "",
]
}
total.each { t ->
if(t[0] && t[2] && t[1] != t[3]){
// grant diff
diffCount++
print " --->".padRight(10, ' ')
} else if(!t[0] || !t[2]){
// object missing
print " --->".padRight(10, ' ')
diffCount++
} else {
print "".padRight(10, ' ')
}
print t[0].padRight(padding, " ")
print t[1].padRight(24, " ") + " "
print t[2].padRight(padding, " ")
print t[3].padRight(24, " ") + " "
println ""
}
}
println "==============================================================================="
println " $target1 $username1 $target2 $username2"
println "\n============================== ACCESS GRANTED ===============================\n"
show(granted1, granted2)
println "\nDiff: $diffCount"
totalDiff += diffCount
diffCount = 0
println "==============================================================================="
println " $target1 $username1 $target2 $username2"
println "\n============================ ACCESS RECEIVED ================================\n"
show(received1, received2)
println "\nDiff: $diffCount"
totalDiff += diffCount
diffCount = 0
println "\nTotal diff: $totalDiff"
println "${(Calendar.instance.timeInMillis - start) / 1000} sec"
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment