{"id":6,"date":"2006-01-03T14:56:43","date_gmt":"2006-01-03T13:56:43","guid":{"rendered":"http:\/\/bar-solutions.com\/wordpress\/2006\/01\/03\/simple-comparison-sql\/"},"modified":"2006-01-05T09:48:37","modified_gmt":"2006-01-05T08:48:37","slug":"simple-comparison-sql","status":"publish","type":"post","link":"https:\/\/blog.bar-solutions.com\/?p=6","title":{"rendered":"Simple comparison SQL"},"content":{"rendered":"

I am comparing tables in two different schemas. Since I am doing the same thing over and over again I created a small script which might be useful to you too.<\/p>\n

ACCEPT<\/font> db1 <\/font>CHAR<\/font> <\/font>prompt<\/font> <\/font>‘owner 1 :’<\/font>
\n<\/font>ACCEPT<\/font> db2 <\/font>CHAR<\/font> <\/font>prompt<\/font> <\/font>‘owner 2 :’<\/font>
\n<\/font>ACCEPT<\/font> tabname <\/font>CHAR<\/font> <\/font>prompt<\/font> <\/font>‘tablename :’<\/font>
\n<\/font>PROMPT<\/font>
\n<\/font>PROMPT<\/font> &db1..&tabname minus &db2..&tabname
\n<\/font>SELECT<\/font> <\/font>*<\/font> <\/font>FROM<\/font> &db1..&tabname
\n<\/font>MINUS<\/font>
\n<\/font>SELECT<\/font> <\/font>*<\/font> <\/font>FROM<\/font> &db2..&tabname<\/font>;<\/font>
\n<\/font>PROMPT<\/font>
\n<\/font>PROMPT<\/font> &db2..&tabname minus &db1..&tabname
\n<\/font>SELECT<\/font> <\/font>*<\/font> <\/font>FROM<\/font> &db2..&tabname
\n<\/font>MINUS<\/font>
\n<\/font>SELECT<\/font> <\/font>*<\/font> <\/font>FROM<\/font> &db1..&tabname<\/font>;<\/font>
\n<\/font>PROMPT<\/font>
\n<\/font>PROMPT<\/font> count(*) &db1..&tabname
\n<\/font>SELECT<\/font> <\/font>COUNT<\/font>(*)<\/font> <\/font>FROM<\/font> &db1..&tabname<\/font>;<\/font>
\n<\/font>PROMPT<\/font>
\n<\/font>PROMPT<\/font> count(*) &db2..&tabname
\n<\/font>SELECT<\/font> <\/font>COUNT<\/font>(*)<\/font> <\/font>FROM<\/font> &db2..&tabname<\/font>;<\/font>
\n<\/font>PROMPT<\/font>
\n<\/font>PROMPT<\/font> count(*) union
\n<\/font>SELECT<\/font> <\/font>COUNT<\/font>(*)<\/font> <\/font>FROM<\/font>
\n<\/font>(<\/font>
\n<\/font>SELECT<\/font> <\/font>*<\/font> <\/font>FROM<\/font> &db1..&tabname
\n<\/font>UNION<\/font>
\n<\/font>SELECT<\/font> <\/font>*<\/font> <\/font>FROM<\/font> &db2..&tabname
\n<\/font>);<\/font>
\n<\/font>PROMPT<\/font>
\n<\/font>PROMPT<\/font> count(*) union all
\n<\/font>SELECT<\/font> <\/font>COUNT<\/font>(*)<\/font> <\/font>FROM<\/font>
\n<\/font>(<\/font>
\n<\/font>SELECT<\/font> <\/font>*<\/font> <\/font>FROM<\/font> &db1..&tabname
\n<\/font>UNION<\/font> <\/font>ALL<\/font>
\n<\/font>SELECT<\/font> <\/font>*<\/font> <\/font>FROM<\/font> &db2..&tabname
\n<\/font>);<\/font><\/p>\n

First the script asks for the schemas in which the table reside. Then it asks for the tablename you want to compare. After you have entered this information, the script performs a couple of simple sql statements.<\/p>\n

    \n
  1. schema1.table minus schema2.table<\/li>\n
  2. schema2.table minus schema1.table<\/li>\n
  3. count of schema1.table<\/li>\n
  4. count of schema2.table<\/li>\n
  5. count of (schema1.table union schema2.table)<\/li>\n
  6. count of (schema1.table union all schema2.table)<\/li>\n<\/ol>\n

    The difference between union<\/em> and union all<\/em> is that union<\/em> removes all duplicates from the result set, whereas union all<\/em> just unions all records from both tables.<\/p>\n

    Notice the double dot (..) notation in &db1..&tabname. If you don’t use this notation, then Oracle seems to forget the dot between the schemaname and the tablename.<\/p>\n","protected":false},"excerpt":{"rendered":"

    I am comparing tables in two different schemas. Since I am doing the same thing over and over again I created a small script which might be useful to you too. ACCEPT db1 CHAR prompt ‘owner 1 :’ ACCEPT db2 CHAR prompt ‘owner 2 :’ ACCEPT tabname CHAR prompt ‘tablename :’ PROMPT PROMPT &db1..&tabname minus […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[2,9],"tags":[],"class_list":["post-6","post","type-post","status-publish","format-standard","hentry","category-oracle","category-sql"],"_links":{"self":[{"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=\/wp\/v2\/posts\/6","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=6"}],"version-history":[{"count":0,"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=\/wp\/v2\/posts\/6\/revisions"}],"wp:attachment":[{"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=6"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=6"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=6"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}