Ask Tom has -https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:30020192108317
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