Home » SQL & PL/SQL » SQL & PL/SQL » CREATE SYNONYM
CREATE SYNONYM [message #277827] Wed, 31 October 2007 16:13 Go to next message
alexaoracle
Messages: 36
Registered: October 2007
Member
hi,
I have two db,
I just grant all privileges on tableA for the other db.

GRANT ALL ON tableA TO schema_b

Then I created a public synonym

CREATE PUBLIC SYNONYM tableA FOR schema_b.tableA

My question is why when I select * from user_synonyms this does not appear on schema1,
and when I am logged in the other db and execute
select * from tableA
why do I get the message

ORA-00980 synonym translation is no longer valid

If I use select * from schema1.tableA I got data
Re: CREATE SYNONYM [message #277828 is a reply to message #277827] Wed, 31 October 2007 16:21 Go to previous messageGo to next message
BlackSwan
Messages: 25047
Registered: January 2009
Location: SoCal
Senior Member
>I have two db,

2 DBs or 2 schemas

>I just grant all privileges on tableA for the other db.

Please start over from scratch using SQL*Plus.
Use CUT & PASTE plus <code tag> to post back here every SQL statement you issue & Oracle's response.

It is unclear from your post which user/schema is logged into Oracle & issuing which SQL statements.

>My question is why when I select * from user_synonyms this does not appear on schema1,
It is because user PUBLIC owns the synonym not "schema1".
Re: CREATE SYNONYM [message #277829 is a reply to message #277828] Wed, 31 October 2007 16:37 Go to previous messageGo to next message
alexaoracle
Messages: 36
Registered: October 2007
Member
ok, here it is again: (I am using a sql editor not sqlplus this moment, but the answer to the statement would be after >)

on SCHEMA1:

GRANT ALL ON table_cust TO OPS$SCHEMA2

>Grant succeeded

CREATE PUBLIC SYNONYM table_cust FOR OPS$SCHEMA2.table_cust

>Synonym created

select * from user_synonyms

>Here is not displayed the public synonym created

then on SCHEMA2:

SELECT * FROM table_cust

>ORA-00980 synonym translation is no longer valid

SELECT * FROM OPS$SCHEMA1.table_cust

>I get all..
Re: CREATE SYNONYM [message #277830 is a reply to message #277827] Wed, 31 October 2007 16:46 Go to previous messageGo to next message
BlackSwan
Messages: 25047
Registered: January 2009
Location: SoCal
Senior Member
>CREATE PUBLIC SYNONYM table_cust FOR OPS$SCHEMA2.table_cust
But the table is owned be "schema1"; not "OPS$SCHEMA2"

OPS$SCHEMA2 only comes into play when you are LOGGED directly on the that database server system & NOT using SQL*Net to access the DB

>then on SCHEMA2
I am unclear if or how the line above relates to "OPS$SCHEMA2"
because they are NOT the same user!

You're On Your Own (YOYO)!

[Updated on: Wed, 31 October 2007 16:48] by Moderator

Report message to a moderator

Re: CREATE SYNONYM [message #277831 is a reply to message #277827] Wed, 31 October 2007 16:48 Go to previous messageGo to next message
alexaoracle
Messages: 36
Registered: October 2007
Member
I saw my own error by posting:

CREATE PUBLIC SYNONYM table_cust FOR OPS$SCHEMA2.table_cust

it must be

CREATE PUBLIC SYNONYM table_cust FOR OPS$SCHEMA1.table_cust

but the still I dont see it in user_synonyms why?
Re: CREATE SYNONYM [message #277832 is a reply to message #277827] Wed, 31 October 2007 16:50 Go to previous messageGo to next message
BlackSwan
Messages: 25047
Registered: January 2009
Location: SoCal
Senior Member
>but the still I dont see it in user_synonyms why?
What part of "It is because user PUBLIC owns the synonym not schema1". do you NOT understand????????????????
Re: CREATE SYNONYM [message #277833 is a reply to message #277832] Wed, 31 October 2007 17:09 Go to previous messageGo to next message
alexaoracle
Messages: 36
Registered: October 2007
Member
Well, seriously, I dont understand that, and I wont feel bad for that.
Where can I see this? or perhaps you could explain me that better..

And for last, since I executed the code I posted before, and then correct my mistake, could this be the reason why I lost all my data in that table?

Thank you
Re: CREATE SYNONYM [message #277841 is a reply to message #277827] Wed, 31 October 2007 18:39 Go to previous messageGo to next message
BlackSwan
Messages: 25047
Registered: January 2009
Location: SoCal
Senior Member
select owner, synonym_name, table_owner from dba_synonyms where table_name = 'TABLE_CUST';
Re: CREATE SYNONYM [message #277878 is a reply to message #277841] Thu, 01 November 2007 00:57 Go to previous messageGo to next message
Littlefoot
Messages: 20899
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Here is a piece of SQL*Plus session which, I believe, does what you are looking for. First, connect to a database, create public synonym and grant privileges on underlying table to another user:
SQL> connect scott/tiger
Connected.
SQL> create public synonym syn_dept for dept;

Synonym created.

SQL> select * From all_synonyms where synonym_name = 'SYN_DEPT';

OWNER                          SYNONYM_NAME
------------------------------ ------------------------------
TABLE_OWNER                    TABLE_NAME
------------------------------ ------------------------------
DB_LINK
---------------------------------------------------------------------------

PUBLIC                         SYN_DEPT
SCOTT                          DEPT



SQL> grant all on dept to kriz;

Grant succeeded.

Now connect as another user and check whether it works or not:
SQL> connect kriz/kriz
Connected.
SQL> select * From all_synonyms where synonym_name = 'SYN_DEPT';

OWNER                          SYNONYM_NAME
------------------------------ ------------------------------
TABLE_OWNER                    TABLE_NAME
------------------------------ ------------------------------
DB_LINK
---------------------------------------------------------------------------

PUBLIC                         SYN_DEPT
SCOTT                          DEPT



SQL> select * from syn_dept;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

SQL>

Try to follow these steps and see what you did differently; perhaps you'll get the solution.
Re: CREATE SYNONYM [message #279079 is a reply to message #277878] Tue, 06 November 2007 19:21 Go to previous message
alexaoracle
Messages: 36
Registered: October 2007
Member
Thanks Smile
Previous Topic: select date range values
Next Topic: packages......
Goto Forum:
  


Current Time: Wed Dec 07 14:25:29 CST 2016

Total time taken to generate the page: 0.41908 seconds