| User tables are in ALL_TAB_COLUMNS but not in USER_TAB_COLUMNS [message #198199] |
Mon, 16 October 2006 02:49  |
 |
djmartin
Messages: 10181
Registered: March 2005
Location: Surges Bay TAS 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  |
|
djmartin
Messages: 10181
Registered: March 2005
Location: Surges Bay TAS 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 |
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
