RE: Renaming schemas directly
- From: "Goulet, Richard" <Richard.Goulet@xxxxxxxxxxx>
- To: <bbel5@xxxxxxxxxxxx>, "Oracle L" <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 31 Aug 2009 13:02:45 -0400
Bambi,
One can use your method if you like, but don't tell OTS about it.
They have a habit of starting to have a good laugh as they tell you to
export and rebuild the database. Messing around in the lower levels of
the data dictionary is something that should be done at the direction of
OTS only and then with extreme care. OH and yes only after a full cold
backup.
Dick Goulet
Senior Oracle DBA/NA Team Lead
PAREXEL International
________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Bellows, Bambi
(Comsys)
Sent: Monday, August 31, 2009 12:51 PM
To: Oracle L
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.
Other related posts:
