I want to take data from one schema schema1 and put it into schema2 this should be quite easy to do but i have searched through the oracle documentation and can't find a way to do it.
- Anybody can ask a question
- Anybody can answer
- The best answers are voted up and rise to the top
Firstly, make sure the target user/schema is already created. It may also temporarily need the
Create a directory object to hold the dump files and logs (make sure this exists on your filesystem):
Export the data, specifying the schema you wish to export (
You can also do this without creating an interim dumpfile by using the
Have you looked at the DBMS_METADATA option? http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_metada.htm#i1019414
This gives you the schema for objects. It is fairly easy to take the results of this and do what you want with it. An example to get the tables and views of a user would be:
Please note that this will not handle permissions. I am assuming from your interest that you are looking for grants to object that the source schema doesn't own. Normally moving permissions is a little more touchy since this relates to security. I don't have a good example of this.
Red Gate have a tool call Data Compare for Oracle that will help you.
The software can sync your two schemas for you, or you can generate a script and and run it through SQL Developer, Toad etc.
(Disclaimer: I work for Red Gate)