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.

6 Answers

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;

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.

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

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

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:

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

alter user USER_NAME identified by <enter_new_password>;