How to Rename a User Through Oracle

There is nothing straight forward command in oracle to rename a user. You have to do it through several steps. In following I have shown it.

1)Determine the user or schema which you want to rename. Suppose you want to rename ARJU user to ARJUUSA.

2)Export the objects the the desired user to a dump file. You can use either data pump export (expdp) or exp(original export). If you use data pump then export as

$expdp schemas=ARJU

If you use original export you can use as,
$exp owner=ARJU

To know more about data pump export and original export please have a look at,
Data Pump Export Import
Original Export Import

3)Create new User ARJUUSA.
SQL>CREATE USER ARJUUSA IDENTIFIED BY A;

4)Drop user Arju.
SQL>DROP USER ARJU CASCADE;

5)Import the Objects in the dumpfile from Arju to ARJUUSA.
If you used expdp then use,
$impdp REMAP_SCHEMA=ARJU:ARJUUSA
If you used exp then use,
$imp FROMUSER=ARJU TOUSER=ARJUUSA

Special Notes
Note that you may think that you can rename a user directory by updating name column from USER$ table and then changing the password of the newly updated user. But this is not supported system by oracle. So avoid it. Below is a test scenario of this method but don't use it as oracle does not support it.

SQL> conn t/t@203.88.110.25:1522/maestro
Connected.

SQL> create table test(a number primary key);
Table created.

SQL> create table test2(a number ,foreign key (a) references test);
Table created.

SQL> create or replace procedure test_p as begin dbms_output.put_line('Test'); end;
2  /
Procedure created.
SQL> col object_name for a20
SQL> select object_name, object_type from user_objects;

OBJECT_NAME          OBJECT_TYPE
-------------------- -------------------
SYS_C0011082         INDEX
TEST                 TABLE
TEST2                TABLE
TEST_P               PROCEDURE

SQL> conn sys/a@203.88.110.25:1522/maestro as sysdba
Connected.

SQL> update user$ set name='T_NEWNAME' where name='T';
1 row updated.

SQL> alter user T_NEWNAME identified by a;
User altered.

SQL> conn t_newname/a@203.88.110.25:1522/maestro
Connected.

SQL> select object_name, object_type, status from user_objects;

OBJECT_NAME          OBJECT_TYPE         STATUS
-------------------- ------------------- -------
SYS_C0011082         INDEX               VALID
TEST                 TABLE               VALID
TEST2                TABLE               VALID
TEST_P               PROCEDURE           VALID

Related Documents
Data Pump Export Import
Original Export Import