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.
Printable View
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.
If it is not huge table use copy command from sqlplus .
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 ..
I prefer to EXPORT-->TRUNCATE-->IMPORT, it is cleaner.
But both are viable options.
MH
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...
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 .
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
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.