CODEWITHSUNDEEP
sql-serveroracle-databasedatabase-schemardbms
Atul Bansal
Atul Bansal

Reputation: 151

Compare SQL Server Database Schema with Oracle Database Schema

in my development environment we support the application both on MSSQL Server as well as Oracle. The database schema of both of these RDBMS are same.

while development we found that the developer made a mistake and forgot to change the oracle database for the last 1 yr. therfore the oracle script is quite behind in term of schema from SQL Server schema script.

now the question is how i can compare the two RDBMS systems to find the difference and make the oracle script updated

Upvotes: 1

Views: 2059

Answers (1)

ThinkJet
ThinkJet

Reputation: 6735

If there are no track log from which it's possible to find and reproduce all changes applied to SQL Server since first detected inconsistency with Oracle version, or that changes was applied, but only partially, you really need to compare objects presented in both databases.
In this case setup a link between databases on any side and use system dictionary views to compare table structures and other objects to find differences and, possible, to generate script for Oracle scheme rollup.

If you want to act from MS SQL Server side:

  1. Install and configure Oracle Instant Client
  2. Install Oracle ODAC
  3. Follow Microsoft recomendations (64-bit version)
  4. Connect as any user with dba role (or use same Oracle schema where object resides) to Oracle from MS SQL database

If you want to act from Oracle Server side:

  1. Install and configure Oracle Database Gateway for SQL Server.

  2. Create database link to MS SQL Server.

After successful configuration you may join Information schema views on SQL Server side with Data dictionary views on Oracle side to find differences.

Of course there are many troubles at this way like different data types, but it gives a chance to automate at least part of work.

Upvotes: 1

Related Questions