RE: Renaming schemas directly

From: Goulet, Richard <>
Date: Tue, 1 Sep 2009 11:09:15 -0400
Message-ID: <>

AH, the Dark SIde returns.  

Dick Goulet
Senior Oracle DBA/NA Team Lead
PAREXEL International  

[] On Behalf Of Jack van Zanen Sent: Monday, August 31, 2009 9:16 PM
Cc:;; oracle-l Subject: Re: Renaming schemas directly

Isn't that SQL Server?  

This is an oracle forum    


2009/9/1 vamshi krishna <>


        you can use these syntax to rename schema.                  

        ALTER USER         Rename a database user or change its default schema.

	      ALTER USER user WITH [NAME = new_user_name] [,
DEFAULT_SCHEMA = schema ]         
	   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;


        ALTER USER user87 WITH NAME = user143;         

	ALTER USER user21 WITH NAME = user144, DEFAULT_SCHEMA = Sales;


	From: "Bellows, Bambi (Comsys)" <>
	To: Oracle L <>
	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_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.




	All good.

	Now.  Let's change back.


	SQL> conn / as sysdba


	SQL> select name from sys.user$ where name like


        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         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


        SQL> select name from sys.user$ where name like



        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?         



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 

Received on Tue Sep 01 2009 - 10:09:15 CDT

Original text of this message