Forum Stats

  • 3,824,848 Users
  • 2,260,430 Discussions
  • 7,896,330 Comments

Discussions

Need a command to rename a schema

Tmicheli-Oracle
Tmicheli-Oracle Member Posts: 24 Red Ribbon
edited Jan 11, 2016 6:06PM in Database Ideas - Ideas

Ask Tom has -https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:30020192108317

You Asked

How can I rename a schema name that was incorrectly named when it was created? Over a period of time, it has been used all over the place, grants have been made to/from this schema, etc, etc. 
What is the best, most efficient and all-encompassing way to rename all references to this schema to a new schema name? Thanks


and we said...

there are none, the references to it must be hunted down and changed, the schema itself would have to be exported, and imported using "fromuser=bad_name touser=good_name"
(and even then, you would be responsible for grants to good_name and public synonyms)
the easiest approach would be to change your view on the name of the schema 
that is, accept it for what it is and go with it.
Else, lots of work. A transitory method would be:
a) create good schema
b) fill it with synonyms pointing to bad
c) move things over time (leaving a synonym behind in bad_name pointing to good_name)
d) when you believe the task to be complete, start dropping the synonyms (and probably watching for what "breaks")

Oracle needs a command like

RENAME SCHEMA <schema name> TO <new schema name>

the one step command needs to cleanup all the warning identified by AskTom

berxRodrigo Jorge DBAPavril-OracleGeeky NerdmanSudhakar Reddy AEduardo SmaniottoRichard Harrison .User259623 -OracleFranck Pachotcarajandbuser6604134954777mschmidtTorsten KleiberDer BaboHans-Martin RuffRobertOrtel960326borneselMartin PreissHemant K ChitaleUser2121 - -OracleCarsten KaftanManish Chaturvedivinaykumar2Andreas HubermarkmevansZlatko SiroticIvica ArsovcaadecarvalhoctriebLoïc Lefèvre-OraclePravin TakpirerohanwaliaZedDBAAparna Dutta-Oracleuser10212775Shwetha Siddamallappa-OracleTSharma-OracleJitendraKiran PawarJagadekaraBPeaslandDBAa_nullbhagatsinghChris HuntulohmannVivek12-Oracleabhinivesh.jainMortenBratenMarco Gralikeuser13336898User_67QPLPatrick Wolf-OracleAish13William RobertsonsysassysdbaSven W.Geert GruwezMaciej TokarshiwoPkSergey KlimovEmad Al-Mousa_LC_Mitja GolouhLudovicoCaldarauser12182396Dear DBA FrankT.S.BeGinDaniel Overby HansenMartinKlierDBAVictor AmeijendaRafiq DOzgur Umut VurgunNimish Gargritan2000pattonjgMarco MischkeDaniel HillingerMKJ109302791483608N.B.ParsJeffrey KempdoberkoflerNelson CaleroCraig ElliottfaviantorresLaurens Wagemakers3178676davemmJ.Schnackenberg3241488sensoftLukas EderGbenga AjakayePKRtrapssonTHEinternetNiels HeckerUser_C20E8pmdba2803418CloudDBJames G KMettemusens2Peter Hraško35323781920519johnnie.billingsDaniel E.Riaz.blessed DBAUser_8VQUEfloo_baropentuningsValeriy Smirnovrober584812Francisco Miguel Biete BanonHitgon-OracleDirk.NachbarEvandro Lima-OracleRui RMaher AlkhateebSven_SKonstantin MylnikovL. FernigriniSam_Pjormart-OracleAbhijit GourJiF BrodeurDejan T.Bernd PatollaRCutshawRok BankoRaul.AlvarengaFelix ManjarresRJAIMES-SVJustin WarwickLuís Gustavo LiraAdityanath DewoolkarUser_HFWN8Göran PauesSanjeev ChauhanNicolas RonayetteCharlesM_DaytonUser_26SNDsdstuberUser_KQW87User_Q4UXNUser_1JH88
159 votes

Active · Last Updated

«13

Comments

  • Geeky Nerdman
    Geeky Nerdman Member Posts: 360 Silver Trophy

    Great Idea, although I imagine it is much easier said than done.

    Der Baborober584812
  • Richard Harrison .
    Richard Harrison . Member Posts: 2,065 Gold Trophy

    I've needed this so many times.....

    You'd think most of this would be achieved by just updating user$ - surely all the other dictionary objects are referencing the user by id not name?

    Surely only hardcoded schema references in stored plsql are  going to be the real headache?

    Emad Al-MousaUser_6JC27
  • Rodrigo Jorge DBA
    Rodrigo Jorge DBA Member Posts: 86 Bronze Badge

    I've needed to do this so often that I even wrote an article about this topic.The workaround that I use is to "clone" the user to the same DB with a different name, using loopback dblink. It's very fast and in the end, after a successful checkup, you can drop the old schema.

    The article is in here: http://www.dbarj.com.br/en/2014/11/rename-schema-oracle-11g-loopback-dblink/

    Regards,

    ritan2000Gbenga Ajakaye2803418Alex Lamar-Oracle
  • Richard Harrison .
    Richard Harrison . Member Posts: 2,065 Gold Trophy

    I've needed to do this so often that I even wrote an article about this topic.The workaround that I use is to "clone" the user to the same DB with a different name, using loopback dblink. It's very fast and in the end, after a successful checkup, you can drop the old schema.

    The article is in here: http://www.dbarj.com.br/en/2014/11/rename-schema-oracle-11g-loopback-dblink/

    Regards,

    Hi,

    I've done that myself - when the database gets big though it becomes impractical. I then came up with another approach using TTS  Oracle DBA Blog 2.0: How to rename a 500GB schema in 17 minutes.....

    However i still think a rename schema is much simpler and less hassle.

    Both of the methods above still need you to update hardcoded references in plsql, so i don;t see how a rename schema is really any different - that manual bits are still required - as long as this is clear in the documentation then i think this is (if the data dictionary was designed relationally.....) a simple case of:

    update user$ set name='newname' where name='oldname'

    However there must be more to it than that otherwise it would already have been implemented long ago.

    This is still the best idea on the list (so far).

    Regards,

    Rich

    Rodrigo Jorge DBAEmad Al-MousaGbenga Ajakaye
  • Tmicheli-Oracle
    Tmicheli-Oracle Member Posts: 24 Red Ribbon

    We, Oracle are working on our internal process as to how to evaluate and prioritize the IDEAS submitted.  But the more votes obviously the more priority we will put on the request.  However votes/popularity alone will not determine the priority.

    As we move through the process the IDEA will change stages: (not in flow order)

    - Active

    - Already Offered

    - Archived

    - Coming Soon

    - For Future Consideration

    - in Progress

    - Partially Implemented

    - Under Review

  • Franck Pachot
    Franck Pachot Member Posts: 912 Bronze Trophy

    Hi,

    There is an undocumented feature to create schema synonyms: http://www.dbi-services.com/index.php/blog/entry/create-schema-synonym-in-oracle-unsupported-feature

    The rename schema should be much easier to implement (just update the sys.user$ and invalidate all dependencies)

    Regards,

    Franck.

    ritan2000HarbourGhostGbenga Ajakaye
  • Marco Gralike
    Marco Gralike Member Posts: 4,491 Silver Trophy

    In principle it should be as simple as an update of the "label" in column "name" on SYS.USER$...but that would assume that only user# is used as a reference and, eg. passwords, don't have dependency with "name" values...

    SELECT user#,

      name

    FROM sys.user$

    WHERE type# = 1

    ORDER BY user#;

    0    SYS

    7    AUDSYS

    8    SYSTEM

    13    OUTLN

    21    GSMADMIN_INTERNAL

    22    GSMUSER

    23    DIP

    36    ORACLE_OCM

    48    DBSNMP

    49    APPQOSSYS

    50    XDB

    51    ANONYMOUS

    61    GSMCATUSER

    62    WMSYS

    70    OJVMSYS

    73    CTXSYS

    75    ORDSYS

    76    ORDDATA

    77    ORDPLUGINS

    78    SI_INFORMTN_SCHEMA

    79    MDSYS

    82    OLAPSYS

    85    MDDATA

    87    SPATIAL_WFS_ADMIN_USR

    90    SPATIAL_CSW_ADMIN_USR

    92    LBACSYS

    99    DVF

    102    HR

    104    IX

    105    SH

    106    PM

    107    BI

    109    OE

    110    SCOTT

  • Cobert
    Cobert Member Posts: 564 Silver Badge

    Another alternative would be Goldegate or streams - replicate to another schema if it's required online, and then cutover when needs be?

    Streams can be tricky but once setup it's great / depending on the level of change. If doubles the footprint but if you can beg or borrow the storage

    The lack of functionality might reflect making sure some pre planning goes into it, I support in another world you could have a push to change naming conventions left, right and centre.

  • top.gun
    top.gun Member Posts: 3,666 Gold Crown

    I've needed this so many times.....

    You'd think most of this would be achieved by just updating user$ - surely all the other dictionary objects are referencing the user by id not name?

    Surely only hardcoded schema references in stored plsql are  going to be the real headache?

    Exactly - and what about the hardcoding in the dynamic PL/SQL. There isn't a dependency like static PL/SQL.

    How will a rename command change these references?

    I can just see a junior DBA using the rename command, then logging a SR complaining that the dynamic PL/SQL didn't update......