Renaming schemas directly
- From: "Bellows, Bambi (Comsys)" <bbel5@xxxxxxxxxxxx>
- To: "Oracle L" <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 31 Aug 2009 11:50:40 -0500
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:
