How do you Rename Oracle Database Schema?

This is a very common question to every oracle dba person and sometimes we need to rename a database schema. So, in this article, we are going to learn how to change schema names in oracle step by step.

Read: How to Rename Database Using Controlfile

Can we rename a schema in Oracle 12c?

Yes, you can change the schema name also in oracle 12c and later versions.

How do you rename a schema?

We have two different ways to rename a schema listed below:

  1. Using schema ID
  2. Using Data Pump (Export & Import)

In this article, we are using the first method to rename schema using schema ID.

Step 1. Find the ID of the respected schema using the below command. We have one schema SHRIPAL and change it into SACHIN.

SQL> select user#,NAME from SYS.user$ WHERE NAME='SHRIPAL';
rename oracle database schema

Step 2. Change the name against the user-id

EXP: SQL> UPDATE USER$ SET NAME='NEW_USERNAME' WHERE USER#=OLD_USER_ID;

SQL> UPDATE USER$ SET NAME='SACHIN' WHERE USER#=97;
rename oracle database schema

Step 3. Commit the checkpoint and flush the shared pool.

SQL> ALTER SYSTEM CHECKPOINT;
SQL> ALTER SYSTEM FLUSH SHARED_POOL;
rename oracle database schema

Step 4. Change the password of the new schema.

SQL> ALTER USER SACHIN IDENTIFIED BY SACHIN;

I hope now you successfully rename a database schema, write your fillings in the comment box.

Chat with me on Instagram

1 thought on “Rename an Oracle Database schema?”

Leave a Comment

%d bloggers like this:
Enable Notifications    OK No thanks