| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: schema comparisons on different databases
This is a multi-part message in MIME format.
------=_NextPart_000_003B_01C05019.E4DBFA00 Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
Here is one home made script. I don't think it is perfect but you can change it:
Djordje
----- Original Message -----
From: <nlzanen1_at_ey.nl>
To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
Sent: Thursday, November 16, 2000 10:36 AM
Subject: schema comparisons on different databases
>
>
> Hi All,
>
> Hit me with shameless plugs here.
>
>
> Anybody know of any tool/utility etc that will compare schema A on
instance
> A with Schema A on instance B.
> Down to the nitty gritty details.
> I Like all options
>
>
> TIA
>
>
> Jack
>
> ===================================================================
> De informatie verzonden met dit E-mail bericht is uitsluitend bestemd voor
> de geadresseerde. Gebruik van deze informatie door anderen dan de
> geadresseerde is verboden. Openbaarmaking, vermenigvuldiging, verspreiding
> en/of verstrekking van deze informatie aan derden is niet toegestaan.
> Ernst & Young staat niet in voor de juiste en volledige overbrenging van
de
> inhoud van een verzonden E-mail, noch voor tijdige ontvangst daarvan.
> ===================================================================
> The information contained in this communication is confidential and may be
> legally privileged. It is intended solely for the use of the individual or
> entity to whom it is addressed and others authorised to receive it. If you
> are not the intended recipient you are hereby notified that any
disclosure,
> copying, distribution or taking any action in reliance on the contents of
> this information is strictly prohibited and may be unlawful. Ernst &
> Young is neither liable for the proper and complete transmission of the
> information contained in this communication nor for any delay in its
> receipt.
> ===================================================================
>
>
>
>
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author:
> INET: nlzanen1_at_ey.nl
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
------=_NextPart_000_003B_01C05019.E4DBFA00 Content-Type: application/octet-stream;
name="CompareDB.sql"
Content-Transfer-Encoding: quoted-printable
Content-Disposition: attachment;
filename="CompareDB.sql"
--
-- cmpprdv.sql
--
-- Script that compares two databases and finds all the differences
-- for given users:
-- Objects existing in one but not in the other database
-- Objects with different definition
--
-- The script should be run by SYSTEM from the db1 side
set trimspool on
set pagesize 1000
spool ZZZZZ
set feedback off
set heading off
select 'Differences between prod and dvlp on '||
TO_CHAR(SYSDATE,'DD-MON-YYYY HH:MI')
from dual
/
set heading on
set feedback on
drop database link db2.domain
/
create database link db2.domain
connect to system identified by syspas
using 'db2'
/
set pagesize 1000
set linesize 120
col ord noprint
col usr noprint
col loc noprint
col diff heading "db1 - db2 differences"
select '1' ord
, owner usr
, '2' loc
, RTRIM(SUBSTR(OWNER, 1, 10)) || ' owns on prod ' ||
RPAD(SUBSTR(
DECODE(OBJECT_TYPE,
'DATABASE LINK', 'DB LINK',
'PACKAGE BODY', 'PCKG BODY',
OBJECT_TYPE),
1, 10),10) || ' ' ||
RPAD(RTRIM(SUBSTR(OBJECT_NAME, 1, 30)),20) || ' created ' ||
TO_CHAR(CREATED) diff
from dba_objects o
where OBJECT_TYPE || OBJECT_NAME not in
( select OBJECT_TYPE || OBJECT_NAME
from dba_objects_at_db2.domain ro
where ro.owner =3D o.owner
)
and owner in ('SCHEMA_NAME')
UNION
select '1' ord
, owner usr
, '1' loc
, RTRIM(SUBSTR(OWNER, 1, 10)) || ' owns on dvlp ' ||
RPAD(SUBSTR(
DECODE(OBJECT_TYPE,
'DATABASE LINK', 'DB LINK',
'PACKAGE BODY', 'PCKG BODY',
OBJECT_TYPE),
1, 10),10) || ' ' ||
RPAD(RTRIM(SUBSTR(OBJECT_NAME, 1, 30)),20) || ' created ' ||
TO_CHAR(CREATED) diff
from dba_objects_at_db2.domain ro
where OBJECT_TYPE || OBJECT_NAME not in
( select OBJECT_TYPE || OBJECT_NAME
from dba_objects o
where ro.owner =3D o.owner
)
and owner in ('SCHEMA_NAME')
UNION
select '2' ord
, tc.owner usr
, '1' loc
, RTRIM(SUBSTR(tc.OWNER, 1, 10)) || ' table ' ||
tc.TABLE_NAME || ' on prod no col ' ||
RTRIM(tc.COLUMN_NAME) || '. Last DDL=3D' ||
TO_CHAR(o.LAST_DDL_TIME) diff
from dba_tab_columns tc
, dba_objects o
where tc.owner =3D o.owner
and tc.table_name =3D o.object_name
and o.object_type =3D 'TABLE'
and exists
( select COLUMN_NAME
from dba_tab_columns_at_db2.domain rtc
where rtc.owner =3D tc.owner
and rtc.table_name =3D tc.table_name
)
and COLUMN_NAME not in
( select COLUMN_NAME
from dba_tab_columns_at_db2.domain rtc
where rtc.owner =3D tc.owner
and rtc.table_name =3D tc.table_name
)
and tc.owner in ('SCHEMA_NAME')
UNION
select '2' ord
, rtc.owner usr
, '2' loc
, RTRIM(SUBSTR(rtc.OWNER, 1, 10)) || ' table ' ||
rtc.TABLE_NAME || ' on dvlp no col ' ||
RTRIM(rtc.COLUMN_NAME) || '. Last DDL=3D' ||
TO_CHAR(o.LAST_DDL_TIME) diff
from dba_tab_columns_at_db2.domain rtc
, dba_objects o
where rtc.owner =3D o.owner
and rtc.table_name =3D o.object_name
and o.object_type =3D 'TABLE'
and exists
( select COLUMN_NAME
from dba_tab_columns tc
where rtc.owner =3D tc.owner
and rtc.table_name =3D tc.table_name
)
and COLUMN_NAME not in
( select COLUMN_NAME
from dba_tab_columns tc
where rtc.owner =3D tc.owner
and rtc.table_name =3D tc.table_name
)
and rtc.owner in ('SCHEMA_NAME')
UNION
select '3' ord
, tc.owner usr
, tc.table_name||tc.column_name||'0' loc
, RTRIM(SUBSTR(tc.OWNER, 1, 10)) || ' table ' ||
tc.TABLE_NAME || ' diff in col ' ||
RTRIM(tc.COLUMN_NAME) || '. Last DDL=3D' ||
TO_CHAR(o.LAST_DDL_TIME) diff
from dba_tab_columns tc
, dba_objects o
where tc.owner =3D o.owner
and tc.table_name =3D o.object_name
and o.object_type =3D 'TABLE'
and DATA_TYPE||DATA_TYPE_MOD||TO_CHAR(DATA_LENGTH)||
TO_CHAR(DATA_PRECISION)||TO_CHAR(DATA_SCALE)||NULLABLE||
TO_CHAR(DEFAULT_LENGTH) <>
( select DATA_TYPE||DATA_TYPE_MOD||TO_CHAR(DATA_LENGTH)||
=
TO_CHAR(DATA_PRECISION)||TO_CHAR(DATA_SCALE)||NULLABLE||
TO_CHAR(DEFAULT_LENGTH)
from dba_tab_columns_at_db2.domain rtc
where rtc.owner =3D tc.owner
and rtc.table_name =3D tc.table_name
and rtc.column_name =3D tc.column_name
)
and tc.owner in ('SCHEMA_NAME')
UNION
select '3' ord
, rtc.owner usr
, rtc.table_name||rtc.column_name||'1' loc
, ' dvlp: '||DATA_TYPE||' '||DATA_TYPE_MOD||' '||
TO_CHAR(DATA_LENGTH)||' '||TO_CHAR(DATA_PRECISION)||' '||
TO_CHAR(DATA_SCALE)||' '||NULLABLE||' =
'||TO_CHAR(DEFAULT_LENGTH) diff
from dba_tab_columns_at_db2.domain rtc
, dba_objects o
where rtc.owner =3D o.owner
and rtc.table_name =3D o.object_name
and o.object_type =3D 'TABLE'
and DATA_TYPE||DATA_TYPE_MOD||TO_CHAR(DATA_LENGTH)||
TO_CHAR(DATA_PRECISION)||TO_CHAR(DATA_SCALE)||NULLABLE||
TO_CHAR(DEFAULT_LENGTH) <>
( select DATA_TYPE||DATA_TYPE_MOD||TO_CHAR(DATA_LENGTH)||
=
TO_CHAR(DATA_PRECISION)||TO_CHAR(DATA_SCALE)||NULLABLE||
TO_CHAR(DEFAULT_LENGTH)
from dba_tab_columns tc
where rtc.owner =3D tc.owner
and rtc.table_name =3D tc.table_name
and rtc.column_name =3D tc.column_name
)
and rtc.owner in ('SCHEMA_NAME')
UNION
select '3' ord
, tc.owner usr
, tc.table_name||tc.column_name||'2' loc
, ' prod: '||DATA_TYPE||' '||DATA_TYPE_MOD||' '||
TO_CHAR(DATA_LENGTH)||' '||TO_CHAR(DATA_PRECISION)||' '||
TO_CHAR(DATA_SCALE)||' '||NULLABLE||' =
'||TO_CHAR(DEFAULT_LENGTH) diff
from dba_tab_columns tc
, dba_objects o
where tc.owner =3D o.owner
and tc.table_name =3D o.object_name
and o.object_type =3D 'TABLE'
and DATA_TYPE||DATA_TYPE_MOD||TO_CHAR(DATA_LENGTH)||
TO_CHAR(DATA_PRECISION)||TO_CHAR(DATA_SCALE)||NULLABLE||
TO_CHAR(DEFAULT_LENGTH) <>
( select DATA_TYPE||DATA_TYPE_MOD||TO_CHAR(DATA_LENGTH)||
=
TO_CHAR(DATA_PRECISION)||TO_CHAR(DATA_SCALE)||NULLABLE||
TO_CHAR(DEFAULT_LENGTH)
from dba_tab_columns_at_db2.domain rtc
where rtc.owner =3D tc.owner
and rtc.table_name =3D tc.table_name
and rtc.column_name =3D tc.column_name
)
and tc.owner in ('SCHEMA_NAME')
UNION
select '4' ord
, rc.owner usr
, '1' loc
, RTRIM(SUBSTR(rc.OWNER, 1, 10)) || ' table ' ||
rc.TABLE_NAME || ' on dvlp has cnstr ' ||
RTRIM(rc.CONSTRAINT_NAME) || '. Last DDL=3D' ||
TO_CHAR(o.LAST_DDL_TIME) diff
from dba_constraints_at_db2.domain rc
, dba_objects o
where rc.owner =3D o.owner
and rc.table_name =3D o.object_name
and o.object_type =3D 'TABLE'
and rc.constraint_type !=3D 'R'
and exists
( select TABLE_NAME
from dba_constraints c
where rc.owner =3D c.owner
and rc.table_name =3D c.table_name
and c.constraint_type !=3D 'R'
)
and CONSTRAINT_NAME not in
( select CONSTRAINT_NAME
from dba_constraints c
where rc.owner =3D c.owner
and rc.table_name =3D c.table_name
and c.constraint_type !=3D 'R'
)
and rc.owner in ('SCHEMA_NAME')
UNION
select '4' ord
, c.owner usr
, '2' loc
, RTRIM(SUBSTR(c.OWNER, 1, 10)) || ' table ' ||
c.TABLE_NAME || ' on prod has cnstr ' ||
RTRIM(c.CONSTRAINT_NAME) || '. Last DDL=3D' ||
TO_CHAR(o.LAST_DDL_TIME) diff
from dba_constraints c
, dba_objects o
where c.owner =3D o.owner
and c.table_name =3D o.object_name
and o.object_type =3D 'TABLE'
and c.constraint_type !=3D 'R'
and exists
( select TABLE_NAME
from dba_constraints_at_db2.domain rc
where rc.owner =3D c.owner
and rc.table_name =3D c.table_name
and rc.constraint_type !=3D 'R'
)
and CONSTRAINT_NAME not in
( select CONSTRAINT_NAME
from dba_constraints_at_db2.domain rc
where rc.owner =3D c.owner
and rc.table_name =3D c.table_name
and rc.constraint_type !=3D 'R'
)
and c.owner in ('SCHEMA_NAME')
UNION
select '4' ord
, rc.owner usr
, '3' loc
, RTRIM(SUBSTR(rc.OWNER, 1, 10)) || ' table ' ||
rc.TABLE_NAME || ' on dvlp has cnstr ' ||
RTRIM(rc.CONSTRAINT_NAME) || '. Last DDL=3D' ||
TO_CHAR(o.LAST_DDL_TIME) diff
from dba_constraints_at_db2.domain rc
, dba_objects o
where rc.owner =3D o.owner
and rc.table_name =3D o.object_name
and o.object_type =3D 'TABLE'
and rc.constraint_type =3D 'R'
and exists
( select TABLE_NAME
from dba_constraints c
where rc.owner =3D c.owner
and rc.table_name =3D c.table_name
and c.constraint_type =3D 'R'
)
and R_CONSTRAINT_NAME not in
( select R_CONSTRAINT_NAME
from dba_constraints c
where rc.owner =3D c.owner
and rc.table_name =3D c.table_name
and c.constraint_type =3D 'R'
)
and rc.owner in ('SCHEMA_NAME')
UNION
select '4' ord
, c.owner usr
, '4' loc
, RTRIM(SUBSTR(c.OWNER, 1, 10)) || ' table ' ||
c.TABLE_NAME || ' on prod has cnstr ' ||
RTRIM(c.CONSTRAINT_NAME) || '. Last DDL=3D' ||
TO_CHAR(o.LAST_DDL_TIME) diff
from dba_constraints c
, dba_objects o
where c.owner =3D o.owner
and c.table_name =3D o.object_name
and o.object_type =3D 'TABLE'
and c.constraint_type =3D 'R'
and exists
( select TABLE_NAME
from dba_constraints_at_db2.domain rc
where rc.owner =3D c.owner
and rc.table_name =3D c.table_name
and rc.constraint_type =3D 'R'
)
and R_CONSTRAINT_NAME not in
( select R_CONSTRAINT_NAME
from dba_constraints_at_db2.domain rc
where rc.owner =3D c.owner
and rc.table_name =3D c.table_name
and rc.constraint_type =3D 'R'
)
and c.owner in ('SCHEMA_NAME')
UNION
select '5' ord
, c.owner usr
, c.table_name||c.constraint_name||'0' loc
, RTRIM(SUBSTR(c.OWNER, 1, 10)) || ' table ' ||
c.TABLE_NAME || ' diff prod cnstr ' ||
RTRIM(c.CONSTRAINT_NAME) || '. Last DDL=3D' ||
TO_CHAR(o.LAST_DDL_TIME) diff
from dba_constraints c
, dba_cons_columns cc
, dba_objects o
where c.owner =3D o.owner
and c.table_name =3D o.object_name
and o.object_type =3D 'TABLE'
and c.owner =3D cc.owner
and c.constraint_name =3D cc.constraint_name
and exists
( select constraint_name
from dba_constraints_at_db2.domain rc
where rc.owner =3D c.owner
and rc.constraint_name =3D c.constraint_name
)
and ( CONSTRAINT_TYPE || R_OWNER || R_CONSTRAINT_NAME ||
DELETE_RULE || c.STATUS || DEFERRABLE|| DEFERRED <>
( select CONSTRAINT_TYPE || R_OWNER || R_CONSTRAINT_NAME ||
DELETE_RULE || rc.STATUS || DEFERRABLE || DEFERRED
from dba_constraints_at_db2.domain rc
where rc.owner =3D c.owner
and rc.table_name =3D c.table_name
and rc.constraint_name =3D c.constraint_name
)
or
cc.column_name not in
( select rcc.column_name
from dba_cons_columns_at_db2.domain rcc
where rcc.owner =3D cc.owner
and rcc.table_name =3D cc.table_name
and rcc.constraint_name =3D cc.constraint_name
)
or
cc.position <>
( select rcc.position
from dba_cons_columns_at_db2.domain rcc
where rcc.owner =3D cc.owner
and rcc.table_name =3D cc.table_name
and rcc.constraint_name =3D cc.constraint_name
and rcc.column_name =3D cc.column_name
)
)
and c.owner in ('SCHEMA_NAME')
UNION
select '5' ord
, rc.owner usr
, rc.table_name||rc.constraint_name||'1' loc
, ' dvlp: ' || ' ' || CONSTRAINT_TYPE || ' ' || R_OWNER || ' ' =
||
R_CONSTRAINT_NAME || ' ' || DELETE_RULE || ' ' || rc.STATUS || =
' ' ||
DEFERRABLE || ' ' || DEFERRED diff
from dba_constraints_at_db2.domain rc
, dba_cons_columns_at_db2.domain rcc
, dba_objects o
where rc.owner =3D o.owner
and rc.table_name =3D o.object_name
and o.object_type =3D 'TABLE'
and rc.owner =3D rcc.owner
and rc.constraint_name =3D rcc.constraint_name
and exists
( select constraint_name
from dba_constraints c
where rc.owner =3D c.owner
and rc.constraint_name =3D c.constraint_name
)
and ( CONSTRAINT_TYPE || R_OWNER || R_CONSTRAINT_NAME ||
DELETE_RULE || rc.STATUS || DEFERRABLE|| DEFERRED <>
( select CONSTRAINT_TYPE || R_OWNER || R_CONSTRAINT_NAME ||
DELETE_RULE || c.STATUS || DEFERRABLE || DEFERRED
from dba_constraints c
where rc.owner =3D c.owner
and rc.table_name =3D c.table_name
and rc.constraint_name =3D c.constraint_name
)
)
and rc.owner in ('SCHEMA_NAME')
UNION
select '5' ord
, c.owner usr
, c.table_name||c.constraint_name||'2' loc
, ' prod: ' || ' ' || CONSTRAINT_TYPE || ' ' || R_OWNER || ' ' =
||
R_CONSTRAINT_NAME || ' ' || DELETE_RULE || ' ' || c.STATUS || ' =
' ||
DEFERRABLE || ' ' || DEFERRED diff
from dba_constraints c
, dba_cons_columns cc
, dba_objects o
where c.owner =3D o.owner
and c.table_name =3D o.object_name
and o.object_type =3D 'TABLE'
and c.owner =3D cc.owner
and c.constraint_name =3D cc.constraint_name
and exists
( select constraint_name
from dba_constraints_at_db2.domain rc
where rc.owner =3D c.owner
and rc.constraint_name =3D c.constraint_name
)
and ( CONSTRAINT_TYPE || R_OWNER || R_CONSTRAINT_NAME ||
DELETE_RULE || c.STATUS || DEFERRABLE || DEFERRED <>
( select CONSTRAINT_TYPE || R_OWNER || R_CONSTRAINT_NAME ||
DELETE_RULE || rc.STATUS || DEFERRABLE || DEFERRED
from dba_constraints_at_db2.domain rc
where rc.owner =3D c.owner
and rc.table_name =3D c.table_name
and rc.constraint_name =3D c.constraint_name
)
)
and c.owner in ('SCHEMA_NAME')
UNION
select '5' ord
, rc.owner usr
, rc.table_name||rc.constraint_name||'1' loc
, ' dvlp: column ' || rcc.column_name ||
DECODE(rcc.position,NULL,' ',
' position '||TO_CHAR(rcc.position)) diff
from dba_constraints_at_db2.domain rc
, dba_cons_columns_at_db2.domain rcc
, dba_objects o
where rc.owner =3D o.owner
and rc.table_name =3D o.object_name
and o.object_type =3D 'TABLE'
and rc.owner =3D rcc.owner
and rc.constraint_name =3D rcc.constraint_name
and exists
( select constraint_name
from dba_constraints c
where rc.owner =3D c.owner
and rc.constraint_name =3D c.constraint_name
)
and ( rcc.column_name not in
( select cc.column_name
from dba_cons_columns cc
where rcc.owner =3D cc.owner
and rcc.table_name =3D cc.table_name
and rcc.constraint_name =3D cc.constraint_name
)
or
rcc.position <>
( select cc.position
from dba_cons_columns cc
where rcc.owner =3D cc.owner
and rcc.table_name =3D cc.table_name
and rcc.constraint_name =3D cc.constraint_name
and rcc.column_name =3D cc.column_name
)
)
and rc.owner in ('SCHEMA_NAME')
UNION
select '5' ord
, c.owner usr
, c.table_name||c.constraint_name||'2' loc
, ' prod: column ' || cc.column_name ||
DECODE(cc.position,NULL,' ',
' position '||TO_CHAR(cc.position)) diff
from dba_constraints c
, dba_cons_columns cc
, dba_objects o
where c.owner =3D o.owner
and c.table_name =3D o.object_name
and o.object_type =3D 'TABLE'
and c.owner =3D cc.owner
and c.constraint_name =3D cc.constraint_name
and exists
( select constraint_name
from dba_constraints_at_db2.domain rc
where rc.owner =3D c.owner
and rc.constraint_name =3D c.constraint_name
)
and ( cc.column_name not in
( select rcc.column_name
from dba_cons_columns_at_db2.domain rcc
where rcc.owner =3D cc.owner
and rcc.table_name =3D cc.table_name
and rcc.constraint_name =3D cc.constraint_name
)
or
cc.position <>
( select rcc.position
from dba_cons_columns_at_db2.domain rcc
where rcc.owner =3D cc.owner
and rcc.table_name =3D cc.table_name
and rcc.constraint_name =3D cc.constraint_name
and rcc.column_name =3D cc.column_name
)
)
and c.owner in ('SCHEMA_NAME')
UNION
select '6' ord
, i.index_owner usr
, i.index_name||'0' loc
, RTRIM(SUBSTR(i.INDEX_OWNER, 1, 10)) || ' index ' ||
i.INDEX_NAME || ', last DDL=3D' ||
TO_CHAR(o.LAST_DDL_TIME) diff
from dba_ind_columns i
, dba_objects o
where i.index_owner =3D o.owner
and i.index_name =3D o.object_name
and o.object_type =3D 'INDEX'
and exists
( select index_name
from dba_ind_columns_at_db2.domain ri
where ri.index_owner =3D i.index_owner
and ri.index_name =3D i.index_name
)
and ( column_name not in
( select ri.column_name
from dba_ind_columns_at_db2.domain ri
where ri.index_owner =3D i.index_owner
and ri.index_name =3D i.index_name
)
or TABLE_OWNER || TABLE_NAME not in
( select TABLE_OWNER || TABLE_NAME
from dba_ind_columns_at_db2.domain ri
where ri.index_owner =3D i.index_owner
and ri.index_name =3D i.index_name
)
)
and i.index_owner in ('SCHEMA_NAME')
UNION
select '6' ord
, ri.index_owner usr
, ri.index_name||'1' loc
, ' dvlp: tab_own=3D' || TABLE_OWNER || ' tab_name=3D' || =
TABLE_NAME ||
' col_name=3D' || COLUMN_NAME diff
from dba_ind_columns_at_db2.domain ri
, dba_objects o
where ri.index_owner =3D o.owner
and ri.index_name =3D o.object_name
and o.object_type =3D 'INDEX'
and exists
( select index_name
from dba_ind_columns i
where ri.index_owner =3D i.index_owner
and ri.index_name =3D i.index_name
)
and ( column_name not in
( select ri.column_name
from dba_ind_columns i
where ri.index_owner =3D i.index_owner
and ri.index_name =3D i.index_name
)
or TABLE_OWNER || TABLE_NAME not in
( select TABLE_OWNER || TABLE_NAME
from dba_ind_columns i
where ri.index_owner =3D i.index_owner
and ri.index_name =3D i.index_name
)
)
and ri.index_owner in ('SCHEMA_NAME')
UNION
select '6' ord
, i.index_owner usr
, i.index_name||'2' loc
, ' prod: tab_own=3D' || TABLE_OWNER || ' tab_name=3D' || =
TABLE_NAME ||
' col_name=3D' || COLUMN_NAME diff
from dba_ind_columns i
, dba_objects o
where i.index_owner =3D o.owner
and i.index_name =3D o.object_name
and o.object_type =3D 'INDEX'
and exists
( select index_name
from dba_ind_columns_at_db2.domain ri
where ri.index_owner =3D i.index_owner
and ri.index_name =3D i.index_name
)
and ( column_name not in
( select ri.column_name
from dba_ind_columns_at_db2.domain ri
where ri.index_owner =3D i.index_owner
and ri.index_name =3D i.index_name
)
or TABLE_OWNER || TABLE_NAME not in
( select TABLE_OWNER || TABLE_NAME
from dba_ind_columns_at_db2.domain ri
where ri.index_owner =3D i.index_owner
and ri.index_name =3D i.index_name
)
)
and i.index_owner in ('SCHEMA_NAME')
UNION
select '7' ord
, i.index_owner usr
, i.index_name||i.column_name||'0' loc
, RTRIM(SUBSTR(i.INDEX_OWNER, 1, 10)) || ' index ' ||
i.INDEX_NAME || ' column ' ||
RTRIM(i.COLUMN_NAME) || '. Last DDL=3D' ||
TO_CHAR(o.LAST_DDL_TIME) diff
from dba_ind_columns i
, dba_objects o
where i.index_owner =3D o.owner
and i.index_name =3D o.object_name
and o.object_type =3D 'INDEX'
and exists
( select index_name
from dba_ind_columns_at_db2.domain ri
where ri.index_owner =3D i.index_owner
and ri.index_name =3D i.index_name
)
and TABLE_OWNER || TABLE_NAME in
( select TABLE_OWNER || TABLE_NAME
from dba_ind_columns_at_db2.domain ri
where ri.index_owner =3D i.index_owner
and ri.index_name =3D i.index_name
)
and column_name in
( select ri.column_name
from dba_ind_columns_at_db2.domain ri
where ri.index_owner =3D i.index_owner
and ri.index_name =3D i.index_name
and ri.column_name =3D i.column_name
)
and TO_CHAR(COLUMN_POSITION) || TO_CHAR(COLUMN_LENGTH) <>
( select TO_CHAR(COLUMN_POSITION) || TO_CHAR(COLUMN_LENGTH)
from dba_ind_columns_at_db2.domain ri
where ri.index_owner =3D i.index_owner
and ri.index_name =3D i.index_name
and ri.column_name =3D i.column_name
)
and i.index_owner in ('SCHEMA_NAME')
UNION
select '7' ord
, ri.index_owner usr
, ri.index_name||ri.column_name||'1' loc
, ' dvlp: col_pos=3D' || TO_CHAR(COLUMN_POSITION) ||
' col_len=3D' || TO_CHAR(COLUMN_LENGTH)
from dba_ind_columns_at_db2.domain ri
, dba_objects o
where ri.index_owner =3D o.owner
and ri.index_name =3D o.object_name
and o.object_type =3D 'INDEX'
and exists
( select index_name
from dba_ind_columns i
where ri.index_owner =3D i.index_owner
and ri.index_name =3D i.index_name
)
and TABLE_OWNER || TABLE_NAME in
( select TABLE_OWNER || TABLE_NAME
from dba_ind_columns i
where ri.index_owner =3D i.index_owner
and ri.index_name =3D i.index_name
)
and column_name in
( select i.column_name
from dba_ind_columns i
where ri.index_owner =3D i.index_owner
and ri.index_name =3D i.index_name
and ri.column_name =3D i.column_name
)
and TO_CHAR(COLUMN_POSITION) || TO_CHAR(COLUMN_LENGTH) <>
( select TO_CHAR(COLUMN_POSITION) || TO_CHAR(COLUMN_LENGTH)
from dba_ind_columns i
where ri.index_owner =3D i.index_owner
and ri.index_name =3D i.index_name
and ri.column_name =3D i.column_name
)
and ri.index_owner in ('SCHEMA_NAME')
UNION
select '7' ord
, i.index_owner usr
, i.index_name||i.column_name||'2' loc
, ' prod: col_pos=3D' || TO_CHAR(COLUMN_POSITION) ||
' col_len=3D' || TO_CHAR(COLUMN_LENGTH)
from dba_ind_columns i
, dba_objects o
where i.index_owner =3D o.owner
and i.index_name =3D o.object_name
and o.object_type =3D 'INDEX'
and exists
( select index_name
from dba_ind_columns_at_db2.domain ri
where ri.index_owner =3D i.index_owner
and ri.index_name =3D i.index_name
)
and TABLE_OWNER || TABLE_NAME in
( select TABLE_OWNER || TABLE_NAME
from dba_ind_columns_at_db2.domain ri
where ri.index_owner =3D i.index_owner
and ri.index_name =3D i.index_name
)
and column_name in
( select ri.column_name
from dba_ind_columns_at_db2.domain ri
where ri.index_owner =3D i.index_owner
and ri.index_name =3D i.index_name
and ri.column_name =3D i.column_name
)
and TO_CHAR(COLUMN_POSITION) || TO_CHAR(COLUMN_LENGTH) <>
( select TO_CHAR(COLUMN_POSITION) || TO_CHAR(COLUMN_LENGTH)
from dba_ind_columns_at_db2.domain ri
where ri.index_owner =3D i.index_owner
and ri.index_name =3D i.index_name
and ri.column_name =3D i.column_name
)
and i.index_owner in ('SCHEMA_NAME')
UNION
select '8' ord
, o.owner usr
, '1' loc
, 'different view on prod and dvlp ' || o.owner || '.' || =
o.view_name
|| ': prod length ' || o.text_length || ' dvlp ' || =
ro.text_length
from dba_views o
, dba_views_at_db2.domain ro
where ro.owner =3D o.owner
and ro.VIEW_NAME =3D o.VIEW_NAME
and ro.text_length !=3D o.text_length
and o.owner in ('SCHEMA_NAME')
UNION
select '9' ord
, s.owner usr
, s.synonym_name||'0' loc
, RTRIM(SUBSTR(s.OWNER, 1, 10)) || ' synonym ' ||
s.SYNONYM_NAME || ' different. ' ||
'Last DDL=3D' || TO_CHAR(o.LAST_DDL_TIME) diff
from dba_synonyms s
, dba_objects o
where s.owner =3D o.owner
and s.table_name =3D o.object_name
and o.object_type =3D 'SYNONYM'
and TABLE_OWNER || TABLE_NAME || DB_LINK <>
( select TABLE_OWNER || TABLE_NAME || DB_LINK
from dba_synonyms_at_db2.domain rs
where rs.owner =3D s.owner
and rs.synonym_name =3D s.synonym_name
)
and s.owner in ('SCHEMA_NAME')
UNION
select '9' ord
, s.owner usr
, s.synonym_name||'2' loc
, ' prod: for table ' || RTRIM(TABLE_OWNER) || '.' ||
RTRIM(TABLE_NAME) || DECODE(DB_LINK, NULL, '','@'||DB_LINK)
from dba_synonyms s
, dba_objects o
where s.owner =3D o.owner
and s.table_name =3D o.object_name
and o.object_type =3D 'SYNONYM'
and TABLE_OWNER || TABLE_NAME || DB_LINK <>
( select TABLE_OWNER || TABLE_NAME || DB_LINK
from dba_synonyms_at_db2.domain rs
where rs.owner =3D s.owner
and rs.synonym_name =3D s.synonym_name
)
and s.owner in ('SCHEMA_NAME')
UNION
select '9' ord
, rs.owner usr
, rs.synonym_name||'1' loc
, ' dvlp: for table ' || RTRIM(TABLE_OWNER) || '.' ||
RTRIM(TABLE_NAME) || DECODE(DB_LINK, NULL, '','@'||DB_LINK)
from dba_synonyms_at_db2.domain rs
, dba_objects o
where rs.owner =3D o.owner
and rs.table_name =3D o.object_name
and o.object_type =3D 'SYNONYM'
and TABLE_OWNER || TABLE_NAME || DB_LINK <>
( select TABLE_OWNER || TABLE_NAME || DB_LINK
from dba_synonyms s
where rs.owner =3D s.owner
and rs.synonym_name =3D s.synonym_name
)
and rs.owner in ('SCHEMA_NAME')
UNION
select 'A' ord
, p.grantee usr
, '2' loc
, RTRIM(SUBSTR(p.GRANTEE, 1, 10)) || ' on prod has sys prvlg ' ||
RTRIM(SUBSTR(p.PRIVILEGE, 1, 40)) || ' admin =3D ' ||
p.ADMIN_OPTION diff
from dba_sys_privs p
where PRIVILEGE || ADMIN_OPTION not in
( select PRIVILEGE || ADMIN_OPTION
from dba_sys_privs_at_db2.domain rp
where p.grantee =3D rp.grantee
)
and p.grantee in ('SCHEMA_NAME')
UNION
select 'A' ord
, rp.grantee usr
, '1' loc
, RTRIM(SUBSTR(rp.GRANTEE, 1, 10)) || ' on dvlp has sys prvlg ' =
||
RTRIM(SUBSTR(rp.PRIVILEGE, 1, 40)) || ' admin =3D ' ||
rp.ADMIN_OPTION diff
from dba_sys_privs_at_db2.domain rp
where PRIVILEGE || ADMIN_OPTION not in
( select PRIVILEGE || ADMIN_OPTION
from dba_sys_privs p
where p.grantee =3D rp.grantee
)
and rp.grantee in ('SCHEMA_NAME')
UNION
select 'B' ord
, p.grantee usr
, '2' loc
, RTRIM(SUBSTR(p.GRANTEE, 1, 10)) || ' on prod has role ' ||
p.GRANTED_ROLE diff
from dba_role_privs p
where GRANTED_ROLE not in
( select GRANTED_ROLE
from dba_role_privs_at_db2.domain rp
where p.grantee =3D rp.grantee
)
and p.grantee in ('SCHEMA_NAME')
UNION
select 'C' ord
, rp.grantee usr
, '1' loc
, RTRIM(SUBSTR(rp.GRANTEE, 1, 10)) || ' on dvlp has role ' ||
rp.GRANTED_ROLE diff
from dba_role_privs_at_db2.domain rp
where GRANTED_ROLE not in
( select GRANTED_ROLE
from dba_role_privs p
where p.grantee =3D rp.grantee
)
and rp.grantee in ('SCHEMA_NAME')
UNION
select 'C' ord
, p.grantee usr
, '2' loc
, RTRIM(SUBSTR(p.GRANTEE, 1, 10)) || ' role ' ||
p.GRANTED_ROLE || ' on prod has' ||
' admin=3D' || p.ADMIN_OPTION || ' default=3D' || =
p.DEFAULT_ROLE diff
from dba_role_privs p
where ADMIN_OPTION || DEFAULT_ROLE <>
( select ADMIN_OPTION || DEFAULT_ROLE
from dba_role_privs_at_db2.domain rp
where p.grantee =3D rp.grantee
and p.granted_role =3D rp.granted_role
)
and p.grantee in ('SCHEMA_NAME')
UNION
select 'D' ord
, rp.grantee usr
, '1' loc
, RTRIM(SUBSTR(rp.GRANTEE, 1, 10)) || ' role ' ||
rp.GRANTED_ROLE || 'on dvlp has' ||
' admin=3D' || rp.ADMIN_OPTION || ' default=3D' || =
rp.DEFAULT_ROLE diff
from dba_role_privs_at_db2.domain rp
where ADMIN_OPTION || DEFAULT_ROLE <>
( select ADMIN_OPTION || DEFAULT_ROLE
from dba_role_privs p
where p.grantee =3D rp.grantee
and p.granted_role =3D rp.granted_role
)
and rp.grantee in ('SCHEMA_NAME')
UNION
select 'D' ord
, r.grantee usr
, '1' loc
, RTRIM(SUBSTR(r.GRANTEE, 1, 10)) || ' on prod was granted ' ||
s.GRANTEE || ' role with diff in ' ||
RTRIM(SUBSTR(s.PRIVILEGE, 1, 40)) || ' prvlg, admin=3D' ||
s.ADMIN_OPTION diff
from dba_sys_privs s
, dba_role_privs r
where s.grantee =3D r.granted_role
and s.PRIVILEGE || s.ADMIN_OPTION not in
( select rs.PRIVILEGE || rs.ADMIN_OPTION
from dba_sys_privs_at_db2.domain rs
where s.grantee =3D rs.grantee
)
and r.grantee in ('SCHEMA_NAME')
UNION
select 'E' ord
, t.grantor usr
, '2'||GRANTEE loc
, RTRIM(SUBSTR(t.GRANTOR, 1, 10)) || ' on prod gave ' ||
RTRIM(SUBSTR(t.GRANTEE, 1, 10)) || ' priv ' ||
RTRIM(SUBSTR(t.PRIVILEGE, 1, 40)) || ' for ' ||
RTRIM(SUBSTR(t.OWNER, 1, 10)) || '.' ||
RTRIM(SUBSTR(t.TABLE_NAME, 1, 20)) ||
DECODE(t.GRANTABLE, 'NO', ' non grantable', ' grantable') diff
from dba_tab_privs t
where OWNER || TABLE_NAME || GRANTEE || PRIVILEGE || GRANTABLE not in
( select OWNER || TABLE_NAME || GRANTEE || PRIVILEGE || =
GRANTABLE
from dba_tab_privs_at_db2.domain rt
where t.grantor =3D rt.grantor
)
and t.grantor in ('SCHEMA_NAME')
UNION
select 'E' ord
, rt.grantor usr
, '1'||GRANTEE loc
, RTRIM(SUBSTR(rt.GRANTOR, 1, 10)) || ' on dvlp gave ' ||
RTRIM(SUBSTR(rt.GRANTEE, 1, 10)) || ' priv ' ||
RTRIM(SUBSTR(rt.PRIVILEGE, 1, 40)) || ' for ' ||
RTRIM(SUBSTR(rt.OWNER, 1, 10)) || '.' ||
RTRIM(SUBSTR(rt.TABLE_NAME, 1, 20)) ||
DECODE(rt.GRANTABLE, 'NO', ' non grantable', ' grantable') =
diff
from dba_tab_privs_at_db2.domain rt
where OWNER || TABLE_NAME || GRANTEE || PRIVILEGE || GRANTABLE not in
( select OWNER || TABLE_NAME || GRANTEE || PRIVILEGE || =
GRANTABLE
from dba_tab_privs t
where t.grantor =3D rt.grantor
)
and rt.grantor in ('SCHEMA_NAME')
UNION
select 'F' ord
, t.grantee usr
, '2'||GRANTOR loc
, RTRIM(SUBSTR(t.GRANTEE, 1, 10)) || ' on prod got ' ||
RTRIM(SUBSTR(t.PRIVILEGE, 1, 40)) || ' priv for ' ||
RTRIM(SUBSTR(t.OWNER, 1, 10)) || '.' ||
RTRIM(SUBSTR(t.TABLE_NAME, 1, 20)) ||
DECODE(t.GRANTABLE, 'NO', ' non grantable', ' grantable') diff
from dba_tab_privs t
where OWNER || TABLE_NAME || GRANTOR || PRIVILEGE || GRANTABLE not in
Received on Thu Nov 16 2000 - 21:09:30 CST
 |
 |