2

under the same schema, I got different number of tables from the following two queries:

select object_type,count(*) from user_objects group by object_type;

output: i got 17 for table from object_type column.

another query: 

select count(*) from user_tables;

output:

13 rows selected.

Can someone tell me why I got two different numbers?

Patrick Bacon
  • 4,490
  • 1
  • 26
  • 33
stephanie
  • 75
  • 1
  • 1
  • 3
  • 2
    We would have to see your data to help you further. – Linger Sep 12 '14 at 13:25
  • I ran your queries across 4 different schemas and got consistent results each time. And the count was of the order of 1000s and still accurate to the dot. Yours seems to be a special case, so please provide additional data. – CodeNewbie Sep 12 '14 at 13:39
  • Don't say, SHOW and PROVE.We don't have your tables, so understand. SQL*Plus, copy and paste are your friend. – Lalit Kumar B Sep 12 '14 at 13:42
  • I checked `sys` user and searched tables in `user_objects` that are not in `user_tables`. I got `KOT%$` tables, `S_PROPS_TAB`, `PROPERTIES_TAB`, `USR_PROPERTIES_TAB`,`SCHEDULER%` tables and many `SYSNT%` tables. On "normal" schemas both counts were the same. – yamny Sep 12 '14 at 13:56

1 Answers1

-1

Result of first query includes objects that are in BIN. Try 

select object_type,count(*) from user_objects where object_name not like 'BIN%' group by object_type;

You should get the same result

psmith
  • 1,769
  • 5
  • 35
  • 60
  • It will be an assumption till OP shows the table_names. If OP dropped those tables using PURGE, they shouldn't show up. – Lalit Kumar B Sep 12 '14 at 13:55
  • But why do you suggest my answer doesn't resolve the problem? I've checked it on a few schemas and the only difference between results I got on tables that are in the BIN. I suppose the queries from the question are run on typical users, not SYS not SYSTEM etc. – psmith Sep 12 '14 at 14:17
  • I am not saying your answer is incorrect, but the question itself lacks information. As I said, the recycle_bin objects won't show up in user_objects if they were purged during DROP. So, your post is more of a comment than an answer at this stage. If you answer an incomplete question, you will end up with an incomplete answer ;-) – Lalit Kumar B Sep 12 '14 at 14:21
  • I agree that PURGE removes objects from the BIN but in that case both queries should give the same result. I cannot find any other reason for this difference. Would be nice if the author of the question said something... – psmith Sep 12 '14 at 14:26
  • That's what I said earlier, we are yet to see OP to provide the relevant information. And just in case if you are not aware, STACK OVERFLOW is NOT a forum to discuss over issues with assumptions. All the suggestions are appropriate for comments section. Neither am I saying that you are incorrect, nor did I vote down your answer, in case if you are contemplating if I have voted down. As I would certainly provide a reason as an etiquette :-) – Lalit Kumar B Sep 12 '14 at 14:28
  • Your statement `I agree that PURGE removes objects from the BIN but in that case both queries should give the same result` is wrong. Think again. – Lalit Kumar B Sep 12 '14 at 14:33
  • That's the reason how would you see those purged tables in the view you are referring to? You can add a test case in your answer. – Lalit Kumar B Sep 12 '14 at 14:42
  • Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/61120/discussion-between-pawel-kowalski-and-lalit-kumar-b). – psmith Sep 12 '14 at 14:49