Copying data between two schemas
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 10 of 10

Thread: Copying data between two schemas

  1. #1
    Join Date
    Feb 2003
    Posts
    67

    Copying data between two schemas

    I have two identical schemas in two databases I need to copy data between them.

    Is there any script to export the data from first schema, delete the data from second schema and import first schema's data.

    I do this multiple times every day and if someone can suggest a script, it would save my life...

  2. #2
    Join Date
    May 2002
    Posts
    2,645
    UNIX? What an excellent learning opportunity you have to learn a bit of shell scripting. Look at UNIX Made Easy. You can look online for Livefire Labs. Learn how to fish.

  3. #3
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    Originally posted by stecal
    UNIX? What an excellent learning opportunity you have to learn a bit of shell scripting. Look at UNIX Made Easy. You can look online for Livefire Labs. Learn how to fish.
    Agree!

    If I was you Sureshot I would first read about how export/import works

  4. #4
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938

    Re: Copying data between two schemas

    Originally posted by sureshot
    I have two identical schemas in two databases I need to copy data between them.
    If they are identical why do you need to copy from one to the other :-)
    Oracle Certified Master
    Oracle Certified Professional 6i,8i,9i,10g,11g,12c
    email: ocp_9i@yahoo.com

  5. #5
    Join Date
    Jan 2001
    Posts
    642

    Talking

    Originally posted by stecal
    UNIX? What an excellent learning opportunity you have to learn a bit of shell scripting. Look at UNIX Made Easy. You can look online for Livefire Labs. Learn how to fish.
    Another oppurtunity to learn the oracle itself...
    Go ahead setup the dblink between the two, create a job on the second instance to periodically (whenever you need ) to truncate the tables and pull the data from the first db and populate...

    You can bill more though!!
    There is always a better way to do the things.

  6. #6
    Join Date
    Dec 2002
    Location
    Chennai, India
    Posts
    104
    Try with sql copy command to transfer data between databases

  7. #7
    Join Date
    Feb 2003
    Posts
    67
    I expected someone to post a script... Unix/korn shell not a big deal...

    If someone knows or already doing this, can you post the script?

    Or a metalink note?

  8. #8
    Join Date
    May 2002
    Posts
    2,645
    Is there any script to export the data from first schema, delete the data from second schema and import first schema's data.
    Yes, the one you write.

  9. #9
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Have you considered setting up replication between the databases? It would get around the issue of the script entirely.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  10. #10
    Join Date
    Feb 2003
    Posts
    67
    Has anyone used DBMS_RECTFIER_DIFF package? It is used in replication, but it can be used to synchronize two database tables/schemas.

    Any help on how to use OEM change manager for this job is appreciated.
    Last edited by sureshot; 08-13-2003 at 07:01 PM.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width