CODEWITHSUNDEEP
oracle-database
Nick Long
Nick Long

Reputation:

What is the easiest approach to synchronize and duplicate oracle db schema?

I find it hard to generate the dbscripts from TOAD. I get errors when executing the scripts things like looping chain of synonyms or certain statement not abel to exceute etc.

Is there any seamless way in said like connecting a remote oracle schema and just duplicate to my local environment?

And also do synchronization along the way?

Upvotes: 1

Views: 1246

Answers (3)

David Atkinson
David Atkinson

Reputation: 5899

Schema Compare for Oracle should be able to achieve this, as it's a tool dedicated specifically to solve this task.

If you want it to happen in the background, there's a command line that lets you achieve this.

Upvotes: 0

Gary Myers
Gary Myers

Reputation: 35401

If you are doing a one-off copy exp/imp (or expdp/impdp in newer versions) is best. If you are progressing changes from dev to test to prod, then you should be using formal source control, with SQL or SQL*Plus scripts.

Upvotes: 2

derobert
derobert

Reputation: 51137

Syncing an entire schema, data and all, is fairly easily done with exp and imp:

$ exp username/password@source-sid CONSISTENT=Y DIRECT=Y OWNER=schema FILE=schema.exp
$ ⋮ # some command(s) to nuke objects, see below
$ imp username/password@dest-sid FROMUSER=schema FILE=schema.exp

You can import into a different schema if you want by using TOUSER in the imp command.

You'll need to get rid of all the objects if they already exist before running imp. You can either write a quick script to drop them all (look at the user_objects view), or just drop the user with cascade and re-create the user.

There is probably a better way to do this, but this is quick to implement and it works.

Upvotes: 2

Related Questions