Skip to Main Content
  • Questions

  • How does Oracle Database provide the user$.user#

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question.

Asked: March 21, 2024 - 9:35 am UTC

Last updated: March 25, 2024 - 10:52 am UTC

Version: 19c, migrate EE to SE

Viewed 1000+ times

You Asked

Hi Toms,
eventually, after many years, I came across e question I never realized.
Indeed I have to face a customer, who uses the user$.user# for application purposes. Will say after creating a user, that application stores the user# within application tables columns, say USR_ID, which, subsequently leads to the need that user$.user# has to match the USR_ID.
In consequence, if you have to migrate that application via epxdp / impdp (we have to, as we migrate from Solaris to Linux) these IDs won't match anymore as the users on the new database are created with different user$.user#.
You do not have to tell me that THAT application needs "some redesign"...
However, I have some questions regarding user$.user#.
As far as I have seen / read, when creating a new user using the usual "create user" statement the new users user# is provided by oracle rdbms as the user# of "_NEXT_USER".
_NEXT_USERs user# serves as a high water mark, even when dropping the user again _NEXT_USERs user# won't decrease (looks like an Oracle maintained sequence is used), so creating and dropping users leades to unused ranges of numbers in user$.user#.

Questions:
- Which sequence does provide the number of _NEXT_USER?
- Is there any way to reset it?
- or is there any way to influence the user$.user# or the number that is provided by rdbms to be stored as user$.user#?

=> I assume this may result in corruption but perhaps there is a way.

Thanks and best regards - dietmar


and Connor said...

- Which sequence does provide the number of _NEXT_USER?

Its not a sequence. Its just a place holder for the next number

- Is there any way to reset it, or is there any way to influence the user$.user# or the number that is provided by rdbms to be stored as user$.user#?

Not to my knowledge and not in a supported way.

I did a quick full import test, and we do not preserve them across full export/import.

Before:

     USER# NAME
---------- ------------------------------
         0 SYS
         1 PUBLIC
...
       108 CONNOR
       109 NOTIFICATIONS_USER
       110 NOTIFICATIONS_ADMIN
       111 SCOTT
       126 _NEXT_USER


After

     USER# NAME
---------- ------------------------------
         0 SYS
         1 PUBLIC
...
       109 NOTIFICATIONS_ADMIN
       110 CONNOR
       111 SCOTT
       112 _NEXT_USER



I suppose one thing you could try is get the list of users in order from your source database, then carefully precreate them in the right sequence in your target database, including creating dummy users and dropping them where you have gaps. Once the new database has users with the same user id, then you could do your full import.

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Change the user ids stored in the applcation tables post import

Christian, March 27, 2024 - 11:45 am UTC

Change the user ids stored in the applcation tables post import:

Before you doing that, ensure that there are no collisions inside the applcations data between imported user_ids and newly assigned user_ids.

One can achieve that by forcing the import target database to use user ids in a numerical range larger than the source database by creating and then dropping dummy users as needed pre-import.

So say the source database has user_id range in user$ < 1000 then in the target database do create user/select max(user_id) .../drop user until you have reached 999 and only then do your import after which you update the user_ids stored in application data as required.

Test first and restore your target database to pre-test as often as required.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Data Pump

All of the database utilities including Data Pump are explained in the Utilities guide.