Re: Renaming schemas directly

This is an oracle forum

Jack

2009/9/1 vamshi krishna <vamshireddy_1_at_yahoo.co.in>

> Bambi,
>
> you can use these syntax to rename schema.
>
> ALTER USER
>
> Rename a database user or change its default schema.
>
> Syntax
> ALTER USER *user* WITH [NAME = *new_user_name*] [, DEFAULT_SCHEMA = *schema* ]
>
> Key*
> *user Name for the user in this database.
> new_user_name New name for this user.
> schema The first schema that will used to resolve object names for *user*.
> default schema = dbo
>
> All members of the sysadmin fixed server role have a default schema of dbo.
>
> A user that is not mapped to a SQL Server login may connect to other
> databases as guest, this just requires GRANT CONNECT TO guest;
>
> Examples
>
> ALTER USER user87 WITH NAME = user143;
>
> ALTER USER user21 WITH NAME = user144, DEFAULT_SCHEMA = Sales;
> GO
>
>
>
> ------------------------------
> *From:* "Bellows, Bambi (Comsys)" <bbel5_at_allstate.com>
> *To:* Oracle L <oracle-l_at_freelists.org>
> *Sent:* Monday, August 31, 2009 9:50:40 AM
> *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.
>
>
>

-- 
Jack van Zanen

-------------------------
This e-mail and any attachments may contain confidential material for the
sole use of the intended recipient. If you are not the intended recipient,
please be aware that any disclosure, copying, distribution or use of this
e-mail or any attachment is prohibited. If you have received this e-mail in
error, please contact the sender and delete all copies.
Thank you for your cooperation

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