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
2I'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.
0In oracle database you cannot rename your username but you can change your password.
alter user USER_NAME identified by <enter_new_password>; 
