Re: Renaming schemas directly

From: Stefan Knecht <knecht.stefan_at_gmail.com>
Date: Mon, 31 Aug 2009 18:58:03 +0200
Message-ID: <486b2b610908310958k6b2e7174oda1c027855a7c4f5_at_mail.gmail.com>



3 kinds of answers for something like this ...
  1. You shouldn't do this. Doing this pretty much instantly de-supports your database.
  2. When doing things like this with the data dictionary (there are cases, where actions under the supervision of oracle support are tolerated), you need to bypass the row cache. In other words, do your change, commit, startup force.
  3. You really shouldn't do this.

Stefan P Knecht
CEO & Founder
s_at_10046.ch

10046 Consulting GmbH
Schwarzackerstrasse 29
CH-8304 Wallisellen
Switzerland

Phone +41-(0)8400-10046
Cell +41 (0) 79 571 36 27
info_at_10046.ch
http://www.10046.ch


On Mon, Aug 31, 2009 at 6:50 PM, Bellows, Bambi (Comsys) <bbel5_at_allstate.com
> wrote:

> 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:58:03 CDT

Original text of this message