Home » Other » General » import failed when processing synonyms (oracle 11 g , windows os)
import failed when processing synonyms [message #610909] Tue, 25 March 2014 16:27 Go to next message
anncao
Messages: 87
Registered: August 2013
Member
If I excute : drop user ausername cascade; I understand all the objects created by the user will be dropped.
How about the public synonyms the user created(owned) and granted access to public?
Will that be dropped too? We assume so. but it seems not the case.

We got error when we want to using data dump import into our existing testing database from a production data export dump file.

Before we do the import, we excuted the drop Schema script that does the drop user cascade command.
Then we see the import process hang at the step at following step:

Processing object type DATABASE_EXPORT/SCHEMA/SYNONYM

Any idea what could cause that?
Thanks

Report message to a moderator

Re: import failed when processing synonyms [message #610910 is a reply to message #610909] Tue, 25 March 2014 16:32 Go to previous messageGo to next message
John Watson
Messages: 8960
Registered: January 2010
Location: Global Village
Senior Member
This Quote:
If I excute : drop user ausername cascade; I understand all the objects created by the user will be dropped.
is not correct: you will drop all objects in the user's schema, whether they were created by him or not. Oracle does not store who created an object, only the schema within which it exists. Public synonyms are in the PUBLIC schema, which you cannot drop.

Report message to a moderator

Re: import failed when processing synonyms [message #610911 is a reply to message #610910] Tue, 25 March 2014 16:44 Go to previous messageGo to next message
anncao
Messages: 87
Registered: August 2013
Member
Thank you, so we can only drop the public synonym using drop public synonyms statement in the case the user has the privilege to drop public synonyms, correct?

Also, I noticed when we created a public synonym for example by UserA, and grant select on the synonym to public, then I see when I query the dba_synonyms table, I see the owner is not only UserA, also Public and UserB? why is that, does it mean whenvever a public synonyms created, in dba_synonyms table, all the users have a record that says it is the owner of the public synonyms?

Thanks

[Updated on: Tue, 25 March 2014 16:55]

Report message to a moderator

Re: import failed when processing synonyms [message #610929 is a reply to message #610911] Wed, 26 March 2014 02:00 Go to previous messageGo to next message
John Watson
Messages: 8960
Registered: January 2010
Location: Global Village
Senior Member
What does this mean:Quote:
when we created a public synonym for example by UserA, and grant select on the synonym to public, then I see when I query the dba_synonyms table, I see the owner is not only UserA, also Public and User
If you post an example (please use SQL*Plus, and copy/paste with [code] tags) perhaps someone can explain.

Report message to a moderator

Re: import failed when processing synonyms [message #610944 is a reply to message #610929] Wed, 26 March 2014 04:33 Go to previous messageGo to next message
cookiemonster
Messages: 13958
Registered: September 2008
Location: Rainy Manchester
Senior Member
If the owner isn't public then it isn't a public synonym by definition.
Someone has created private synonyms for userA and userB.

Report message to a moderator

Re: import failed when processing synonyms [message #610990 is a reply to message #610944] Wed, 26 March 2014 10:11 Go to previous messageGo to next message
anncao
Messages: 87
Registered: August 2013
Member
Thanks, yes, I feel confused why in dba_synonyms table, the same synonym appear in three records, one owner is public, the other is UserA, the third is UserB.

The statement we create the synonyms is below and userA execute it.

create or replace public synonym PRODUCT for ma.product;
grant select on PRODUCT to public;

Thanks

[Updated on: Wed, 26 March 2014 10:12]

Report message to a moderator

Re: import failed when processing synonyms [message #610991 is a reply to message #610990] Wed, 26 March 2014 10:18 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/ and please read http://www.orafaq.com/forum/t/174502/102589/

Report message to a moderator

Re: import failed when processing synonyms [message #610992 is a reply to message #610990] Wed, 26 March 2014 10:42 Go to previous messageGo to next message
Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator

Read again cookiemonster's latest post, this is the explanation whatever you can think.

Report message to a moderator

Re: import failed when processing synonyms [message #610993 is a reply to message #610991] Wed, 26 March 2014 10:44 Go to previous messageGo to next message
cookiemonster
Messages: 13958
Registered: September 2008
Location: Rainy Manchester
Senior Member
That statement creates one synonym.
One synonym means one entry in dba_synonyms.
The other two must come from create or replace synonym commands (no use of public keyword).

Report message to a moderator

Re: import failed when processing synonyms [message #610994 is a reply to message #610993] Wed, 26 March 2014 10:49 Go to previous messageGo to next message
anncao
Messages: 87
Registered: August 2013
Member
Thank you cookiemaster, you explained very clearly, now I understand those two other synonyms were created by other create or replace synonym commands, I need to figure out the mystery where they did it. As far as I know now we only have the script that creates the public synonyms.

Thanks

Report message to a moderator

Re: import failed when processing synonyms [message #610995 is a reply to message #610994] Wed, 26 March 2014 10:51 Go to previous messageGo to next message
cookiemonster
Messages: 13958
Registered: September 2008
Location: Rainy Manchester
Senior Member
Those two synonyms aren't public ones by definition (they're private), so scripts that create public synonyms are irrelevant.

Report message to a moderator

Re: import failed when processing synonyms [message #610996 is a reply to message #610995] Wed, 26 March 2014 10:55 Go to previous messageGo to next message
anncao
Messages: 87
Registered: August 2013
Member
Hmm, thanks, got it, so the other two synonyms are private, we don't have a script that creates private synonyms, like using create or replace synonym
, I wonder there is a trigger or something in the database, when we create a table it may created automatically in background private synonyms for those two main users.

Report message to a moderator

Re: import failed when processing synonyms [message #610997 is a reply to message #610996] Wed, 26 March 2014 11:09 Go to previous messageGo to next message
cookiemonster
Messages: 13958
Registered: September 2008
Location: Rainy Manchester
Senior Member
It's conceivable there's a DDL trigger that does it. That's easy to check.

Report message to a moderator

Re: import failed when processing synonyms [message #610998 is a reply to message #610997] Wed, 26 March 2014 11:19 Go to previous messageGo to next message
anncao
Messages: 87
Registered: August 2013
Member
Thanks, any recommendations how to find this out?

Report message to a moderator

Re: import failed when processing synonyms [message #610999 is a reply to message #610998] Wed, 26 March 2014 12:04 Go to previous messageGo to next message
anncao
Messages: 87
Registered: August 2013
Member
Also back to my original question, when I query dba_synonyms, I found 3 records, one is for public, one is for UserA, the third is for UserB.
So I understand the synonym for UserA and UserB are private synonyms, but why when I log into oracle sql developer using UserA, I didn't see that synonym listed under Synonyms list. They only show up in the public synonyms list.

Report message to a moderator

Re: import failed when processing synonyms [message #611000 is a reply to message #610999] Wed, 26 March 2014 12:23 Go to previous messageGo to next message
Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator

Use SQL*Plus instead and query user_synonyms.

Report message to a moderator

Re: import failed when processing synonyms [message #611001 is a reply to message #611000] Wed, 26 March 2014 12:32 Go to previous messageGo to next message
anncao
Messages: 87
Registered: August 2013
Member
Tried SQL plus to query user_synonyms, it shows up! so it means it is a private synonym for this user too, but why it is not shown in the synonym tree in oracle SQL developer, it only shows in the public synonym tree in sql developer.

Report message to a moderator

Re: import failed when processing synonyms [message #611002 is a reply to message #610999] Wed, 26 March 2014 12:34 Go to previous messageGo to next message
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
Senior Member
We have no idea what you are seeing. Would PLEASE post what is asked with the command you gave and the output for when you see 3 rows?

Report message to a moderator

Re: import failed when processing synonyms [message #611003 is a reply to message #611001] Wed, 26 March 2014 12:35 Go to previous message
Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator

We can't see what you're doing and seeing.

Report message to a moderator

Previous Topic: Oracle Licensing
Next Topic: How to view current date audit records from DBA_AUDIT_TRAIL
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Mon Nov 25 22:48:59 CST 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.