RE: Renaming schemas directly
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 defaultschema 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 renameschemas, 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-lReceived on Tue Sep 01 2009 - 10:09:15 CDT