Mir Sayeed Hassan – Oracle Blog

Rename a schema in Oracle Database 11gR2

Find the schema name to rename with user#

SQL> select user#,NAME from SYS.user$ WHERE NAME='TEST';

USER# NAME
 ---------- ------------------------------
 208 MIR

Now modify the schema name “MIR” to “SAYEED” as shown below

SYNTAX:

SQL> UPDATE USER$ SET NAME='NEW_SCHEMA_NAME' WHERE USER#=<USER#>;

MODIFY:

SQL> UPDATE USER$ SET NAME='SAYEED' WHERE USER#=208;
 1 row updated.

Commit the changes

SQL> commit;
 Commit complete.

Modify the system SCN & Refresh the shared pool

SQL> alter system checkpoint;
 System altered.

SQL> alter system flush shared_pool;
 System altered.

If you wish to change the user password, execute the below command

SQL> alter user Sayeed identified by Sayeednew;
 User altered.

Verify

SQL> connect Sayeed/Sayeednew
 Enter password:
 Connected

===========Hence tested & Verified in our test env===========