OraFAQ Forum: Networking and Gateways » create table xx_da as select * from table@2nd database not work

Home » RDBMS Server » Networking and Gateways » create table xx_da as select * from table@2nd database not work

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

create table xx_da as select * from table@2nd database not work [message #247943]

Wed, 27 June 2007 10:50

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

Report message to a moderator

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

Michel Cadot
Messages: 68729
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

You have to configure a listener, create network configuration file and create a database link.

Regards
Michel

Report message to a moderator

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

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

Report message to a moderator

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

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

Report message to a moderator

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

DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland

Senior Member

I guess he got solution Laughing

Report message to a moderator

Previous Topic:	is this enough to build up my tnsnames.ora ?
Next Topic:	DB LINK CONNECTION FAILURE

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Jan 09 10:22:47 CST 2025