Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: copying data from one computer to another, without export

Re: copying data from one computer to another, without export

From: Niall Litchfield <n-litchfield_at_audit-commission.gov.uk>
Date: Wed, 29 Aug 2001 14:20:03 +0100
Message-ID: <3b8cec05$0$225$ed9e5944@reading.news.pipex.net>


You may also need to set some initialisation parameters (or choose your names carefully). The parameters in question are

  1. GLOBAL_NAMES (default value TRUE). This determines whether the db link name must be the same as the global database name for the remote database.
  2. DISTRIBUTED_TRANSACTIONS (default 0) Controls how many transactions running concurrently on the database can use remote tables.

So in summary you are probably best doing the following

  1. set DISTRIBUTED_TRANSACTIONS to a value greater than 0.
  2. create a tnsnames entry on your database server for the remote database
  3. bounce the local database
  4. issue CREATE DATABASE LINK <global_db_name of remote db> CONNECT TO <user_id> IDENTIFIED BY <password> USING <tnsnames entry created in step 2>;

You might wish to create a public database link if you plan on accessing this link from more than 1 oracle account.

HTH

--
Niall Litchfield
Oracle DBA
Audit Commission UK

"Brian Peasland" <oracle_dba_at_qwest.net> wrote in message
news:3B8CE35B.F7B721DB_at_qwest.net...

> Scott,
>
> You are getting closer. But you obviously still have an error. When you
> create a database link, you have to specify a service name with the
> USING clause of the CREATE DATABASE LINK command. For instance, let's
> suppose my create database command looks like:
>
> CREATE DATABASE my_db_link CONNECT TO my_user IDENTIFIED BY
> my_password USING 'remote_db';
>
> The service name I used is "remote_db". When you tried to use the
> database link, the system tried to resolve this service name into it's
> key components (host, port, protocol, sid). This information is
> typically defined in the TNSNAMES.ORA file found in
> $ORACLE_HOME/network/admin. So you'll need the "remote_db" entry in this
> file. It couldn't find this entry in that file, so you got the 12154
> error. Fire up Net8 Assistant and add it!! Then you db link should work.
>
> HTH,
> Brian
>
> Scott Mattes wrote:
> >
> > Brian,
> > Thank you, that seems to get me closer, but it doesn't work. I get
> > "0ra-12154: tns; could not resolve service name". I used the same
> > information that I put in the command to log on to the remote system and
> > that works.
> >
> > "Brian Peasland" <oracle_dba_at_qwest.net> wrote in message
> > news:3B8B92E7.98D30100_at_qwest.net...
> > > Scott,
> > >
> > > You are on the right track...
> > >
> > > First, you need to create a database link from your local database to
> > > your remote database. This is done as follows:
> > >
> > > CREATE DATABASE LINK link_name CONNECT TO userid IDENTIFIED BY
> > > password USING 'tns_entry';
> > >
> > > You choose the link_name which is meaningful to you. The
userid/password
> > > is the u/p to connect to the remote database. The tns_entry is an
entry
> > > in your TNSNAMES.ORA file which points to the remote database. Refer
to
> > > the Net8 Assistant for help setting that up.
> > >
> > > After you've got the link created, you can use it to insert into a
local
> > > table as follows:
> > >
> > > INSERT INTO local_table SELECT * FROM remote_table_at_link_name;
> > >
> > > That's all there is to it!
> > >
> > > HTH,
> > > Brian
> > >
> > > Scott Mattes wrote:
> > > >
> > > > I am working on a project where sometimes it would be handy to have
> > > > the DB local (like when my IP connection goes out, or theirs does).
I
> > > > have installed Oracle 8 Personal and created all the tables. This
> > > > helps quite a bit with compiling. Now I want to populate some of the
> > > > tables with data. I have the free TOAD and that will create insert
> > > > statements, but I thought that it would be easier, at least less
> > > > steps, to do something like
> > > >
> > > > insert into my_local_table values( select * from remote.schema.table
> > > > );
> > > >
> > > > My first try didn't succeed and I need to get back to work right
now,
> > > > but I thought that I would ask and see if I was at least on the
right
> > > > track.
> > > >
> > > > Any example would be greatly appreciated.
Received on Wed Aug 29 2001 - 08:20:03 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US