18

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.

adelak
  • 647
  • 4
  • 11
  • 25

6 Answers6

24

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;
mohamed stitane
  • 457
  • 1
  • 7
  • 12
  • 1
    grateful to you, it saved my day, and worked like anything...thanks. – ArifMustafa Jan 26 '18 at 11:31
  • This method will update the schema name in objects (function, cursor, procedure and lob etc...) for oracle 11c . Is it best method for rename the schema with objects ? – Nullpointer Feb 09 '19 at 15:41
  • 5
    I do not recommend this method on 12.1. The database stores the old name of the schema somewhere else, except USER$. 1) The value in dba_context.schema remains old. 2) Packages and SQL-types in the renamed schema are not compiled. I get errors like: ORA-00600: internal error code, arguments: [kotaty805], [DATA_KOEF_TAB_REC], [], [], [], [], [], [], [], [], [], [] Only DROP/CREATE helps. – alexeionin Feb 12 '19 at 17:04
  • The user$ table no longer exists – Nathan Jul 31 '22 at 20:26
12

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.

Justin Cave
  • 227,342
  • 24
  • 367
  • 384
7

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
Dba
  • 6,511
  • 1
  • 24
  • 33
1

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

Firas Nizam
  • 1,392
  • 12
  • 7
1

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/

Ahmed Ashour
  • 5,179
  • 10
  • 35
  • 56
Rodrigo de Araujo Jorge
  • 21
  • 1
-5

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

alter user USER_NAME identified by <enter_new_password>;
Pang
  • 9,564
  • 146
  • 81
  • 122
manoranjan sethy
  • 1