Renaming schemas directly

Hey there Team!

It would sure be swell if Oracle gave us some means to rename schemas,
but, they don't.  I'm in 10g, and going behind the scenes, I can rename
a schema, once, but I can't seem to rename it back again.  Here's the
deal-i-o...

==============
sqlplus "/ as sysdba"

SQL> select name from sys.user$ where name like '%MIGRATION_SCHEMA%';

NAME
------------------------------
MIGRATION_SCHEMA1
MIGRATION_SCHEMA2
MIGRATION_SCHEMA3

SQL>update  sys.user$ set name=
  2  'NEW_MIGRATION_SCHEMA1' where user# = (select user# from sys.user$
where
  3* name='MIGRATION_SCHEMA1')

1 row updated.

SQL>  select name,user# from sys.user$ where name like 'NEW_MIG%';

NAME                                USER#
------------------------------ ----------
NEW_MIGRATION_SCHEMA1                2746

SQL> alter user NEW_MIGRATION_SCHEMA1 identified by junk;

User altered.

SQL> conn NEW_MIGRATION_SCHEMA1/junk
Connected.

==================

All good.

Now.  Let's change back.

===================
SQL> conn / as sysdba
Connected.

SQL> select name from sys.user$ where name like '%MIGRATION_SCHEMA1';

NAME
------------------------------
NEW_MIGRATION_SCHEMA1

SQL> update sys.user$ set name='MIGRATION_SCHEMA1'
  2  where name='NEW_MIGRATION_SCHEMA1';

1 row updated.

SQL> select name from sys.user$ where name like '%MIGRATION_SCHEMA1';

NAME
------------------------------
MIGRATION_SCHEMA1

SQL>  conn MIGRATION_SCHEMA1/junk
ERROR:
ORA-01017: invalid username/password; logon denied


Warning: You are no longer connected to ORACLE.
SQL> conn / as sysdba
Connected.


SQL> select name from sys.user$ where name like '%MIGRATION_SCHEMA%';

NAME
------------------------------
MIGRATION_SCHEMA2
MIGRATION_SCHEMA3
NEW_MIGRATION_SCHEMA1

=================

Note that if I put a commit in, I get the same kind of behavior.  Has
anyone else seen this?  How do you get around this?

Thanks!
Bambi.

Other related posts: