RE: Renaming schemas directly

From: Goulet, Richard <Richard.Goulet_at_parexel.com>
Date: Tue, 1 Sep 2009 11:09:15 -0400
Message-ID: <23C4836D8E9C5F4280A66C0C247BC16F2D1C5A3F_at_US-BOS-MX011.na.pxl.int>



AH, the Dark SIde returns.  

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 Jack van Zanen Sent: Monday, August 31, 2009 9:16 PM
To: vamshireddy_1_at_yahoo.co.in
Cc: bbel5_at_allstate.com; oracle-l-admins_at_freelists.org; oracle-l Subject: Re: Renaming schemas directly

Isn't that SQL Server?  

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
Received on Tue Sep 01 2009 - 10:09:15 CDT

Original text of this message