Home » RDBMS Server » Security » ALL_TABLES is showing incorrect information
ALL_TABLES is showing incorrect information [message #273965] Fri, 12 October 2007 07:12 Go to next message
Pastonp
Messages: 13
Registered: November 2006
Junior Member
Hi ,

Has anyone come accross the following senario under 10.2.0.3.0 and has any idea what may be causing it ?

Some User tables not showing in all_tables or all_objects but do appear in the dba_ user_ eqivalents.

Some user tables that have been deleted and purged appearing in all_tables and all_objects but not in dba_ and user_ eqivalents.

Just ordinary tables - eg create table fred as select * from dual - nothing strange.

Thanks
P

[Updated on: Fri, 12 October 2007 07:43]

Report message to a moderator

Re: ALL_TABLES is showing incorrect information [message #273978 is a reply to message #273965] Fri, 12 October 2007 09:14 Go to previous messageGo to next message
BlackSwan
Messages: 22712
Registered: January 2009
Senior Member
You should file a bug to report such incorrect behavior.
Re: ALL_TABLES is showing incorrect information [message #273993 is a reply to message #273965] Fri, 12 October 2007 10:56 Go to previous messageGo to next message
Michel Cadot
Messages: 58920
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Prove what you are saying.

Regards
Michel
icon12.gif  Re: ALL_TABLES is showing incorrect information [message #274143 is a reply to message #273993] Sun, 14 October 2007 10:54 Go to previous messageGo to next message
Pastonp
Messages: 13
Registered: November 2006
Junior Member
Michel Cadot wrote on Fri, 12 October 2007 11:56

Prove what you are saying.

Regards
Michel




This has been playing on my mind all weekend. The only thing i can think of is that the user has a table or view in their schema called ALL_TABLES and ALL_OBJECTS.

Will check this out Monday ...This is the only expalnation I can think of. I refuse to believe that something fundemental would not have been spotted already.

Could this be the explanation ?

What punishment would you suggest for such a user Wink

Regards
P

Re: ALL_TABLES is showing incorrect information [message #274144 is a reply to message #274143] Sun, 14 October 2007 11:05 Go to previous messageGo to next message
Michel Cadot
Messages: 58920
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No explaination until you post what you did.

Regards
Michel
Re: ALL_TABLES is showing incorrect information [message #274145 is a reply to message #274144] Sun, 14 October 2007 11:28 Go to previous messageGo to next message
Pastonp
Messages: 13
Registered: November 2006
Junior Member
Michel Cadot wrote on Sun, 14 October 2007 12:05

No explaination until you post what you did.

Regards
Michel



Whats to explain ?

What part of this very simple question do you not understand ?

If you need me to explain how to select records from all_tables or user_tables then you are unkilely to be able to help me with my question.

Suggest you let someone who does understand reply and stop being very intentianlly difficult and rude.

P
Re: ALL_TABLES is showing incorrect information [message #274146 is a reply to message #274145] Sun, 14 October 2007 11:40 Go to previous messageGo to next message
Michel Cadot
Messages: 58920
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:

stop being very intentianlly difficult and rude.

Rude? What is rude? I just said I can't give any explaination until you post what you did.
How many times I saw people saying what they said is what they did and when they finally post what they did we see this is not what they said? I can't count them.

In addition, you gave an interpretation of what you got but are you sure this is the correct one?

So post what you did and what you get.

Regards
Michel

[Updated on: Sun, 14 October 2007 11:45]

Report message to a moderator

Re: ALL_TABLES is showing incorrect information [message #274161 is a reply to message #274145] Sun, 14 October 2007 18:20 Go to previous messageGo to next message
pablolee
Messages: 2615
Registered: May 2007
Location: Scotland
Senior Member
Quote:

What part of this very simple question do you not understand ?

YOU are the one looking for help. If you want help, then do what is asked of you. It's that simple.
Re: ALL_TABLES is showing incorrect information [message #274261 is a reply to message #274161] Mon, 15 October 2007 04:36 Go to previous messageGo to next message
Pastonp
Messages: 13
Registered: November 2006
Junior Member
Hi ,

Problem solved.

P

Re: ALL_TABLES is showing incorrect information [message #274270 is a reply to message #274261] Mon, 15 October 2007 05:09 Go to previous messageGo to next message
Michel Cadot
Messages: 58920
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And reason/solution is?

Regards
Michel
Re: ALL_TABLES is showing incorrect information [message #274278 is a reply to message #274270] Mon, 15 October 2007 05:41 Go to previous messageGo to next message
Pastonp
Messages: 13
Registered: November 2006
Junior Member
Hi ,

for the benefit of those who are interested in what caused this situation i will try to explain...

When using sql developer a user noticed that sql developer refused to show the DDL for some tables. It appeared at first sight to be a bug but i was more concerned when the user got a different list of tables when running a
select * from all_tables where owner = USER
compared to a
select * from user_tables


I was unable to recreate the problem.

In the end it turned out that the user had created his own copy of all_tables and all_objects. These were overriding the public synonyms of all_tables and all_objects.

Renaming or deleting the Users tables called all_tables etc resolved the issue.

not sure why sql developer does not explicity refer to the public synonyms but that is a question for another day. I will send a bug report as the sql developer team might be interested to know of this behaviour.

Regards
P
Re: ALL_TABLES is showing incorrect information [message #274280 is a reply to message #274278] Mon, 15 October 2007 05:50 Go to previous message
Michel Cadot
Messages: 58920
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Name resolution is:
1/ user object
2/ object pointed by private synonym
3/ object pointed by public synonym

Regards
Michel
Previous Topic: ORAPWD Utility
Next Topic: Grant on BLOB
Goto Forum:
  


Current Time: Thu Aug 28 04:36:58 CDT 2014

Total time taken to generate the page: 0.05839 seconds