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

Scott,

Some other things to check. Where is you TNSNAMES.ORA file? And do you have TNS_ADMIN set to find it? If so, that's why you can resolve the service name just fine with SQL*Plus. But the Oracle database is expecting this file to be in $ORACLE_HOME/network/admin. Is the TNSNAMES.ORA file in that directory configured properly? If your TNS_ADMIN points to another directory, then in $ORACLE_HOME/network/admin create a link to the TNSNAMES file in that directory.

HTH,
Brian

Scott Mattes wrote:
>
> Brian,
> I can't use the exact settings, would have to kill you and the world
> if I did, but this is what I did
>
> Using SQL/PLUS I put in userid=ME, password=******, host string=xyz
> and got logged onto the remote system. So I know 'xyz' is there and I
> can log on. So I get out of SQL/PLUS.
>
> Using SQL/PLUS again I log into my local DB and then I did
>
> CREATE DATABASE LINK my_db_link
> CONNECT TO ME
> IDENTIFIED BY ******
> USING 'xyz';
>
> I then did my insert as such
>
> INSERT INTO a_table SELECT * FROM a_table_at_my_db_link;
>
> which gives the "ORA-12154: TNS:could not resolve service name" msg.
>
> and
>
> INSERT INTO a_table SELECT * FROM a_table_at_xyz;
>
> gives the same thing.
>
> Is there some setting, or service, or grant, that the remote DB has
> to be running to allow this to work?
>
> Thank you for your time.
>
> 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 Thu Aug 30 2001 - 07:49:08 CDT