Home » SQL & PL/SQL » SQL & PL/SQL » insert from remote server to client table (oracle 9i, oracle 10g)
insert from remote server to client table [message #329910] Fri, 27 June 2008 01:07 Go to next message
sdhanuka
Messages: 173
Registered: March 2008
Senior Member
hey everyone.

On my client machine runs oracle 9i.

I have created a table on the client side. I need to fetch the data from the server machine which has 10g and insert this data into the table created on the client side.

Once the connection takes place how do i insert the data from remote server views to a table on the client machine.

Do we have to use a db link??
Re: insert from remote server to client table [message #329932 is a reply to message #329910] Fri, 27 June 2008 01:45 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Do you have 1 or 2 databases?

Regards
Michel
Re: insert from remote server to client table [message #329933 is a reply to message #329932] Fri, 27 June 2008 01:48 Go to previous messageGo to next message
sdhanuka
Messages: 173
Registered: March 2008
Senior Member
the server machine has one database oracle 10g
and the client machine has one database oracle 9i
Re: insert from remote server to client table [message #329942 is a reply to message #329910] Fri, 27 June 2008 02:04 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You will need a Db Link.
Re: insert from remote server to client table [message #329944 is a reply to message #329942] Fri, 27 June 2008 02:11 Go to previous messageGo to next message
sdhanuka
Messages: 173
Registered: March 2008
Senior Member
alright, i just want to confirm

i have created a dblink that connects to the remote server.

I want to populate the table tb so i need to connect using my client machine user name and password and not the server machine username and password right?


Microsoft Windows XP [Version 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.

C:\Documents and Settings\Administrator>sqlplus test/test

SQL*Plus: Release 9.2.0.1.0 - Production on Fri Jun 27 12:41:32 2008

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production

SQL> insert into tb select tablespace_name from dba_data_files@db_10g;
insert into tb select tablespace_name from dba_data_files@db_10g
                                                          *
ERROR at line 1:
ORA-12535: TNS:operation timed out


SQL>


should i connect as the remote server host? in that case the table will not be identified

What to do?
Re: insert from remote server to client table [message #329950 is a reply to message #329944] Fri, 27 June 2008 02:21 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
What to do?

Fix the error.
You don't say us how you created your db link.
You don't say us if you can connect from your client to your server.
You don't say us if you can even the server...

ORA-12535: TNS:operation timed out
 *Cause: The requested operation could not be completed within the time out
 period.
 *Action: Look at the documentation on the secondary errors for possible
 remedy. See SQLNET.LOG to find secondary error if not provided explicitly.
 Turn on tracing to gather more information.

Regards
Michel
Re: insert from remote server to client table [message #329952 is a reply to message #329950] Fri, 27 June 2008 02:23 Go to previous messageGo to next message
sdhanuka
Messages: 173
Registered: March 2008
Senior Member
ok fixed


SQL> create table try as select tablespace_name from dba_data_files;

Table created.

SQL> insert into table try select tablespace_name from dba_data_files@db_10g;
insert into table try select tablespace_name from dba_data_files@db_10g
            *
ERROR at line 1:
ORA-00903: invalid table name


SQL> insert into try select tablespace_name from dba_data_files@db_10g;

4 rows created.

SQL> select * from try;

TABLESPACE_NAME
------------------------------
SYSTEM
UNDOTBS1
INDX
TOOLS
USERS
SYSTEM
UNDOTBS1
SYSAUX
USERS

9 rows selected.

SQL>



yes i can connect to the server from my machine

You don't say us if you can even the server...
?




[Updated on: Fri, 27 June 2008 02:27]

Report message to a moderator

Re: insert from remote server to client table [message #329954 is a reply to message #329910] Fri, 27 June 2008 02:24 Go to previous messageGo to next message
varu123
Messages: 754
Registered: October 2007
Senior Member
Have you set the entry for the remote server in the tnsnames file of the client?
Re: insert from remote server to client table [message #329955 is a reply to message #329910] Fri, 27 June 2008 02:25 Go to previous messageGo to next message
varu123
Messages: 754
Registered: October 2007
Senior Member
what was the error?
wrong IP??
Re: insert from remote server to client table [message #329956 is a reply to message #329955] Fri, 27 June 2008 02:29 Go to previous message
sdhanuka
Messages: 173
Registered: March 2008
Senior Member
i had not started the listener

Now it is connected from the client to server machine
Previous Topic: Sql 10% data only..
Next Topic: Result set
Goto Forum:
  


Current Time: Sun Dec 11 00:08:38 CST 2016

Total time taken to generate the page: 0.26106 seconds