OraFAQ Forum: Server Utilities » How do I copy just the data from one db to another?

Home » RDBMS Server » Server Utilities » How do I copy just the data from one db to another?

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

How do I copy just the data from one db to another? [message #74547]

Wed, 05 January 2005 04:38

Patrick Ferguson
Messages: 4
Registered: January 2005

Junior Member

Thanks for taking a look. I'm between a rock and a hard place.

I know how to do a full database copy, that's pretty easy. And I know how to create control files and load them via sql loader. But my problem is I need to find a way to write a script which will create control files out of all of the tables in a specific database. Then I need a script which will turn around and load them all into another database. I plan on dropping the constraints on the destination database so the load will go quickly and smoothly. Is this easy to do and I just can't figure it out? Please someone help me, I am in a seriously pressure situation where I need this done quickly. I will name my first born after you if you can help me solve this problem.

Posting on here is fine, but if you could also e-mail me directly at fergp25@gmail.com would help me out a ton.

Thanks,

Patrick

Report message to a moderator

Re: How do I copy just the data from one db to another? [message #74548 is a reply to message #74547]

Wed, 05 January 2005 05:16

Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

SQL*loader is used only to load data from a text file ( text file generated from other database sources).
If you already have the data inside an oracle database and you want that to be copied to another oracle database
all you need to do is
export the sourceDB
precreate the users/tablespaces etc in targetDB.
import the data from sourceDB.

This is flexible.
There are many options here.

You can export/import the whole database
or
Just a particular user
or
a particular subset of tables
or
even a subset of table ( using a where clause)
or
Just table data / just indexes etc

Lookinto export and import options.

Report message to a moderator

Re: How do I copy just the data from one db to another? [message #74550 is a reply to message #74548]

Wed, 05 January 2005 06:20

Patrick Ferguson
Messages: 4
Registered: January 2005

Junior Member

Okay, I looked at the export and import stuff, seems to be what I need, but when I try to execute a simple export like this, it fails with a table not found and i KNOW the table exists. Is there something wrong with my syntax??

C:>exp userid/passwd@dbname tables=SET_CHANGE_TYPE_RFT file=set_ecode_ref.dmp

Export: Release 9.0.1.3.1 - Production on Wed Jan 5 10:25:25 2005

(c) Copyright 2001 Oracle Corporation. All rights reserved.

Connected to: Oracle8i Enterprise Edition Release 8.1.7.4.1 - Production
With the Partitioning option
JServer Release 8.1.7.4.1 - Production
EXP-00056: ORACLE error 942 encountered
ORA-00942: table or view does not exist
EXP-00000: Export terminated unsuccessfully

Report message to a moderator

Re: How do I copy just the data from one db to another? [message #74551 is a reply to message #74550]

Wed, 05 January 2005 06:36

Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

You are using a 9.0.1.3.1 version of EXP to talk ( export) with an 8i database.
It will not work as such.

Option 1:

Install 9i export views in the 8i database by running
the $ORACLE_HOME/rdbms/admin/catexp.sql from the 9i machine.
You may not want to do this.

Option 2:

Use the lower version of the exp/imp to do the export
and use the same lower version to do the import.

from machine installed with oracle 8i
do the export.
create tnsentries in tnsnames.ora to talk to any remote database ( higher version) .
now import this dmp file using the same lower version of IMP utility.

Report message to a moderator

Re: How do I copy just the data from one db to another? [message #74552 is a reply to message #74551]

Wed, 05 January 2005 06:43

Patrick Ferguson
Messages: 4
Registered: January 2005

Junior Member

Thanks, I'll try that, if it doesn't work I'm just going to shoot myself in the face so I don't have to worry about it.

Report message to a moderator

Previous Topic:	Is export/import reliable for 600gb data?
Next Topic:	how to convert UTC timestamp to local timestamp

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Jul 03 01:28:32 CDT 2024