Re: Renaming schemas directly

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

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.


      

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