Skip navigation

Need a command to rename a schema

score 1360
You have not voted. Active

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

Comments

Vote history