OraFAQ Forum: Server Administration » synonym returns no rows

Home » RDBMS Server » Server Administration » synonym returns no rows

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

synonym returns no rows [message #173041]

Fri, 19 May 2006 02:13

dbasif
Messages: 35
Registered: March 2006
Location: Bangalore, India

Member

Hi ,

i have created two private synonym called usr1.s1 & usr1.s2 for
two tables ie usr2.t1 and usr2.t2 and granted all priviledge on this table to users usr1 and urs2, but one synonym like s2 returns zero records, please help me out , is there any priviledge to be assigned.

Regards
Mohammad Asif

Report message to a moderator

Re: synonym returns no rows [message #173141 is a reply to message #173041]

Fri, 19 May 2006 10:49

smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida

Senior Member

Gotta provide more info, show us your complete session where you create and run everything.

Report message to a moderator

Re: synonym returns no rows [message #173146 is a reply to message #173041]

Fri, 19 May 2006 11:31

rkl1
Messages: 97
Registered: June 2005

Member

As you mentioned the tables t1 and t2 are under usr2 schema and since privileges have been granted, then connecting as usr1:
sql> select 'x' from usr2.t1 where rownum <=1;

would show you one x. Now if that works out then we may go with synonym:

sql>create synonym s1 for usr2.t1;

now you may run the query:

sql>select * from s1;

Which will be resolved by Oracle as select * from usr2.t1;

thanks.

Report message to a moderator

Re: synonym returns no rows [message #173181 is a reply to message #173146]

Sat, 20 May 2006 00:00

dbasif
Messages: 35
Registered: March 2006
Location: Bangalore, India

Member

Ya RK

for one table it returns x but for the table i mentioned it returns null, but what may be the problem, what needs to be checked as the table contains rows.

Regards
Asif

Report message to a moderator

Re: synonym returns no rows [message #173198 is a reply to message #173181]

Sat, 20 May 2006 02:46

dbasif
Messages: 35
Registered: March 2006
Location: Bangalore, India

Member

select table also returns zero rows from usr1 schema.

ie select * from usr2.tab2

Report message to a moderator

Re: synonym returns no rows [message #173231 is a reply to message #173198]

Sat, 20 May 2006 11:28

Littlefoot
Messages: 21818
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

If both "SELECT * FROM table2" and "SELECT * FROM synonym2" return no rows, the conclusion is simple: table2 is empty, so how can you expect to select something from its synonym?

Report message to a moderator

Re: synonym returns no rows [message #173328 is a reply to message #173231]

Sun, 21 May 2006 22:51

dbasif
Messages: 35
Registered: March 2006
Location: Bangalore, India

Member

Sorry i mean usr1.t2 returns no rows but usr2.t2 has data,
but when i create other table like
create table usr2.tmp as select * from usr2.t2, and want to access from us1 schema, like usr1.tmp records are coming.
i am really shocked .....

Regards
Asif

Report message to a moderator

Previous Topic:	Server
Next Topic:	Please Help me in Fine Grain Auditing (FGA)......How to enable it

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Sep 20 11:36:54 CDT 2024