Home » RDBMS Server » Networking and Gateways » create table xx_da as select * from table@2nd database not work
create table xx_da as select * from table@2nd database not work [message #247943] Wed, 27 June 2007 10:50 Go to next message
reyazan
Messages: 53
Registered: February 2005
Member
i have a 2 database . both on windows on different machine.
i run the following command in sqlplus

create table xxda as select * from username.tablename@database2

but it does not work.
plz tell what is the problem
Re: create table xx_da as select * from table@2nd database not work [message #247952 is a reply to message #247943] Wed, 27 June 2007 11:07 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You have to configure a listener, create network configuration file and create a database link.

Regards
Michel
Re: create table xx_da as select * from table@2nd database not work [message #248030 is a reply to message #247952] Wed, 27 June 2007 17:29 Go to previous messageGo to next message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
Quote:
i have a 2 database . both on windows on different machine.


Create the service from one database to another from where you want to pull data.

or you can also use DBLINK like this.

CREATE DATABASE LINK DEV01_MYDB
     CONNECT TO  oracle
     IDENTIFIED BY  oracle
     USING 'SECONDDB.abc.xyz.com';

SQL> show parameter service_names
service_names        string      SECONDDB.abc.xyz.com



then check whaeter its work or not

simple by connecting

and before running your querry set this parameter false;
SQL>ALTER SESSION SET GLOBAL_NAMES=FALSE;

and then 
insert into abc
select * from abc@DEV01_MYDB


Re: create table xx_da as select * from table@2nd database not work [message #250236 is a reply to message #248030] Mon, 09 July 2007 04:10 Go to previous messageGo to next message
pra_dbaquery
Messages: 6
Registered: June 2007
Location: Jamnagar
Junior Member
first of all u have to create the tns entry from db1 to db2.

Check the connection from command prompt using TNSPING command.

U just create the db link that will lead to db2.

then check the db link using below query.

select * from global_name@(your dblink name).

when u execute this query it will return the instance name of target server

suppose here is db2.

then u create the table.

if u face any more problems u just tell me i will guide u
Re: create table xx_da as select * from table@2nd database not work [message #250318 is a reply to message #250236] Mon, 09 July 2007 11:05 Go to previous message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
I guess he got solution Laughing
Previous Topic: is this enough to build up my tnsnames.ora ?
Next Topic: DB LINK CONNECTION FAILURE
Goto Forum:
  


Current Time: Thu Dec 08 12:11:50 CST 2016

Total time taken to generate the page: 0.11988 seconds