Re: Comparing two schemas.

From: Cameron Kane <ckane_at_sqf.hp.com>
Date: Fri, 20 Aug 1999 14:02:29 +0100
Message-ID: <37BD51E5.FB3F926C@sqf.hp.com>

Hi there,

I've had to do a lot of this. One easy way which doesn't involve non-oracle products is to use the schema extraction functionality of imp, without actually importing any rows.

You first exp the instance (without exporting any rows), then imp it to produce a schema file (without importing any rows) - just to create the schema file on it's own. Here's an example.

Example:

einstein:oracle> exp

Export: Release 8.0.4.2.1 - Production on Fri Aug 20 14:1:13 1999

Username: hpbi_owner
Password:

Connected to: Oracle8 Enterprise Edition Release 8.0.4.2.1 - Production PL/SQL Release 8.0.4.2.1 - Production
Enter array fetch buffer size: 4096 > 4096

Export file: expdat.dmp > /tmp/db.dmp

(2)U(sers), or (3)T(ables): (2)U > 2

Export grants (yes/no): yes > no

Export table data (yes/no): yes > no

Compress extents (yes/no): yes > no

Export done in US7ASCII character set and US7ASCII NCHAR character set Note: table data (rows) will not be exported Note: grants on tables/views/sequences/roles will not be exported
. exporting foreign function library names for user HPBI_OWNER
. exporting object type definitions for user HPBI_OWNER
About to export HPBI_OWNER's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export HPBI_OWNER's tables via Conventional Path ...
<table schema information is exported here>
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting referential integrity constraints
. exporting triggers
. exporting posttables actions
. exporting snapshots
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
Export terminated successfully without warnings.

Once you have the database dump file /tmp/db.dmp, you can make a parfile like this (call it /tmp/get_schema.par):

USERID=<username/password>
FILE=/tmp/db.dmp

BUFFER=2048
IGNORE=Y
GRANTS=Y

FULL=Y
INDEXES=Y
ROWS=N
COMMIT=N
INDEXFILE=/tmp/schema.sql

You can then run (as Oracle, with your ORACLE_SID etc. set up correctly):

$ imp parfile=/tmp/get_schema.par
which will create /tmp/schema.sql, which contains the sql statements to create the database. Perform this on both schemas, and then compare the two files!

tmgn wrote:

> 
> It seems there is a tool called 'Schema Manager' from Quest Software
> (www.quests.com) which does these sort of things for you..
> 
> -Thiru
> 
> Tapan Trivedi wrote:
> 
> > Hey Gurus,
> >    I have a situation where I may have to compare two very similar
> > schemas and try to find the difference between them  not the data
> > differences but just if the schemas have changed or not . Does somebody
> > have a script that does the same ? Does somebody know where to find such
> > a script ??  Any suggestions, comments,experiences are welcome.
> >
> >                                         Thanks a lot in advance.
> >
> >                                                         Tapan

--
Regards,

Cameron Kane
Systems Support Engineer

Systems Support Team    		   Tel: +44 (0)131 331 7380
Telecom Systems Division                   Fax: +44 (0)131 331 7697
Hewlett-Packard Limited 		   
South Queensferry 		           HP Telnet : 313-2380
United Kingdom, EH30 9TG                   MailStop  : SQFMK6
-------------------------------------------------------------------

Received on Fri Aug 20 1999 - 08:02:29 CDT