At some point working with Oracle database you’ll try “alter user oldname rename to newname”. With all the rename commands now available in Oracle 11g you’d think this would be one, but alas, it is not. I’ve seen some posts that you can easily change the name in the database dictionary, but that clearly isn’t a good idea.
With DataPump in Oracle 11g there is a good way to do this with the REMAP_SCHEMA clause.Â Usually when I’ve needed to do this it is for an end-user account that has no schema objects, only privileges. If you have an account with schema objects then be prepared that this could take a while.
expdp dumpfile=rename.dmp logfile=rename_exp.log userid=system schemas=oldname impdp dumpfile=rename.dmp logfile=rename_imp.log userid=system remap_schema=oldname:newname
Now that you have the new users, there is one catch. Data Pump loads in the System Grants and Role Grants, but not direct object grants. Preferably you grant everything through roles, but if you need some object grants you can use dbms_metadata to get them.
SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT','OLDNAME') FROM DUAL
A benefit to doing it this way is the user can then test the new account before you drop the old one.