DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: copying production data to a development table

  1. #1
    Join Date
    Jan 2001
    Posts
    138
    We have a custom table in both development and production databases. We need to refresh the table in dev with production data. How do we accomplish this? Thanks.

  2. #2
    Join Date
    Aug 2001
    Posts
    267
    If it is not huge table use copy command from sqlplus .
    Raghu

  3. #3
    Join Date
    Aug 2001
    Posts
    267
    if it is one time refresh use copy

    syntax:
    COPY {FROM database | TO database | FROM database TO database}
    {APPEND|CREATE|INSERT|REPLACE} destination_table
    [(column, column, column, ...)] USING query

    where database has the following syntax:
    username[/password]@connect_identifier


    If it is to be refreshed in interval only way is through snapshots ..
    Raghu

  4. #4
    Join Date
    Jan 2001
    Posts
    3,134
    I prefer to EXPORT-->TRUNCATE-->IMPORT, it is cleaner.
    But both are viable options.

    MH
    I remember when this place was cool.

  5. #5
    Join Date
    Feb 2000
    Location
    Washington DC
    Posts
    1,843
    I think EXPORT/IMPORT little time consuming approach than COPY command...(which is pretty quick). When I played with 120 million rows table COPY command gave me double the performance than EXPORT/IMPORT. I cancelled maintennace with EXPORT/IMPORT approach and followed Scripting COPY command for bigger tables...
    Reddy,Sam

  6. #6
    Join Date
    Aug 2001
    Posts
    267
    True . But it depends on network traffic . My case we got lot n/w traffic . So for bigger tables I use export/import since I only need to copy one file though network between machines , and import to local DB . Other wise copy is best and good method for me .
    Raghu

  7. #7
    Join Date
    Mar 2002
    Posts
    534
    Have a look at the transportable tablespace option. It MAY BE a good solution.
    1. Export the metadata
    2. Copy the datafiles to target system
    3. Import metadata into target system
    4. Done

    For more details see:
    http://download-east.oracle.com/docs...spor.htm#12010

  8. #8
    Join Date
    Oct 2000
    Posts
    250
    Hi pcotten,
    U also can use the duplicate/clone database method to do this.

    Basic Steps to clone
    1. backup the control file to trace (Check the USER_DUMP_DEST latest file)
    2. Shutdown developement database
    3. backup the database datafile including those init.ora
    4. amend the new control file and init.ora file
    5. if this is the Windows, u need to add a service name - ORADIM method
    6. startup the modified trace file


    But if the database is pretty small, I would suggest just use the Export and Import method. Personally, I feel Copy method is good if just few tables without constraint key.

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