Home » Other » General » Structural differences between two different databases (10g, 10.2.0.4, windows server)
Structural differences between two different databases [message #586352] |
Wed, 05 June 2013 06:49 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
ishika_20
Messages: 339 Registered: December 2006 Location: delhi
|
Senior Member |
|
|
Dear All,
We have two databases, development & testing. Now, there are changes in both the database and we need to find out the differences between the structures.
Suggest me how to find out the structures between the servers with different IP.
Regards,
Ishika
|
|
|
|
|
Re: Structural differences between two different databases [message #586394 is a reply to message #586356] |
Thu, 06 June 2013 01:45 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
ishika_20
Messages: 339 Registered: December 2006 Location: delhi
|
Senior Member |
|
|
Dear All,
Thanks for your suggestions. But i haven't got the exact answers from you. Though I have created one script which will help others to understand and get proper solutions.
Below are my query -
create database link testlink_24_25
connect to system identified by oracle
using '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.30.25)(PORT=1521))(CONNECT_DATA=(SID=lvgitst2)))';
create database link testlink_25_24
connect to system identified by oracle
using '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.30.24)(PORT=1521))(CONNECT_DATA=(SID=lvgitrn)))'
------------------------------
in LVGITST2 but not in LVGITRN
------------------------------
create table lvgitrn as
select owner,table_name from dba_tables where owner in ('INS','CONFSYS','REINS','RNL','DW_REP') and table_name not like 'BIN%'
minus
select owner,table_name from dba_tables@testlink_25_24 where owner in ('INS','CONFSYS','REINS','RNL','DW_REP') and table_name not like 'BIN%'
select a.owner,a.table_name,column_name,column_id from dba_tab_columns a, dba_tables b
where a.OWNER=b.owner
and a.TABLE_NAME=b.table_name
and a.owner in ('INS','CONFSYS','REINS','RNL','DW_REP')
and a.table_name not like 'BIN%' and a.owner||a.table_name not in (select owner||table_name from lvgitrn)
minus
select a.owner,a.table_name,column_name,column_id from dba_tab_columns@testlink_25_24 a, dba_tables@testlink_25_24 b
where a.OWNER=b.owner
and a.TABLE_NAME=b.table_name
and a.owner in ('INS','CONFSYS','REINS','RNL','DW_REP')
and a.table_name not like 'BIN%' and a.owner||a.table_name not in (select owner||table_name from lvgitrn)
------------------------------
in LVGITRN but not in LVGITST2
------------------------------
create table lvgitst2 as
select owner,table_name from dba_tables where owner in ('INS','CONFSYS','REINS','RNL','DW_REP') and table_name not like 'BIN%'
minus
select owner,table_name from dba_tables@testlink_24_25 where owner in ('INS','CONFSYS','REINS','RNL','DW_REP') and table_name not like 'BIN%'
select a.owner,a.table_name,column_name,column_id from dba_tab_columns a, dba_tables b
where a.OWNER=b.owner
and a.TABLE_NAME=b.table_name
and a.owner in ('INS','CONFSYS','REINS','RNL','DW_REP')
and a.table_name not like 'BIN%' and a.owner||a.table_name not in (select owner||table_name from lvgitst2)
minus
select a.owner,a.table_name,column_name,column_id from dba_tab_columns@testlink_24_25 a, dba_tables@testlink_24_25 b
where a.OWNER=b.owner
and a.TABLE_NAME=b.table_name
and a.owner in ('INS','CONFSYS','REINS','RNL','DW_REP')
and a.table_name not like 'BIN%' and a.owner||a.table_name not in (select owner||table_name from lvgitst2)
Regards
Ishika
|
|
|
Re: Structural differences between two different databases [message #586398 is a reply to message #586394] |
Thu, 06 June 2013 02:08 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/102589.gif) |
Michel Cadot
Messages: 68653 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
Thanks for the feedback.
Some remarks:
- You just cover the tables and no other objects
- You didn't cover the cases where data types changed
- Take care that "owner||table_name" can be equal but "owner" and "table_name" are not; add a "chr(0)" between the 2 fields or simply select the 2 fields (see next point how to do it)
- Some tables can be named "BIN...", you exclude them, if you meant tables that are not in the recycle bin, it is useless: %_TABLES do not show these tables. If you want to be sure anyway better use "(table_name,owner) not in (select owner, object_name from dba_recyclebin)".
Regards
Michel
[Edit: typo]
[Updated on: Thu, 06 June 2013 07:14] Report message to a moderator
|
|
|
|
Goto Forum:
Current Time: Sun May 19 19:07:02 CDT 2024
|