Re: Renaming schemas directly
- From: Stefan Knecht <knecht.stefan@xxxxxxxxx>
- To: bbel5@xxxxxxxxxxxx
- Date: Mon, 31 Aug 2009 18:58:03 +0200
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@xxxxxxxx
10046 Consulting GmbH
Schwarzackerstrasse 29
CH-8304 Wallisellen
Switzerland
Phone +41-(0)8400-10046
Cell +41 (0) 79 571 36 27
info@xxxxxxxx
http://www.10046.ch
=========================
On Mon, Aug 31, 2009 at 6:50 PM, Bellows, Bambi (Comsys) <bbel5@xxxxxxxxxxxx
> 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.
>
>
Other related posts:
