How to rename oracle schema?

Renaming a schema is not an easy thing in Oracle. For reasons unknown Oracle does not allow you to rename a schema by a keyword such as

rename old_schema to new_schema;

or

alter user old_schema rename to new_schema;

THIS FACILITY DOES NOT EXIST in Oracle. There might be some utilities or some undocumented features which might leverage the renaming a schema.

But if you really want one way to rename the schema go for the traditional way of exporting the existing schema and import into a new schema. Use clause fromuser touser while importing.

But this too is not fully renaming schema as the privileges will not be imported.

11 comments :

  1. select name from sys.user$;

    The schema name is only in oracle in one place and that is in sys.user$. Oracle uses the user# internally for all joins. If you
    1) shutdown immediate
    2) startup
    3) update sys.user$ with new "name"
    4) Commit
    You will have renamed the schema. I have done it many times without any negative fallout.

    ReplyDelete
  2. what if you do not shutdown immediately. If the DB is running for a day or two but minimal changes to the schema will this process still work.

    ReplyDelete
  3. Hey! i tried this but now i m not able to connect with this user nor changed user. Can you help me??

    ReplyDelete
  4. You did update the sys.user$ as per the comment in this page??? OMG...I haven't tried it yet...

    ReplyDelete
  5. I tried the method exposed of renaming in sys.user$.
    It works with a few additional tricks...
    - sys.user$ stores the user names in upper case. If you want to rename schema toto to foo you must update sys.user$ set name='FOO' where name = 'TOTO'.
    If you set name='foo', neither foo nor FOO will be able to connect.
    - Once renaming is done you need to 1) shutdown immediate 2) startup yet again before you can alter the user any further, eg resetting his password.
    Thanks for the tip.

    ReplyDelete
  6. OMG!!!!!
    I can't believe you actually suggested this!!!!!

    You NEVER change SYS owned objects unless Oracle tells you to. Believe me, I've been an Oracle DBA for 22 years. You DON'T change values in SYS tables.

    ReplyDelete
  7. To the first poster: You are spreading TERRIBLE advice. Want to break your Oracle contracts? Go ahead. You are obviously not a DBA... at least one I would hire.

    ReplyDelete
  8. The two previous posters seem to be heavily affected by Oracle brain-plague.

    ReplyDelete
  9. The only reason against playing around with SYS objects is that will make the target DB officially unsupported. It won't burn your house. Why are people so frightened to mention this, so much so that "spreading" this becomes a taboo / crime / sin is beyond me.

    ReplyDelete
  10. Works a treat, thanks for this :)

    ReplyDelete
  11. Worked perfect...... Thanks a lot, this saved me few days of works from exporting and exporting the schemas. The only thing I had to do is reasign the passwords after restarting and voila!!!.

    I have a controlled development/test enviroment. and this worked just perfect.... until now.

    ReplyDelete