upgrading a schema

[tima...@hotmail.com]

Hi,

I have an application using Oracle with a large (100+) number of
tables. Are there any free/open source tools that can ease the process
of updating the schema.

Specifically I would like to compare a source and a target, determine
the differences and the best way to update the target to be equivalent
to the source - tables, columns, views, etc.

thanks

[Matthias Hoys]

<tima...@hotmail.com> wrote in message
news:1158610383....@k70g2000cwa.googlegroups.com...

You can do that with third-party tools like TOAD - it can even generate a
script with the differences. Maybe you can do it with Oracle's free SQL
Developer, don't know about that. I believe Enteprise Manager with the
Change packs can also do it.

Matthias

[DA Morgan]

The capability is built into the database in the DBMS_RECTIFIER_DIFF
package.

A demo can be found in Morgan's Library at www.psoug.org.
--
Daniel Morgan
University of Washington
Puget Sound Oracle Users Group

[davdonin]

DA Morgan wrote:
>
> The capability is built into the database in the DBMS_RECTIFIER_DIFF
> package.

Daniel, the author is looking to compare schemas, not data I think.

I have not come across a free product, which does this.

Quest has Schema Manager product, which allows to compare
schemas/baselines. It also allows to manage data. See
http://www.quest.com/schema_manager/ for more details.

Oracle Enterprice Manager has Change Management Pack, which allows to
do the same. For more details please refer to Enterprise Manager
documentation.

Denis

[sualeh...@gmail.com]

The free, open-source SchemaCrawler tool will compare the schema and
data. SchemaCrawler outputs details of your schema (tables, views,
procedures, and more) in a diff-able plain-text format (text, CSV, or
XHTML). SchemaCrawler can also output data (including CLOBs and BLOBs)
in the same plain-text formats. You can use a standard diff program to
diff the current output with a reference version of the output.
SchemaCrawler can be run either from the command line, or as an ant
task. A lot of examples are available with the download to help you get
started.

SchemaCrawler is free, open-source, cross-platform (operating system
and database) tool, written in Java, that is available at SourceForge:
http://schemacrawler.sourceforge.net/
You will need to provide a JDBC driver for your database. No other
third-party libraries are required.

Sualeh Fatehi.

[DA Morgan]

Then how about:

SELECT object_name
FROM user_objects
INTERSECT
SELECT object_name
FROM user_objects@remote;

SELECT object_name
FROM user_objects
MINUS
SELECT object_name
FROM user_objects@remote;

and again with USER_TAB_COLUMNS.

[tima...@hotmail.com]

How does running diff on the two outputs help me generate the DDL
statements to bring the target to the same level as the source?

Without that there is not much value - I could write the same program
in about 20 minutes.

[gazzag]

tima...@hotmail.com wrote:
> How does running diff on the two outputs help me generate the DDL
> statements to bring the target to the same level as the source?
>
> Without that there is not much value - I could write the same program
> in about 20 minutes.

How does not providing your version of Oracle allow us to help you
effectively? ;-)

Depending on your answer to the above, check out the DBMS_METADATA
package:

http://download-uk.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_metada.htm#i1015856