Copy all table data from one schema to another
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Copy all table data from one schema to another

Hybrid View

  1. #1
    Join Date
    Apr 2009
    Posts
    17

    Copy all table data from one schema to another

    Hi all

    I have to move data from test environment to production.
    we have 2 schema (1.Dev and 2.Prod)
    All our datas are in "Dev" schema.
    Now we need to move it in to Prod schema..
    Tables are already existing in Prod schema..

    What I need to do is
    1)Just move all the data from Dev to Prod
    2)Delete all the data from Dev

    note : I dont want to use the command insert into prod.table_name select * from dev.table_name;
    reason is we have more than 500 tables in database. so I wanted to move all the table datas with out specifying the table names individually.

    Other details ==> Oracle 10g on windows server 2003 .Connecting oracle using Toad 9.1

    please helpme on this..

    Thanks in advance

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    export and import

  3. #3
    Join Date
    Apr 2009
    Posts
    17
    can u guide me how to use it exactly..

  4. #4
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    otn.oracle.com -> utilities guide

  5. #5
    Join Date
    Mar 2001
    Location
    India
    Posts
    57
    Hi,

    You can use CTAS (create table b as select from a)

    Thanks and regards,
    Gitesh Trivedi
    Last edited by davey23uk; 11-02-2009 at 03:09 PM.

  6. #6
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Quote Originally Posted by nishantprabha View Post
    note : I dont want to use the command insert into prod.table_name select * from dev.table_name;
    reason is we have more than 500 tables in database. so I wanted to move all the table datas with out specifying the table names individually.
    You don't have to.
    Just build a script by quering dba_tables where owner = 'Dev'
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

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