Oracle “alter user rename” command

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.

Advertisements

3 responses to “Oracle “alter user rename” command

  1. This particular posting Oracle “alter user rename” command | Jed’s, has got genuinely fantastic information and I figured out specifically what I was initially researching for. Thank you.

  2. Do you mind if I quote a couple of your articles
    as long as I provide credit and sources back to your site?
    My blog site is in the exact same niche as yours and my users would
    truly benefit from a lot of the information you present here.

    Please let me know if this alright with you.
    Many thanks!

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