Rename a user in Oracle

Can one rename a database user (schema)?

No, this is listed as Enhancement Request 158508. Workaround:

    Step1. Do a user-level export of user A
    Step2. create new user B
    Step3. import system/manager fromuser=A touser=B
    Step4. drop user A 

Yes, we can rename database user, there is some trick

You can rename a user but it is not supported by Oracle. To properly rename the user, follow this guide: 1. login sqlplus using sys user as sysdba

D:\>SET ORACLE_SID=DEMO
D:\>sqlplus /nolog

SQL*Plus: Release 10.1.0.2.0 - Production on Fri Jun 13 16:59:05 2008

Copyright (c) 1982, 2004, Oracle.  All rights reserved.

SQL>conn sys/oracle as sysdba
Connected.

2. select the user id and which name from user$

SQL>select user#, name from user$;

     USER# NAME
---------- ------------------------------
         0 SYS
         1 PUBLIC
         2 CONNECT
         3 RESOURCE
         4 DBA
         5 SYSTEM
        ...........
        ...........
        64 DEMO
        ...........
        ...........

70 rows selected.

3. Change your old user name into new user name. Be careful when u update user$

---update user$ set name='<newname>'; where user#=<old user#>;

SQL>update user$ set name='SHOW' where user#=64;

1 row updated.

SQL>commit;

Commit complete.

4. Shutdown the database and start up, or restart database service.

SQL>shutdown;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL>startup
ORACLE instance started.

Total System Global Area  171966464 bytes
Fixed Size                   787988 bytes
Variable Size             145488364 bytes
Database Buffers           25165824 bytes
Redo Buffers                 524288 bytes
Database mounted.
Database opened.
SQL>

5. alter the renamed user password using system user or sys or dba user user.

SQL>alter user SHOW identified by SHOW;

User altered.

6. Now try to connect database with renamed user and its password

SQL>conn SHOW/SHOW
Connected.
SQL>exit

That's it!!!

Copyright - There is no copyright on the code. You can copy, change and distribute it freely. Just mentioning this site should be fair

(C) June 2008, manivelcode