Home » RDBMS Server » Server Administration » oracle user does not see some data (Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 )
oracle user does not see some data [message #670048] Thu, 31 May 2018 07:46 Go to next message
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
Re: oracle user does not see some data [message #670049 is a reply to message #670048] Thu, 31 May 2018 07:59 Go to previous messageGo to next message
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
Re: oracle user does not see some data [message #670050 is a reply to message #670048] Thu, 31 May 2018 08:00 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

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

Re: oracle user does not see some data [message #670051 is a reply to message #670048] Thu, 31 May 2018 08:07 Go to previous messageGo to next message
gazzag
Messages: 1118
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.
Re: oracle user does not see some data [message #670052 is a reply to message #670051] Thu, 31 May 2018 08:41 Go to previous messageGo to next message
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
Re: oracle user does not see some data [message #670053 is a reply to message #670052] Thu, 31 May 2018 08:44 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
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 Go to previous messageGo to next message
yk307
Messages: 3
Registered: May 2018
Junior Member
Michel Thank you for answering , I will try to investigate
Re: oracle user does not see some data [message #670056 is a reply to message #670055] Thu, 31 May 2018 09:28 Go to previous messageGo to next message
gazzag
Messages: 1118
Registered: November 2010
Location: Bedwas, UK
Senior Member
SQL> SELECT owner, table_name
     FROM dba_tables
     WHERE table_name IN ('DEN','MNT');
Re: oracle user does not see some data [message #670057 is a reply to message #670056] Thu, 31 May 2018 09:40 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
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 Go to previous messageGo to next message
gazzag
Messages: 1118
Registered: November 2010
Location: Bedwas, UK
Senior Member
Seconded Smile
Re: oracle user does not see some data [message #670066 is a reply to message #670052] Fri, 01 June 2018 07:13 Go to previous message
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.
Previous Topic: Upgrading Oracle Grid Infrastructure from 12cR1 to 12cR2
Next Topic: Control file
Goto Forum:
  


Current Time: Thu Mar 28 15:11:53 CDT 2024