Home » RDBMS Server » Server Utilities » synonyms compilation error (win 10g)
synonyms compilation error [message #476424] Thu, 23 September 2010 04:29 Go to next message
ateeqrahman786
Messages: 52
Registered: December 2009
Location: Hyderabad,India
Member
Hi, i did an export from 8.1.7 and imported in 10g. All the tables were imported. I created all the users and gave them necessary grants. when i try to compile synonyms for this schema i imported,I get the following error in enterprise manager:
Failed to compile: ORA-00980: synonym translation is no longer valid


I cannot login to my application with these users even though they have all grants.
I receive the following error:

FRM-40735:PRE-FORM trigger raised unhandled exception ORA-00942

When i click ok, the screen disappears.
Re: synonyms compilation error [message #476428 is a reply to message #476424] Thu, 23 September 2010 04:46 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I think that you got it wrong. It is not a "synonym" that failed to compile (you don't compile synonyms!), but a form failed to compile because user (you are connected to in Forms Builder) doesn't see those synonyms any more. This is what ORA-00942 is talking about. Go to the PRE-FORM trigger and check all FROM clauses - you should be able to find the culprit.

Finally, simply recreate a missing synonym.
Re: synonyms compilation error [message #476429 is a reply to message #476424] Thu, 23 September 2010 04:49 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Do the owners of the tables and the owners of the synonyms have the same name in both DBs?
Cause that error tells you the synonym is pointing to something that no longer exists.
So either the table_name is wrong or the table_owner is wrong.
Query all_synonyms to check what they're currently pointing at.

[Updated on: Thu, 23 September 2010 04:56]

Report message to a moderator

Re: synonyms compilation error [message #476432 is a reply to message #476429] Thu, 23 September 2010 04:56 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
@LF - missing synonyms doesn't give ORA-00980
Re: synonyms compilation error [message #476434 is a reply to message #476429] Thu, 23 September 2010 05:03 Go to previous messageGo to next message
ateeqrahman786
Messages: 52
Registered: December 2009
Location: Hyderabad,India
Member
All the tables and owners of synonyms have same name in both databases. I have two super users who can log on without any problem. But the normal users have problem connecting? is it grants or synonyms the main culprits?
Re: synonyms compilation error [message #476436 is a reply to message #476434] Thu, 23 September 2010 05:14 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Check what all_synonyms says.
Check if you can directly query the table the synonym is pointing to from the owner of the synonym.
Are these public or private synonyms?
Re: synonyms compilation error [message #476437 is a reply to message #476434] Thu, 23 September 2010 05:15 Go to previous messageGo to next message
ramoradba
Messages: 2456
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
ORA-00980:	synonym translation is no longer valid

Cause:	The synonym used is based on a table, view, or synonym that no longer exists.

Action:	Replace the synonym with the name of the object it references or re-create the synonym so that it refers to a valid table, view, or synonym.


Your synonym may be accessing another table/view/synonym which it wont permission now ...
Re: synonyms compilation error [message #476444 is a reply to message #476436] Thu, 23 September 2010 05:46 Go to previous messageGo to next message
ateeqrahman786
Messages: 52
Registered: December 2009
Location: Hyderabad,India
Member
Check what all_synonyms says.
Check if you can directly query the table the synonym is pointing to from the owner of the synonym.
Are these public or private synonyms? 



I queried all_synonyms and found many entries for my tables. But i cant directly access them, its shows table or view does not exist. Most of them are private synonyms.
I can view the data in tables when i query like

select * from <schema_name>.<table_name>;

Please suggest

[Updated on: Thu, 23 September 2010 05:48]

Report message to a moderator

Re: synonyms compilation error [message #476445 is a reply to message #476444] Thu, 23 September 2010 05:49 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Did you try to drop a synonym that is invalid, and create a new one?
Re: synonyms compilation error [message #476446 is a reply to message #476445] Thu, 23 September 2010 05:54 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Show us exactly what you did. copy and paste from sqlplus the following:

show user;
SELECT * FROM user_synonyms WHERE synonym_name = <pick one>;
SELECT * FROM <user.table from previous query>;
SELECT * FROM <synonym you picked>;
Re: synonyms compilation error [message #476697 is a reply to message #476424] Fri, 24 September 2010 11:29 Go to previous messageGo to next message
ateeqrahman786
Messages: 52
Registered: December 2009
Location: Hyderabad,India
Member
We have an Hospital Management System Application. Creating a user involves two steps:
1) create a database level user i.e.Oracle user (in sqlplus)
2) Create an application level user (in Application)

Creating a database level user:
1)The script is run as SYSTEM user
2)A new user id and password,default tablespace and roles are provided.
3)A create sysnonym script generates required synonyms.
select distinct
   'CREATE SYNONYM  '||TABLE_NAME||' FOR '||USER||'.'||TABLE_NAME||';'
        FROM DBA_TAB_PRIVS
        WHERE GRANTEE = '&&GNPROLE';


The next step involves setting up a user in Application.

The problem i am facing after import is,
I can see all the users,roles in Application, but when i try to login to that application, i get error message.
FRM-40735:PRE-FORM trigger raised unhandled exception ORA-00942
Re: synonyms compilation error [message #476701 is a reply to message #476697] Fri, 24 September 2010 12:40 Go to previous message
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
Senior Member
ateeqrahman786 wrote on Fri, 24 September 2010 12:29

1)The script is run as SYSTEM user
2)A new user id and password,default tablespace and roles are provided.
3)A create sysnonym script generates required synonyms.
select distinct
   'CREATE SYNONYM  '||TABLE_NAME||' FOR '||USER||'.'||TABLE_NAME||';'
        FROM DBA_TAB_PRIVS
        WHERE GRANTEE = '&&GNPROLE';



What script is run as SYSTEM? The create synonym script? And do you actually execute the output from it or do you just run that command?

Running the output as system will not give access to the table to any user other than SYSTEM or a DBA. If you run it as the new user, you would not have access to DBA_TAB_PRIVS, so either way this explain to me why you get a table or view does not exist error.
Previous Topic: moving database using exp/imp
Next Topic: Data Pump Inquiry
Goto Forum:
  


Current Time: Thu Mar 28 10:37:16 CDT 2024