can I compare db. schema against export or ddl file

Toad for Oracle
1

Hello forum,

I need to be able to compare db. ddl schema with some kind of ddl file

I was wondering if Toad allows to create ddl schema file and use it later to compare with the current schema in the database.

2

There are two windows in Toad that can do this. We have “Compare Schemas” which compares schema-related objects (tables, views, sequences, procedures, etc), and “Compare Databases” which compares non-schema-related objects (roles, tablespaces, profiles, etc).

Both of them support the use of a “snapshot file” which is an XML file that Toad can generate from the schema (or database). You can later compare the database or schema to this file. Files can be generated in schema/database compare or another set of windows “Generate Schema Script” or “Generate Database Script”.

3

Hello John, thank you for your advice.

It works for the most part.

For instance, difference in column length definition : multiplier VARCHAR2(18 BYTE) - is not getting noticed.

4

What do you mean “Not getting noticed”? Do you have BYTE in one DB (or the file) and CHAR in the other, and the Schema Compare isn’t picking up that difference? Or something else?

5

John, when I run comparison between xml file and live db. schema, it does not show the differences in column definition. I had about 10 tables which have the same column defined differently.

In ddl snapshot, it has multiplier VARCHAR2(10 BYTE) , in database - VARCHAR2(18 BYTE) .

So it does not ‘notice’ this difference.

6

What version of Toad are you using? I just tested this and it works OK for me. Here’s what I did:

create user compare identified by compare;

grant dba to compare;

create table compare.comp_tbl(col1 number,

multiplier varchar2(10 byte));

then opened generate schema script and created a schema snapshot file for the COMPARE schema.

after that, I ran this command:

alter table compare.comp_tbl modify

(multiplier varchar2(18 byte));

Finally, I ran the schema compare and compared the COMPARE schema to the XML file that I created, and the difference was found. It was noted in the “Objects which differ” section, under “Columns”. Sometimes people don’t notice it there because they are looking for “Tables”.