OraFAQ Forum: Server Administration

Home » RDBMS Server » Server Administration » Compare 2 Schema's

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Compare 2 Schema's [message #184173]

Tue, 25 July 2006 11:04

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

Mahesh Rajendran
Messages: 10707
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

ebrian
Messages: 2794
Registered: April 2006

Senior Member

If you have access to Change Manager, you may want to consider using it.

Report message to a moderator

Re: Compare 2 Schema's [message #184477 is a reply to message #184179]

Wed, 26 July 2006 13:51

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

Report message to a moderator

Re: Compare 2 Schema's [message #184479 is a reply to message #184477]

Wed, 26 July 2006 14:00

Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

Do those have '$' in name? May be lobindexes?
Post a few samples.
Check dba_* views.

Report message to a moderator

Re: Compare 2 Schema's [message #184482 is a reply to message #184479]

Wed, 26 July 2006 14:17

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

Report message to a moderator

Re: Compare 2 Schema's [message #184486 is a reply to message #184482]

Wed, 26 July 2006 14:55

Mahesh Rajendran
Messages: 10707
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?

Report message to a moderator

Re: Compare 2 Schema's [message #184492 is a reply to message #184486]

Wed, 26 July 2006 15:27

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 !

Report message to a moderator

Re: Compare 2 Schema's [message #226891 is a reply to message #184173]

Mon, 26 March 2007 19:40

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.

Report message to a moderator

Previous Topic:	Installing Database
Next Topic:	How to find all the infomation about Oracle 10g Bug?

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 25 00:49:50 CDT 2024