How to change schema name?

Question

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.

Answer 1

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;

Answer 2

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/

Regards,

Rodrigo Jorge