RE: Renaming schemas directly

    One can use your method if you like, but don't tell OTS about it. They have a habit of starting to have a good laugh as they tell you to export and rebuild the database. Messing around in the lower levels of the data dictionary is something that should be done at the direction of OTS only and then with extreme care. OH and yes only after a full cold backup.  

Dick Goulet
Senior Oracle DBA/NA Team Lead
PAREXEL International  

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Bellows, Bambi (Comsys)
Sent: Monday, August 31, 2009 12:51 PM
To: Oracle L
Subject: 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.

--
http://www.freelists.org/webpage/oracle-l