Rename Oracle User (Schema)

If you have ever wanted to rename a user in oracle, but didn’t think you could then this is for you.

Before I continue I should mention this is not an oracle supported process so continue at your own risk.  I will say this though … I have NEVER experienced any issues with the following process.  In my case it performed perfectly, but I did not need any references to the objects in the schema being renamed.  What I mean is I did not need any synonyms to remain valid.  If you need that then you will need to recreate the synonyms as well as other objects that fully reference the object such as <username>.<table_name>.

Well, no more stalling … let’s get to it.

First of all, make sure the username you are about to rename is not connected and then you can do the following.

SQL> connect / as sysdba
SQL> -- CAUTION: Make sure to use CAPITAL letters for the new username.
SQL> update user$ set name = 'NEWUSERNAME' where name = 'OLDUSERNAME';
SQL> commit;
SQL> shutdown immediate
SQL> startup
SQL> -- You will need to reset the password.
SQL> alter user newusername identified by newuserpwd
SQL> exit;

Ok, I know … it seems too simple, but sometimes that’s just how it works.

Have fun…

Post a Comment