| synonyms compilation error [message #476424] |
Thu, 23 September 2010 04:29  |
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 #476429 is a reply to message #476424] |
Thu, 23 September 2010 04:49  |
cookiemonster
Messages: 13961
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 #476434 is a reply to message #476429] |
Thu, 23 September 2010 05:03 |
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 |
cookiemonster
Messages: 13961
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 #476444 is a reply to message #476436] |
Thu, 23 September 2010 05:46 |
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 #476446 is a reply to message #476445] |
Thu, 23 September 2010 05:54 |
cookiemonster
Messages: 13961
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 |
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 |
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.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
