| Solaris 9, 8.1.7 Oracle Financials - database link ora-980 [message #157182] |
Tue, 31 January 2006 16:43  |
nmacdannald
Messages: 460
Registered: July 2005
Location: Stockton, California - US...
|
Senior Member |
|
|
I created a linkuser on the target database. I Granted connect and object privleges, and a public synonym. I can select from the table on the target database.
I created a link object on the requesting database (passwords match). I created a public synonym. Tnsnames.ora file is okay.
I am getting 'ORA-00980: synonym translation is no longer valid' when trying to connect from the requesting database.
Any ideas?
Examples below:
On financials database:
CREATE USER LINKUSER IDENTIFIED BY <pw>
DEFAULT TABLESPACE USERS
TEMPORARY TABLESPACE TEMP
PROFILE DEFAULT ACCOUNT UNLOCK;
GRANT CONNECT TO LINKUSER;
CREATE ROLE LINKROLE1 NOT IDENTIFIED;
Now connect as apps on financials database.
GRANT SELECT ON GL.GL_SETS_OF_BOOKS TO LINKROLE1;
GRANT LINKROLE1 TO LINKUSER;
Now connect as LINKUSER on financials database:
select count(*) from gl.gl_sets_of_books;
COUNT(*)
----------
1
However....
On requesting database:
CREATE PUBLIC DATABASE LINK OFNDBLINK
CONNECT TO LINKUSER
IDENTIFIED BY <pw>
USING 'ofnt';
CREATE PUBLIC SYNONYM GL_SETS_OF_BOOKS FOR GL_SETS_OF_BOOKS@OFNDBLINK.CC.CA.US;
select count(*) from gl_sets_of_books;
*
ERROR at line 1:
ORA-00980: synonym translation is no longer valid
What am I missing?
Neil.
[Updated on: Tue, 31 January 2006 16:45] Report message to a moderator |
|
|
|
|
|
| Re: Solaris 9, 8.1.7 Oracle Financials - database link ora-980 [message #157335 is a reply to message #157182] |
Wed, 01 February 2006 12:17  |
nmacdannald
Messages: 460
Registered: July 2005
Location: Stockton, California - US...
|
Senior Member |
|
|
On the database that has the link object(os2t):
OS2T> CREATE PUBLIC SYNONYM GL_SETS_OF_BOOKS FOR GL_SETS_OF_BOOKS@OFNDBLINK;
Synonym created.
OS2T> desc gl_sets_of_books
ERROR:
ORA-04043: object "GL"."GL_SETS_OF_BOOKS" does not exist
******
On the database that has the link user (ofnt):
OFNT> desc gl_sets_of_books
Name Null? Type
----------------------------------------- -------- ----------------------------
SET_OF_BOOKS_ID NOT NULL NUMBER(15)
NAME NOT NULL VARCHAR2(30)
SHORT_NAME NOT NULL VARCHAR2(20)
...
[Updated on: Wed, 01 February 2006 12:19] Report message to a moderator |
|
|
|
| How to create a database link [message #163921 is a reply to message #157182] |
Mon, 20 March 2006 17:28 |
nmacdannald
Messages: 460
Registered: July 2005
Location: Stockton, California - US...
|
Senior Member |
|
|
Database links.
Okay, here is the deal. I just spent two days reading about something that a person can explain in two minutes.
Make sure your tnsnames.ora have the same correct entries.
then lsnrctl reload
create a public database link on the local database.
As sys (or some user that can create dblinks):
CREATE PUBLIC DATABASE LINK xxDBLINK
CONNECT TO LINKUSER
IDENTIFIED BY <a valid password>
USING '<a service name in your tnsnames.ora file that points to the remote database>';
On the remote database, create a user for the link to use:
CREATE USER LINKUSER IDENTIFIED BY <the valid password above>
DEFAULT TABLESPACE USERS
TEMPORARY TABLESPACE TEMP
PROFILE <an appropriate profile>
ACCOUNT UNLOCK;
GRANT CONNECT TO LINKUSER;
or perhaps
grant create session to linkuser;
grant object priveleges to select, and/or update, and/or delete, and/or insert for the remote table to the new linkuser.
grant select on scott.<a table>@xxDBLINK;
create a public synonym
create public synonym <tablename> for scott.<tablename>@xxDBLINK;
Then on your local database
select count(*) from <tablename>
Unless I missed something. This does work for Solaris 9 Oracle 10.2.0.1. to Linux Oracle 10.1.0.3. Watch out for firewalls or anything that would stop the two databases from seeing each other.
Happy linking!
Neil.
|
|
|
|
) 1 Vote
] 
Blog
Search
Help
Members
Register
Login
Home
