Hi there,

Our small company regularly alters tables, creates new views, adds
triggers and sequences, and these changes have to be reciprocated at
out (single) client site. Its hard to keep track, as people forget the
changes they have made, or don't bother to update files of changes.

What's the best way to compare 2 different schemas in a single
database? Something that will tell me things like:

1. This table T1 has a new fields...
2. This table T2 does not exist...
3. This table T3 has a different key...
4. This view is different.
5. This trigger is changed.
6. This sequence is new.
etc....

Thanks for any help,

Dean

A simple group by on the dba_objects or user_objects will give you the
counts by object_type which will let you see where new objects were
added or perhaps dropped.

You can write an outer join of the dba_tab_columns dictionary view to
identify differences in the column lists of tables and views. By using
an outer join or perhaps full outer join you can make missing and new
objects show up.

You can join dba_ind_columns to comapre indexes equal

etc...

How hard this is really depends on how fancy you want the
identification of the differences to be.

HTH -- Mark D Powell --

TOAD will do it for you

On 18 Feb 2005 13:40:20 -0800, "deanbrown3d (AT) yahoo (DOT) com"
<deanbrown3d (AT) yahoo (DOT) com> wrote:

The best way to deal with this is to have a proper development process
in place, source code control, and a beating for those people who
continue to change the database on the fly.


--
Sybrand Bakker, Senior Oracle DBA

<deanbrown3d (AT) yahoo (DOT) com> wrote

besides the suggestions in the other posts, consider implementing
database-level BEFORE CREATE, BEFORE ALTER, and BEFORE DROP triggers to
track or manage changes

a simple trigger that just requires a package variable to be set before
allowing the operation can serve as a reminder or safety catch in this type
of development environment. a more sophisticated trigger can track versions
and who did the changes

++ mcs

In addition to the suggestions in the previous post,
consider doing occasional export/import of your main schema into
baseline copies. A number of tools (TOAD, PowerDesigner, Oracle
ChangeManagment?...) will let you compare and produce reports on
comparisons between schemas (some reading the dump files directly).
Another bonus is that some of these tools will also generate scripts to
implement the upgrades to resynch the two schemas.
Depending on your Oracle version and the amount of activity on your
database, you could also try hunting/fishing in the redo log (using log
miner), looking for DDL transactions.

"deanbrown3d (AT) yahoo (DOT) com" <deanbrown3d (AT) yahoo (DOT) com> wrote in
news:1108762820.683244.94030 (AT) l41g2000cwc (DOT) googlegroups.com:

I tend to AUDIT all DDL statement to who changed what & when.

deanbrown3d (AT) yahoo (DOT) com wrote:

Institute a change management process and fire anyone that violates it.
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace 'x' with 'u' to respond)