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
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 #586355 is a reply to message #586352] Wed, 05 June 2013 06:53 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
That was discussed some time ago (feel free to search the board for more results).
Re: Structural differences between two different databases [message #586356 is a reply to message #586352] Wed, 05 June 2013 06:53 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
There are many tools that do this.
Given the number of times this has been asked, just Google for your question and you will get many hits.

Regards
Michel
Re: Structural differences between two different databases [message #586394 is a reply to message #586356] Thu, 06 June 2013 01:45 Go to previous messageGo to next message
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 messageGo to next message
Michel Cadot
Messages: 68625
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

Re: Structural differences between two different databases [message #586450 is a reply to message #586398] Thu, 06 June 2013 06:47 Go to previous message
ishika_20
Messages: 339
Registered: December 2006
Location: delhi
Senior Member
Dear Michel,

Requirement was like that. Application team wants to know the differences of table_name and its columns.

Thanks for your suggestion. Next time, i will keep in mind all the points which you mentioned.

Regards
Ishika
Previous Topic: Best questions to ask when creating someone a database.
Next Topic: Can valid object refer to invalid objects?
Goto Forum:
  


Current Time: Thu Mar 28 20:24:05 CDT 2024