I tried that. No Joy. Thanks Andrew.
Jim
Andrew Babb wrote in message <371F1690.169368A8_at_mail.com>...
>Jim,
>
>I think you will discover that if you shutdown the database after you issue
the
>update statement, and before the user is created, you will solve the
problem.
>
>Oracle caches certain values in memory when the database starts up, and
will use
>the cached value over that off the database. However, when the database is
>shutdown, oracle does not write the cached value back.
>
>Give it a go on the Test system...
>
>Andrew
>
>Jim Kennedy wrote:
>
>> We found the following problem in Oracle 7.3.
>> Create a user.
>> Drop the user.
>> Create a user.
>> Drop a user.
>>
>> Do that over the course of time until you have done that over 65,500
times.
>> You will find that you cannot create anymore users. We called support
and
>> this is a known issue and is marked as an enhancement (we think it is a
>> defect not an enhancement). The only known workaround is to export all
the
>> data, drop the database, and reimport the data into a new database. To
do
>> that is going to cost our customer hundreds of thousands of dollars in
lost
>> time.
>>
>> It was suggested altering the user$ table by dropping some users and
>> resetting the user# value for the user called _NEXT_USER . We tried that
>> (on our test system) and _NEXT_USER went inexorably upward when we added
>> another user. For example, lets say that _NEXT_USER is 85 and I drop
users
>> numbered 80 through 84. I then do update user$ set user#=80 where
>> name='_NEXTUSER '; commit;
>> I then lok at the table to see if it worked (and it shows that it did.)
I
>> then add a user. That user becomes user# 85 and _NEXT_USER becomes 86.
>>
>> No Joy.
>>
>> It appears that it is tied to some sequence number and if I could just
reset
>> that I would be okay. Maybe export the user$ table, drop it and import
it?
>>
>> Any help would be appreciated. I would prefer not to have to rebuild the
>> database.
>> Jim
>> jim_kennedy_at_medicalogic.com
>
Received on Thu Apr 22 1999 - 07:44:03 CDT
