Renaming schemas directly

From: Bellows, Bambi (Comsys) <"Bellows,>
Date: Mon, 31 Aug 2009 11:50:40 -0500
Message-ID: <AD0CB572A820AB4E8E52ABD38950FD3606AEAD1A_at_a0001-xpo0150-s.hodc.ad.allstate.com>

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.

--

http://www.freelists.org/webpage/oracle-l Received on Mon Aug 31 2009 - 11:50:40 CDT