ALTER USER RENAME - A half official option

Many times DBA's have to rename / copy complete users/schemas from the current name to a new name within one database. Since several years DBA's are asking for a simple method inside Oracle to execute something like "ALTER USER RENAME".

Common approaches to rename users/schemas are:

• IMPDP with REMAP_SCHEMA
• update of sys.user$

For the option "update of sys.user$", which is in my opinion a really bad choice, see Tom Kyte's comment 

For the option "IMPD with REMAP_SCHEMA", its official but can be time consuming.

It would be really cool to have a simple SQL Statement like "ALTER USER RENAME". In the official Oracle Documentation "Database SQL Language Reference" under ALTER USER is nothing documented for a RENAME option, but it exists !!!! :-)

How does it work?

Let's say we have a user called DEMO and we want to rename this user to DEMO_NEW. The user DEMO got tables, views and functions.

?

1 2 3 4 5 6 7 8 9 10 11

# Connect as SYS to your database conn / as sysdba # At first we set an undocumented parameter to enable the RENAME option alter session set "_enable_rename_user"=true; # Bring the Database to restricted session, in order to avoid Memory Problems for huge schema alter system enable restricted session; # Now lets RENAME the user DEMO to DEMO_NEW # and provide a password for the new user DEMO_NEW alter user DEMO rename to DEMO_NEW identified by "demo"; # Disable restricted session alter system disable restricted session;

That's it :-)

The ALTER USER RENAME works from Oracle 11.2.0.2.0 going, my above test case was done with Oracle 12.1.0.2.0.

So why is this option with ALTER USER RENAME "half official"?

• It's not documented in the official Oracle Documentation
• It requires an undocumented parameter
• But there are Patches available for the ALTER USER RENAME within My Oracle Support
• For Example: Patch 10217802 which fixes an ORA-4030 for ALTER USER RENAME

The question now is why Oracle doesn't make this cool feature official?

So far, I found one limitation, if the user holds a type which is referenced within a table you will get following error for the ALTER USER RENAME:

ORA-42287: cannot rename user on whose type a table depends

If anybody finds more limitation while testing this cool feature, just drop me message here.

But remember, DON'T DO THIS IN PRODUCTION !! Its not an official option, there might be some serious kickback effects.

Reference Site