CODEWITHSUNDEEP
oracle-databaseplsqlschemaoracle11gr2
anria
anria

Reputation: 657

How to change schema name?

I have created a user:

CREATE USER gds_map
IDENTIFIED BY gds_map;

And now I need to change a name. I tried to update or find other way but have not found nothing yet.

I will be glad of any hint.

Upvotes: 18

Views: 95739

Answers (6)

Rodrigo de Araujo Jorge
Rodrigo de Araujo Jorge

Reputation: 21

I've needed to do this so often that I even wrote an article about this topic

The workaround that I use is to "clone" the user to the same DB with a different name, using loopback dblink.

It's very fast and in the end, after a successful checkup, you can drop the old schema.

Check it here: http://www.dbarj.com.br/en/2014/11/rename-schema-oracle-11g-loopback-dblink/

Upvotes: 1

mohamed stitane
mohamed stitane

Reputation: 509

If you want to modify a schema's name,you should have the preveledegs on USER$

1. Get Id of a schema's name

SQL> select user#,NAME from SYS.user$ WHERE NAME='TEST';
USER# NAME
---------- ------------------------------
*93* TEST

2. modify the schema's name 

SQL> UPDATE USER$ SET NAME='NEW_SCHEMA_NAME' WHERE USER#=93;

3. finished commit 

SQL> COMMIT;

4. modify the system SCN

SQL> ALTER SYSTEM CHECKPOINT;

5.Then refresh shared_pool

SQL> ALTER SYSTEM FLUSH SHARED_POOL;

6. Modify the new schema's password

SQL> ALTER USER new_schema  IDENTIFIED BY new_pass;

Upvotes: 22

manoranjan sethy
manoranjan sethy

Reputation: 1

In oracle database you cannot rename your username but you can change your password.

alter user USER_NAME identified by <enter_new_password>;

Upvotes: -5

Firas Nizam
Firas Nizam

Reputation: 1422

do this

1- login as sys

2- execute this: update sys.user$ set name= 'new_name' where name = 'old_name';

3- then restart the database

Upvotes: 1

Dba
Dba

Reputation: 6639

No methods exists to rename an oracle schema.

Try,

1-Create new schema

2-Export the old schema,

$exp owner=test2

3-Import old schema to new schema,

$imp fromuser=test2 touser=newuser_name

Upvotes: 7

Justin Cave
Justin Cave

Reputation: 231661

You can't (at least not in a supported or vaguely responsible way). You'd need to create a new user with the new username and drop the old user.

Upvotes: 14

Related Questions