Need a command to rename a schema

Tmicheli-Oracle

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

Geeky Nerdman

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

Richard Harrison .

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?

Rodrigo Jorge DBA

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,

Richard Harrison .

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

Tmicheli-Oracle

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

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.

Franck Pachot

The best alternative I've seen is from @P.Forstmann: https://pierreforstmanndotcom.wordpress.com/2015/02/27/how-to-rename-an-oracle-schema-without-exporting-and-importing-al…

Marco Gralike

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

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

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......