can you tell which id a new user will get in oracle?

January 8, 2015 — Leave a comment

as described previously all roles and users are stored in the user$ table.

and yes, you can tell:

select user#,name from user$ where type# = 0 and name='_NEXT_USER';

     USER# NAME
---------- ------------------------------
	77 _NEXT_USER

create a new user:

SYS@xxxx> create user a identified by "a";

User created.

SYS@xxxx> select user# from user$ where name = 'A';

     USER#
----------
	77

checking the “_NEXT_USER” again we can see that the ID got incremented:

SYS@xxxx> select user#,name from user$ where type# = 0 and name='_NEXT_USER';

     USER# NAME
---------- ------------------------------
	78 _NEXT_USER

so oracle uses a (hidden) role to track the next id a user will get and updates the id of the (hidden) role itself after a user has been created :)

Advertisements

No Comments

Be the first to start the conversation!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s