OraFAQ Forum: Server Administration » oracle user does not see some data

Home » RDBMS Server » Server Administration » oracle user does not see some data (Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 )

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

oracle user does not see some data [message #670048]

Thu, 31 May 2018 07:46

yk307
Messages: 3
Registered: May 2018

Junior Member

Hello this is the problem.
Oracle user do not see some data in some tables.
When I created another user ( using the same script) . New user able to see all data
Thanks
should I re-create all users with such problems

Thank you

Report message to a moderator

Re: oracle user does not see some data [message #670049 is a reply to message #670048]

Thu, 31 May 2018 07:59

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Welcome to this forum

Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read

Report message to a moderator

Re: oracle user does not see some data [message #670050 is a reply to message #670048]

Thu, 31 May 2018 08:00

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

We know nothing about your problem but as you seem to have a (simple) solution, apply it.

Report message to a moderator

Re: oracle user does not see some data [message #670051 is a reply to message #670048]

Thu, 31 May 2018 08:07

gazzag
Messages: 1119
Registered: November 2010
Location: Bedwas, UK

Senior Member

It would be better if you showed us what was happening. This means, start a SQL*Plus session, show us the commands that you used and the output you see. Copy and paste that here.

Report message to a moderator

Re: oracle user does not see some data [message #670052 is a reply to message #670051]

Thu, 31 May 2018 08:41

yk307
Messages: 3
Registered: May 2018

Junior Member

select MNT_PIDMA, count(*)
from MNT
where MNT_PIDMA is not null
group by MNT_PIDMA
having not exists
(select 'x'
from den
where den_pidma = MNT_PIDMA)
and count(*)>0;

the same query returns different results

user-1 ( created 5 years ago ) brings 0 rows

user-2 ( created 2 weeks ago , with the same creation script as user-1 ) brings 30 rows
Thanks

Report message to a moderator

Re: oracle user does not see some data [message #670053 is a reply to message #670052]

Thu, 31 May 2018 08:44

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Users do not hit the same tables and they have not the same data.

Please read How to use [code] tags and make your code easier to read.

Format your query, if you don't know how to do it, learn it using SQL Formatter

[Updated on: Thu, 31 May 2018 08:46]

Report message to a moderator

Re: oracle user does not see some data [message #670055 is a reply to message #670053]

Thu, 31 May 2018 09:15

yk307
Messages: 3
Registered: May 2018

Junior Member

Michel Thank you for answering , I will try to investigate

Report message to a moderator

Re: oracle user does not see some data [message #670056 is a reply to message #670055]

Thu, 31 May 2018 09:28

gazzag
Messages: 1119
Registered: November 2010
Location: Bedwas, UK

Senior Member

SQL> SELECT owner, table_name
     FROM dba_tables
     WHERE table_name IN ('DEN','MNT');

Report message to a moderator

Re: oracle user does not see some data [message #670057 is a reply to message #670056]

Thu, 31 May 2018 09:40

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

I'd rather use from each user:

set role all
select object_name, owner, object_type
from all_objects
where object_name in ('DEN','MNT')
order by 1, 2, 3
/

which will also cover the cases of views and synonyms and show the accessible objects from each account.

[Edit: typo]

[Updated on: Thu, 31 May 2018 10:09]

Report message to a moderator

Re: oracle user does not see some data [message #670058 is a reply to message #670057]

Thu, 31 May 2018 09:43

gazzag
Messages: 1119
Registered: November 2010
Location: Bedwas, UK

Senior Member

Seconded

Report message to a moderator

Re: oracle user does not see some data [message #670066 is a reply to message #670052]

Fri, 01 June 2018 07:13

EdStevens
Messages: 1376
Registered: September 2013

Senior Member

yk307 wrote on Thu, 31 May 2018 08:41

select MNT_PIDMA, count(*)
from MNT
where MNT_PIDMA is not null
group by MNT_PIDMA
having not exists
(select 'x'
from den
where den_pidma = MNT_PIDMA)
and count(*)>0;

the same query returns different results

user-1 ( created 5 years ago ) brings 0 rows

user-2 ( created 2 weeks ago , with the same creation script as user-1 ) brings 30 rows
Thanks

Others have hinted, but just to spell it out ...
When user1 issues 'select .... from den ..' he is selecting from user1.den.
When user2 issues 'select .... from den ..' he is selecting from user2.den.
Different tables, different results.

Report message to a moderator

Previous Topic:	Upgrading Oracle Grid Infrastructure from 12cR1 to 12cR2
Next Topic:	Control file

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Nov 28 11:40:36 CST 2024