Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2003
    Posts
    9

    Unanswered: Copy the data from one db to another but perserving the table structure?

    Hi!

    I have an intersting problem. I want to move the data from one db to another. I do not want to recreate the tables though.

    I have a db, call it A. I want to move the data in it to db B. I do not want to recreate the tables in B. How do I do this? exp and imp works great if I delete the tables first (or drop and recreate the user) but if i want to preserve the table structure in B i dont think it can be done with exp and imp.

    To spice things up there are lots of fun foreign keys and sequences in B making the emptying of the database a hazzle.

    I wrote a script that drops all the users sequences, all the foreign keys and then deletes all the data in the tables in B. So far so good. But then I need a way to dump this data out of A.

    It feels a bit like im doing something wrong from the start. Or how would you do this?

    Thanks for your time.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1

    Re: Copy the data from one db to another but perserving the table structure?

    Originally posted by fjutt
    Hi!

    I have an intersting problem. I want to move the data from one db to another. I do not want to recreate the tables though.

    I have a db, call it A. I want to move the data in it to db B. I do not want to recreate the tables in B. How do I do this? exp and imp works great if I delete the tables first (or drop and recreate the user) but if i want to preserve the table structure in B i dont think it can be done with exp and imp.

    To spice things up there are lots of fun foreign keys and sequences in B making the emptying of the database a hazzle.

    I wrote a script that drops all the users sequences, all the foreign keys and then deletes all the data in the tables in B. So far so good. But then I need a way to dump this data out of A.

    It feels a bit like im doing something wrong from the start. Or how would you do this?

    Thanks for your time.
    1) Shutdown database A
    2) Use OS commands to copy all files into new directory tree structure
    3) Use editor of choice to create SQL file to start database B
    4) Start database A

  3. #3
    Join Date
    Oct 2003
    Posts
    3

    Re: Copy the data from one db to another but perserving the table structure?

    Originally posted by fjutt
    Hi!

    I have an intersting problem. I want to move the data from one db to another. I do not want to recreate the tables though.

    I have a db, call it A. I want to move the data in it to db B. I do not want to recreate the tables in B. How do I do this? exp and imp works great if I delete the tables first (or drop and recreate the user) but if i want to preserve the table structure in B i dont think it can be done with exp and imp.

    To spice things up there are lots of fun foreign keys and sequences in B making the emptying of the database a hazzle.

    I wrote a script that drops all the users sequences, all the foreign keys and then deletes all the data in the tables in B. So far so good. But then I need a way to dump this data out of A.

    It feels a bit like im doing something wrong from the start. Or how would you do this?

    Thanks for your time.
    1. backup the controlfile to trace.
    "alter database backup controlfile to trace;"
    This will be located in the $ORACLE_BASE/SID/udump directory (pay attention to the time you created)

    2. Shutdown the database immediate or transactional
    3. Edit the trace file you created and remove all commented lines at the beginning.
    4. Add the word SET in front of the DATABASE on the first name and rename the database (SID) to a new name. (I like to do this just so the users don't accidently connect to production thinking they are on test.)
    5. Change the NORESETLOGS to RESETLOGS.
    6. Ensure all the file names are there and in the proper place.
    7. Comment out the "Recover" and "Open" statements
    8. Ensure that there is a new init.ora file for the new database.
    9. set the environment to the new database name and startup no mount;
    10. Run the file you created in step 4.
    11. If anything goes wrong - don't panic. Shutdown abort, fix the error and repeat steps 8 and 9 again.
    12. Run the following command:
    "select status from v$datafile;" --- This is NOT datafiles note the lack of the "s".
    13. All the files should be listed as "System" and "Recover".
    14. Run this command"
    "Alter database Open resetlogs;"
    15. Shutdown Normal
    16. Startup Normal
    17 back up newly created database.

    Have fun!

Posting Permissions

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