Re: URGENT:Limit on created users in Oracle 7.3

From: Graham C Thornton <graham.thornton_at_ln.ssw.abbott.com>
Date: Thu, 22 Apr 1999 09:11:03 -0500
Message-ID: <7fnamh$ap6@news.abbott.com>

Jim,

I tried this with the following result - seems to work okay for me.

Don't forget to flush the shared pool after commiting the change.

SVRMGR> select user#, name, type from user$;

USER#      NAME                           TYPE
---------- ------------------------------ ----------
         0 SYS                                     1
         1 PUBLIC                                  0
         2 CONNECT                                 0
         3 RESOURCE                                0
         4 DBA                                     0
         5 SYSTEM                                  1
         6 EXP_FULL_DATABASE                       0
         7 IMP_FULL_DATABASE                       0
        12 MEC                                     1
         8 SNMPAGENT                               0
         9 DBSNMP                                  1
        11 OPS$TAGUEE                              1
        15 _NEXT_USER                              0
        14 TEMPUSER2                               1

14 rows selected.

SVRMGR> drop user tempuser2;
Statement processed.

SVRMGR> update user$ set user# = 13 where name = '_NEXT_USER'; 1 row processed.

SVRMGR> commit;
Statement processed.

SVRMGR> alter system flush shared_pool; Statement processed.

SVRMGR> create user tempuser3 identified by tempuser3; Statement processed.

SVRMGR> select user#, name, type from user$;

USER#      NAME                           TYPE
---------- ------------------------------ ----------
         0 SYS                                     1
         1 PUBLIC                                  0
         2 CONNECT                                 0
         3 RESOURCE                                0
         4 DBA                                     0
         5 SYSTEM                                  1
         6 EXP_FULL_DATABASE                       0
         7 IMP_FULL_DATABASE                       0
        12 MEC                                     1
         8 SNMPAGENT                               0
         9 DBSNMP                                  1
        11 OPS$TAGUEE                              1
        14 _NEXT_USER                              0
        13 TEMPUSER3                               1

14 rows selected.
SVRMGR> Let us know how you get on.

Thanks

Graham

Jim Kennedy wrote in message ...
>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 - 09:11:03 CDT

Re: URGENT:Limit on created users in Oracle 7.3 - how to fix?