Home » SQL & PL/SQL » SQL & PL/SQL » Synonym (Merged)
Synonym (Merged) [message #183723] Sat, 22 July 2006 13:03 Go to next message
novicess
Messages: 8
Registered: July 2006
Junior Member
Hi
I am a newbie, who would be grateful to get some help!
I've created a dblink;

CREATE DATABASE LINK SWI
CONNECT TO GROS IDENTIFIED BY BOY
USING 'API.I.K.COM';

Now I need to create a synonym and import a table to my own table in my own scheme 'upp' using this DB-link. How would you do this?

Re: synonym [message #183724 is a reply to message #183723] Sat, 22 July 2006 13:12 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
It appears you were noy paying close enough to the instructor of your class and you could benefit from reading the fine manuals found at
http://tahiti.oracle.com
especially the SQL Reference manual
http://download-west.oracle.com/docs/cd/B19306_01/server.102/b14200/toc.htm
You'd likely benefit from also reading the Concepts Manual.

HTH & YYMV
HAND!
Synonym ? [message #183725 is a reply to message #183723] Sat, 22 July 2006 13:15 Go to previous messageGo to next message
novicess
Messages: 8
Registered: July 2006
Junior Member
I am a newbie who would be grateful to get some help. I have created a dblink.

CREATE DATABASE LINK SIT
CONNECT TO GROS IDENTIFIED BY BOY
USING 'AU.IK.COM';

Now I to create a synonym to import a table GRO.TABLE to my own table in my own scheme 'vice'. How would you do this?


Re: Synonym ? [message #183726 is a reply to message #183725] Sat, 22 July 2006 13:24 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
Please do NOT multi-post redundant questions.
Re: synonym [message #183734 is a reply to message #183723] Sat, 22 July 2006 16:52 Go to previous messageGo to next message
William Robertson
Messages: 1640
Registered: August 2003
Location: London, UK
Senior Member
Do you need to create a synonym, import a table or use a DB link?

Maybe try one at a time.
Re: synonym [message #183741 is a reply to message #183723] Sun, 23 July 2006 02:58 Go to previous messageGo to next message
Peter D.
Messages: 19
Registered: June 2006
Location: Warsaw, Poland
Junior Member
Try this:

CREATE TABLE upp.<your_table>
AS
SELECT *
FROM <remote_table_name>@SWI;


Peter D.
Re: Synonym ? [message #183743 is a reply to message #183725] Sun, 23 July 2006 03:03 Go to previous messageGo to next message
Peter D.
Messages: 19
Registered: June 2006
Location: Warsaw, Poland
Junior Member
See this thread:

http://www.orafaq.com/forum/t/66680/87111/

Peter D.
Re: Synonym ? [message #183822 is a reply to message #183743] Mon, 24 July 2006 01:26 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Topics merged
Re: synonym [message #183887 is a reply to message #183741] Mon, 24 July 2006 04:41 Go to previous messageGo to next message
novicess
Messages: 8
Registered: July 2006
Junior Member
Thank you very much! This worked fine...

CREATE DATABASE LINK SWI
CONNECT TO G IDENTIFIED BY b
USING 'sw.k.k.com';

CREATE SYNONYM GRO
FOR BOOKS@SWI;

UNTIL i did
SELECT * FROM GRO; which give me the following Error ORA-00980: synonym translation is no longer valid

I need to select/import 2 tables from gro...
Re: synonym [message #183891 is a reply to message #183887] Mon, 24 July 2006 04:54 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
So, other than the fact that it doesn't work, it's worked fine
Cool

Q1: Is the DB link working.

Post the results of:
select sysdate from dual@SWI;


Q2: Is the table that you're looking for actually there at the other end of the link

Post the results of:
SELECT count(*) FROM books@SWI;
Re: synonym [message #183967 is a reply to message #183891] Mon, 24 July 2006 09:58 Go to previous messageGo to next message
novicess
Messages: 8
Registered: July 2006
Junior Member
thanks..
Q1 & Q2 gives me the following ERROR;
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
So it doesn't work.

But when I log in as user G identified by B instead , and run Q1 & Q2 with "books@AP" "select sysdate from dual@AP" the connection works fine...

I made some changes in tnsnames.
Re: synonym [message #183973 is a reply to message #183967] Mon, 24 July 2006 10:55 Go to previous message
William Robertson
Messages: 1640
Registered: August 2003
Location: London, UK
Senior Member
novicess wrote on Mon, 24 July 2006 09:58

ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

The error refers to the TNS connection on the server (in case you are checking the one on the client).

Essentially a TNS label defines a server, a port and an Oracle SID. For a datanase link the database must look up that definition in its tnsnames.ora file, and use it to contact the remote database. The error suggests that it found the server and the port and connected to a listener, only the listener did not recognise the service name (i.e. the SID specified in the TNS entry). Therefore make sure the specified server exists (ping it from the command line), the specified instance is running on it, and there is a listener listening on the specified port.

[Updated on: Mon, 24 July 2006 11:07]

Report message to a moderator

Previous Topic: Clear Temporary Tablespace
Next Topic: NUMERIC VALUES IN WORDS
Goto Forum:
  


Current Time: Fri Dec 02 16:26:28 CST 2016

Total time taken to generate the page: 0.13080 seconds