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:
- Using schema ID
- 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';
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;
Step 3. Commit the checkpoint and flush the shared pool.
SQL> ALTER SYSTEM CHECKPOINT;
SQL> ALTER SYSTEM FLUSH SHARED_POOL;
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
Is this supported by Oracle or just a hack?
you can check
It is not supported by oracle and Noone advise to do this using this method.
Instead of Exp data using datapump and
Import data in new user schema using datapump .
And do post check for database objects count.
The only way to rename a schema is to export and import the objects into the new schema, and then drop the old schema.There is no command to directly rename the schema in oracle database.
Please kindly refer to below notes for more details.
How to Rename a User Schema using EXPORT or IMPORT (Doc ID 132885.1)
Import DataPump Parameter REMAP_SCHEMA – How to Move Database Objects from one Schema to Another (Doc ID 260225.1)