Home » RDBMS Server » Server Administration » User tables are in ALL_TAB_COLUMNS but not in USER_TAB_COLUMNS
User tables are in ALL_TAB_COLUMNS but not in USER_TAB_COLUMNS [message #198199] Mon, 16 October 2006 02:49 Go to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
I know that we are running an old, old database - but it works!! Usually.

We are running Version 7.3.2.3 on all our machines. Since last Thursday one of them has been failing during a stored procedure run. We have tracked it down to a 'NO DATA FOUND' on a select of the table 'user_tab_columns'. But the tables exist and they are selectable through 'all_tab_columns'. A 'count(*)' on 'user_tab_columns' is returning zero!! But a 'count(*)' on 'all_tab_columns where owner = 'MY_USER'' is greater than zero.

One of my colleagues thinks he saw this happen a few years ago but can't remember what caused it. Do any of you old guys have any recollections of this problem?

The user in question is created and dropped every night. The current batch software has been out in the field for a couple of months. A release did go out about the time of the error but it was supposed to only change some of the forms and user exits, not this night time process.

David

[Updated on: Mon, 16 October 2006 18:03]

Report message to a moderator

Re: User tables are in ALL_TAB_COLUMNS but not in USER_TAB_COLUMNS [message #198864 is a reply to message #198199] Wed, 18 October 2006 21:27 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
Okay, the code that accesses 'user_tab_columns' only went out with this most recent release. The problem is that the 'USER#' in the 'SYS.USER$' table was more than 32,767 (32767) and there is a bug in Oracle 7 through 8.0.3 whereby the 'USER_TAB_COLUMNS' view doesn't show the columns of tables owned by users with a 'USER#' greater than 32,767.

We changed the '_NEXT_USER' number to be greater than the other user numbers in the database but less than 32,767. We restarted the database, recreated our special temporary user (now with a number around 2000) and everything ran successfully.

David
[EDITED by DJM]

It happened again and I had to look through all the 'sys' tables until I found 'user$' before finding a keyword that would let me find this thread! So I am adding some keywords.

Keywords: user_objects oracle 32,768 (32768) user_id next user_id object_id next object_id ORA-01403: no data found

[Updated on: Wed, 02 December 2009 23:38]

Report message to a moderator

Re: User tables are in ALL_TAB_COLUMNS but not in USER_TAB_COLUMNS [message #199022 is a reply to message #198864] Thu, 19 October 2006 12:10 Go to previous message
joy_division
Messages: 4643
Registered: February 2005
Location: East Coast USA
Senior Member
Thanks David. I was puzzled by your situation too. At first I though it was a newbie doing something wrong, but when I saw it was you, I was hoping someone would have an answer. I don't think it will ever come up in my situation, but now I would know what to look for.
Previous Topic: Change Hostname for databse server
Next Topic: OEM problem
Goto Forum:
  


Current Time: Thu Dec 08 12:26:58 CST 2016

Total time taken to generate the page: 0.14653 seconds