Skip to Main Content

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question.

Asked: December 12, 2004 - 8:42 pm UTC

Last updated: May 08, 2013 - 1:29 pm UTC

Version: 9.2.0

Viewed 100K+ times! This question is

You Asked

How can I rename a schema name that was incorrectly named when it was created? Over a period of time, it has been used all over the place, grants have been made to/from this schema, etc, etc.

What is the best, most efficient and all-encompassing way to rename all references to this schema to a new schema name?

Thanks

and Tom said...


there are none, the references to it must be hunted down and changed, the schema itself would have to be exported, and imported using "fromuser=bad_name touser=good_name" (and even then, you would be responsible for grants to good_name and public synonyms)

the easiest approach would be to change your view on the name of the schema :)

that is, accept it for what it is and go with it.

Else, lots of work. A transitory method would be:

a) create good shema
b) fill it with synonyms pointing to bad
c) move things over time (leaving a synonym behind in bad_name pointing to good_name)
d) when you believe the task to be complete, start dropping the synonyms (and probably watching for what "breaks")

Rating

  (18 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

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

Tom Kyte
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

Tom Kyte
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!

Tom Kyte
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?

Tom Kyte
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.

Tom Kyte
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.


Tom Kyte
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?

Tom Kyte
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.
Tom Kyte
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!