How to change the schema name?

Question

I created a user:

CREATE USER gds_map IDENTIFIED BY gds_map; 

And now I need to change the name. I tried to update or find another way, but haven’t found anything yet.

I will be glad to any hint.

Answer 1

You cannot (at least not be supported or vaguely respond). You will need to create a new user with a new username and delete the old user.

Answer 2

If you want to change the schema name, you must have preveledegs in USER $

1. Get the schema name identifier

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

2. change the schema name

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

3. commit completed

 SQL> COMMIT; 

4. change the SCN system

 SQL> ALTER SYSTEM CHECKPOINT; 

5. Then update shared_pool

 SQL> ALTER SYSTEM FLUSH SHARED_POOL; 

6. Change the new scheme password

 SQL> ALTER USER new_schema IDENTIFIED BY new_pass; 

Answer 3

There are no methods for renaming an oracle schema. A.

Try

1-Create a new scheme

2-Export the old schema,

 $exp owner=test2 

3 - import the old scheme into a new scheme,

 $imp fromuser=test2 touser=newuser_name 

Answer 4

do it

1- login as sys

2- do the following: update sys.user $ set name = 'new_name' where name = 'old_name';

3- then restart the database

Answer 5

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

The workaround I'm using is to "clone" the user into the same database with a different name using loopback dblink.

This is very fast and, after all, after a successful check, you can abandon the old circuit.

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

Hi,

Rodrigo Jorge

Answer 6

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

 alter user USER_NAME identified by <enter_new_password>;