Home » RDBMS Server » Server Administration » Compare 2 Schema's
Compare 2 Schema's [message #184173] Tue, 25 July 2006 11:04 Go to next message
faiz_hyd
Messages: 294
Registered: February 2006
Senior Member
Hello

Can some body give some scripts or any steps to compare 2 schema's objects like tables, datatypes, sequences, views,(almost all except PACKAGES,PROCEDURE,FUNCTIONS,TYPE OBJECTS)

My aim to synchronize 2 schemas in different tables for example
between TEST database and Developement Database
user is same like scott available in both DB's

Any help using script or any tools is appreciated.


Regards

[Updated on: Tue, 25 July 2006 11:05]

Report message to a moderator

Re: Compare 2 Schema's [message #184174 is a reply to message #184173] Tue, 25 July 2006 11:09 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Look into dba_objects.

dba_objects will give you list of all objects.
all_tab_columns will give you list of all columns in all tables.
So it depnds on what granularity the comparison should be.
Please search the forum.
Several methods are already discussed like this
http://orafaq.com/node/59
Or using Oracle Change manager etc
I do not use TOAD. I beleive, toad has a built-in functionality too!.
And many custom built scripts are available all over the web.
http://asktom.oracle.com/pls/ask/f?p=4950:8:4674014385171550577::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:941629680330,

[Updated on: Tue, 25 July 2006 11:28]

Report message to a moderator

Re: Compare 2 Schema's [message #184179 is a reply to message #184173] Tue, 25 July 2006 11:26 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
If you have access to Change Manager, you may want to consider using it.
Re: Compare 2 Schema's [message #184477 is a reply to message #184179] Wed, 26 July 2006 13:51 Go to previous messageGo to next message
faiz_hyd
Messages: 294
Registered: February 2006
Senior Member
Hi,
Thanks for follow up,
I used Toad but strange things in the output script,
It is listing some constraints and Indexes saying to Drop them in the schema which i am comparing,
where as if i search for those Constraints or Indexes i couldn't fine

I used

user_constraints
User_indexes
all_cons_columns

Still in confusion from where Toad is getting those constraints/Indexes and Some Tables and Views also.


Thanks
Re: Compare 2 Schema's [message #184479 is a reply to message #184477] Wed, 26 July 2006 14:00 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Do those have '$' in name? May be lobindexes?
Post a few samples.
Check dba_* views.
Re: Compare 2 Schema's [message #184482 is a reply to message #184479] Wed, 26 July 2006 14:17 Go to previous messageGo to next message
faiz_hyd
Messages: 294
Registered: February 2006
Senior Member
Thanks Mahesh

I checked twice there is no '$' in the constraint /index/table name and as a example here it is , but after comparison Toad output gives 10 constaints/20 index and some views and some tables to be dropped to synchronize which i couldn't find, Still in confusion may be i am missing some thing then.
SQL> select * from user_indexes where index_name='XIF_NOT_TXNID';

no rows selected

SQL> select * from user_constraints where constraint_name='NN_USR_TMZ';

no rows selected
disconnect
conn system/xxxx@yyy

 select * from dba_constraints  where constraint_name='NN_USR_TMZ' and owner='R1APP60'

no rows selected

Thanks
Re: Compare 2 Schema's [message #184486 is a reply to message #184482] Wed, 26 July 2006 14:55 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Apparently, I have no idea on the behaviour of TOAD.
Are you sure you are checking the right schema?
icon14.gif  Re: Compare 2 Schema's [message #184492 is a reply to message #184486] Wed, 26 July 2006 15:27 Go to previous messageGo to next message
faiz_hyd
Messages: 294
Registered: February 2006
Senior Member
Thanks a Lot Mahesh for follow up,
I was checking correct schema but when copying from TOAD to text editor i don't how come space was coming (single character space)
this was giving problem, i used Text editor and did find and replace may be there space was there , Any how Thanks for Guiding
At last i was able to find it, and it was Toad i used to Compare Schema's.


Thanks Again !
Re: Compare 2 Schema's [message #226891 is a reply to message #184173] Mon, 26 March 2007 19:40 Go to previous message
rbedick
Messages: 2
Registered: March 2007
Junior Member
You might want to consider DB Side-By-Side (http://www.schematodoc.com). It lets you create xml snapshots of a database's metadata. You can then compare any two snapshots to identify differences in primary keys, fields (datatype, size, nullable, defaults), indexes, check constraints, foreign keys, triggers, views and stored procedures. You can compare two different databases or the same database at two different points in time.
Previous Topic: Installing Database
Next Topic: How to find all the infomation about Oracle 10g Bug?
Goto Forum:
  


Current Time: Sat Dec 03 20:02:50 CST 2016

Total time taken to generate the page: 0.09246 seconds