Copy tables between servers, dbs 4

[VBAHole22]

How would I go about copying a table from one server to another server. The tables are in different databases. I suck at SQL PLus, I usually use Aqua Data Center.

I know I need to use an INsert Into statement but how do you specifiy the different servers?

Do I have to have a middle step, like ASCII?

Could I do it using Oracle9i's interface? Where?

 

[SantaMufasa]

VBA,

Absolutely, positively, the safest and "best" method to duplicate a table from one server to another is (IMHO) "exp" (Oracle export) from the source server, then "imp" (Oracle import) into the targe server.

Since you sound "new" to the Oracle world, I'll mention that both "exp" and "imp" are Oracle utilities, which you exectute from your o/s prompt. To see what parameters you can provide for each command, you can first issue this command (from your o/s prompt):

emp help=y
or
imp help=y

These commands give full disclosure of the types of parameters you can specify on an "emp" or "imp" command line. If you would like syntax help with sample "emp" and "imp" commands, please post again (preferrably in a new thread since that is a slightly different topic from this question.)

Mufasa
(aka Dave of Sandy, Utah, USA @ 21:04 (21Jan04) GMT, 14:04 (21Jan04) Mountain Time)

 

[dsanchez2]

If both databases are in the same machine, or machines connected by a network, the best way to copy a table from one server to another is be using a database link. To do this you will need to:

(1) Configure and start the listener on the machine containing the table (the server).

(2) Configure the TNSNAME file on the machine where you be copying the table (the client).

(3) Set up a Database link on the "client". For example:

CREATE DATABASE LINK server1
CONNECT TO user1 IDENTIFIED BY userpwd
USING 'SERVER1';

Where:
"SERVER1" is the name you given to the server in the TNSNAME file.
"user1" is an user that owns the table and "userpwd" is the password of the user.

(4) Having the Database link you can do the following:

CREATE TABLE mytable
AS SELECT * FROM mytable@SERVER1;

Where "SERVER1" is the name you gave to the Database link.

 

[SantaMufasa]

DSanchez,

I must differ. As much as I depend upon, and enjoy, database links, using them to reproduce tables is a weak proposition. (In fact, database links are precisely why I said in my post, above, that 'Absolutely, positively, the safest and "best" method to duplicate a table ... is "exp"... and "imp".')

When you "CREATE TABLE mytable AS SELECT * FROM mytable@SERVER1;" as you suggest, you lose PRIMARY KEYs, FOREIGN KEYs, UNIQUE constraints, CHECK constraints (except for NOT NULL), and all indexes. That is a truckload of integrity and efficiency to either lose or have to re-create by hand. "exp" and "imp" preserve all of those information assets automatically. So, even though "CREATE TABLE AS SELECT..." is syntactically easy, there is way too much risk or manual followup required.

Mufasa
(aka Dave of Sandy, Utah, USA @ 06:31 (22Jan04) GMT, 23:31 (21Jan04) Mountain Time)