Sigh
A reader, December 12, 2004 - 10:12 pm UTC
Hm, I was afraid of that
At times like these, I wonder why Oracle (even with 10g) doesnt have a simple
ALTER USER old RENAME TO new;
command.
It could simply do
update sys.user$ set name='new' where user#=N and name='old';
Since the data dictionary is so highly normalized, this simple update should take care of everything nicely?
Thanks
December 13, 2004 - 9:50 am UTC
except for your scads of code that you are worried about that need now be recoded.
all of your plsql -- bam, scott.emp is now bob.emp, go fix it.
your views...
your references from other schemas
your public synonyms
and so on.
see the question was:
What is the best, most efficient and all-encompassing way to rename all
references to this schema to a new schema name?
updating user$ would achieve NONE of that.
Is it worth it?
Huy, December 13, 2004 - 5:56 am UTC
It's hardly worth the work and the risks. A schema by any other name ...
Updating user$
A reader, December 13, 2004 - 10:35 am UTC
You are right of course, if the schema is heavily used all over the place, that has to be fixed and your initial response was perfect for that.
But if the schema is just created and not much code uses it, its just a matter of fixing the dictionary, that hypothetical ALTER USER RENAME would come in handy!
Of course, if the schema is just created, I can just drop it and recreate with the new name anyway :)
Thanks
Plus...
Kashif, December 21, 2004 - 10:42 am UTC
I'm surprised Tom let the one about updating the sys.user$ table slip by without any chiding. Wouldn't that be a strict no-no anyways?
Kashif
December 21, 2004 - 1:06 pm UTC
that would be strictly a no-no -- 100%
updating sys.user$
A reader, December 21, 2004 - 5:14 pm UTC
Right, I didnt mean we, as users, update sys.user$. I was wondering why Oracle didnt offer a DDL command like ALTER USER old RENAME TO new, which could internally be implemented as that update (or whatever else it needs to do!)
Renaming an Oracle Schema
Brian, February 02, 2005 - 3:25 pm UTC
Very useful as I was contemplating this very question
rename schema
jianhui, March 25, 2005 - 4:07 pm UTC
In 10G we are seeing renaming tablespace now, but i hope we dont have to wait 11x for renaming schema owner. Since the user name is part of hashed password in data dictionary, so while renaming the user, it probably looks like this:
alter user old rename to new identified by pwd,
otherwise, oracle doesnot know how to get the hash value for the encrypted password. Internally, all dependancies should be referenced by user ID instead of user Name, just like not to update the PK. The password is the only thing i can imagine why oracle is not offerring renaming schema. Come on oracle, just a name change but we need this feature!
March 25, 2005 - 6:57 pm UTC
did you file an enhancement request? telling me doesn't do much other than let me know. In order to get things into the database -- well, you gotta get it into the system.
but consider what your
functions
procedures
packages
triggers
views.........
will do -- do you use a userid in them or a username....
Ravi, May 23, 2005 - 4:43 pm UTC
Tom,
I've been tasked with Uniting/Merging two schemas as Business has decided to unite two applications.
Naturally, I am worried.
Reading all posts, it looks like I need to use
0)Mind the common objects between them and formulate a strategy exclusively for them for their merge. Mind any sequences if any that are common.
1)Export and Import from old schema to new schema
2)Mind the Grants To and From Old Schema
3)Check fixed referenceds like 'SCOTT.EMP' in all code of old schema and issue instructions to change them to be portable ie remove "SCOTT".
Is that all to mind?
Do you know of any strategy or any documentation or any Oracle recommended way that is accurate and safe for my purpose?
May 23, 2005 - 7:17 pm UTC
are these schemas for the same application?
Ravi, May 24, 2005 - 2:23 am UTC
They used to be for two different applications, with two different set of objects. But for the Business, the applications have been merged and it is thought that Schema A will be migrated to schema B as a long term plan.
As a startup, we are planning to Unite the schemas.
May 24, 2005 - 7:52 am UTC
well, it seems rather simplistic to say "merge them"
I cannot comment further, merging two applications generally takes alot of work, mapping, enhancement. It is a rather non-trivial operation and the resulting schema is a conglomeration of the two -- not a merge so much.
supported way to change owner of table & index
Mikhail, April 03, 2006 - 8:24 am UTC
Hi,
I found a way to change schema for table[s] & index[es].
using transportable tablespace. If would be more simple if all data objects are in the tablespace set which does not contain data objects from another schemas, so
1. create user new_user...
2. grant ... to new_user;
3. execute dbms_tts.transport_set_check(...);
4. alter tablespace ... read only;
5. exp transport_tablespace=y tablespaces=...
6. drop tablespace ... including contents;
7. imp transport_tablespace=y tablespaces=... datafiles=... fromuser=old_user touser=newuser
8. create nondata objects in new_user schema
9. [drop user old_user cascade;]
10. alter tablespace ... read write;
So you have changed owner of tables & indexes and created nondata objects in new_user schema with out of copiing large amount of data. I consider recreating nondata object as relatively quick comparing large table export/import.
Hope it could be useful.
April 04, 2006 - 9:29 am UTC
hmmm, very nice.
Indeed
Michel Cadot, April 04, 2006 - 12:30 pm UTC
Well done Mikhail!
A reader, September 07, 2006 - 11:37 am UTC
Yamy Vissel, December 13, 2006 - 10:43 am UTC
Hi, One question ; i'm not aware enough on the transportable option, but can i run an import while a tablespace is read only?
December 13, 2006 - 11:39 am UTC
as long as you are not trying to write into that tablespace, sure (meaning you can import into tablespace X even if tablespace Y is read only, but obviously you cannot import anything into Y since it is read only)
Partially working
kumar, May 08, 2013 - 12:47 pm UTC
we have rename schema as per your post.
after that we tried to connect as new username, its not connecting and its changed to old schema name. is there any other way to change schema name.
May 08, 2013 - 1:29 pm UTC
huh?
if you imported the data, then you already have connected as the new username.
I have no clue what you are doing. As long as you created the new user - of course you can connect as it.
what has changed to the old schema name????? that makes no sense.
Some script I used
HajdaM, February 18, 2014 - 8:47 am UTC
Hi, three years ago I had to merge two schemas together (move hundred objects from one schema to another) for multiple instances and it was lot of work.
I couldn't use imp/exp or any other tool and had to write SQL script for it and I still have it. Lucky for me, there was only standard tables.
I cannot recommend this as a proper solution, but I didn't find any other viable way how to solve my case and this worked for me.
You might need to add additional limits to apply this only to your schemas. Maybe some part of this script would be to any use to someone.
http://hajdam.zdechov.net/share/sql/oracle_rename_shema.sql
ignore case
Leontinus, May 05, 2014 - 5:54 pm UTC
I created user with capital letters in it's name. (username=RTH and password=RTH)
Then I imported the data, granted many things.
The developers were complaining about the user name. They needed lowercase username. I could not rename it but I wanted to avoid the create user, import, grant processes.
I did the followings:
sqlplus '/as sysdba'
SQL> select * from v$version;
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
SQL> show parameter case
sec_case_sensitive_logon boolean TRUE
SQL> connect RTH/RTH
Connected
SQL> connect rth/rth
ERROR:
ORA-01017: invalid username/password; logon denied
SQL> connect /as sysdba
Connected.
SQL> alter user rth identified by rth;
User altered.
SQL> connect rth/rth
Connected
SQL> connect RTH/RTH
ERROR:
ORA-01017: invalid username/password; logon denied
Why?
A reader, May 20, 2014 - 6:02 am UTC
Because...
<user> rth = RTH. You altered the case sensitive <password> from RTH to rth. So, rth/rth and RTH/rth would work, rth/RTH and RTH/RTH not (in the current situation)...
cherry, December 11, 2019 - 5:52 am UTC
work done !
thank you very much!