Extract data from one database and import into another

[Glowworm27]

Hello gurus,

I have a need to copy all the data from a production database into a table in my development database.

I was thinking I could use sql plus to extract the data and sql plus to reimport the data into the other database.

I can Only query the production database, and the databases are physicaly in two different parts of the country. I can using my network query both databases, but I do not have an administravtive athourity on the production database. only Query.

Thanks


George Oakes
Check out this awsome .Net Resource!

http://www.learnvisualstudio.net

 

[KenCunningham]

Hi George. Is this not what Oracle's export/import facilities are made for? If your permissions are too tight, then perhaps a dump to a delimited file might be more appropriate, using a query. If this is what you wish to do, I know there are several threads in this and other Tek-Tips Oracle fora which will give you step-by-steps - try a keyword seach for, say, comma.

 

[SantaMufasa]

George,

What is Oracle exp's response/behaviour when you ask it to export your subject table?

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[bookouri]

If you only have "query" permissions on the production database you probably wont have export permissions either. If you have a copy of Access laying around you can create an ODBC connection to the table(s) you can query on the production database. Then use Access to link to the table(s) you need and create local tables in Access. Then you can get those tables into your local Oracle db. Or if you're only doing a table or two, as suggested by Ken above you just need to spool off a query to a delimited file. I like to us Access because I can link to both Oracle databases from inside Access and easily move data either way.

 

[williey]

Or you can have your DBA create a DB link to connect the 2 DBs. If you have no access to exp/imp at all, just use sqlplus to dump the data into a flat file and reload into your dev server using sqlldr.

The bottleneck will be your network then..