Clever Geek Handbook

What is the easiest way to sync and duplicate oracle db schema?

I am having a hard time creating dbscripts from TOAD. I am getting errors when executing scripts like cyclic chain of synonyms or a specific statement, not abel for an exception, etc.

Is there any seamless way, for example to connect a remote oracle schema and just duplicate in my local environment?

And also sync along the way?

+1


source to share


3 answers


Synchronizing the entire schema, data and everything, is pretty easy 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 another schema if you like using TOUSER in the imp command.



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

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

+2


source


If you are making a one-time copy of exp / imp (or expdp / impdp in newer versions) this is best. If you are progressing from dev to test to prod, then you should use a formal source of control with SQL or SQL * Plus scripts.



+2


source


Schema comparison for Oracle should be able to achieve this as it is a tool specifically designed for this task.

If you want this to happen in the background, there is a command line that allows you to do this.

0


source






More articles:


All Articles