How do we change the name of the user / schema in an Oracle database

• Author
• Recent Posts

Stratos Matzouranis

The journey began in 2008 where I officially started working in the field of IT (IT). Starting the first semester of school I realized a special attraction towards databases and automation. I have been involved with databases such as Microsoft SQL Server / Oracle Database, data analysis and automations using the command line (CLI), Visual Basic for Applications and Python. Through years of experience I have developed these capabilities so that I can make my life easier. For me, the purpose of every IT guy and every office worker is to have the knowledge so that through tools he can work a little but produce a lot. Through this his website DataPlatform.gr I try to offer knowledge and propose solutions to everyday problems.

Certifications:

Latest posts by Stratos Matzouranis (see all)

• How do we automate the process of checking the integrity of databases in SQL Server without using a maintenance plan - 10 May 2024
• How can we connect Oracle Database to SQL Server using Oracle Gateway on Windows - 5 April 2024
• How can we get email whenever PGA memory exceeds a limit in Oracle database - 1 March 2024

Using one Oracle Database we may at some point need to rename a user and a schema. Until version 11g this was not possible and we had to create a new user and export/import with remap_schema. However, we can now rename the usage after first activating a parameter. In this article we will see how it is done step by step.

First, we connect to the database with sqlplus:

[oracle@dp-gr ~]$ sqlplus / as sysdba

Then we run the following command to enable the parameter on the next restart of the instance:

alter system set "_enable_rename_user"=true scope=spfile;

System altered

We stop the instance:

shutdown immediate;

Database closed.
Database dismounted.
ORACLE instance shut down.

We start the instance in restricted mode (only sysdba user can connect):

startup restrict;

ORACLE instance started.

Total System Global Area  411041792 bytes
Fixed Size                  2925024 bytes
Variable Size             327159328 bytes
Database Buffers           75497472 bytes
Redo Buffers                5459968 bytes
Database mounted.
Database opened.

In our example we want the user /schema “stratos" let's change it to "Nikos". If we now run a select on a table belonging to it we will see that it will allow results:

select * from stratos.dept;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     FLORIDA

We run the command below that changes the name:

alter user stratos rename to nikos identified by "kwdikos";

User altered.

In case it shows us an error and it won't let us change the username, we can try the following instead of the previous command:

update sys.user$ set name= 'nikos' where name = 'stratos';
commit;

alter user stratos identified by "kwdikos";

shutdown immediate;

startup restrict;

If we run the same select again, we will see that it says that the table does not exist:

select * from stratos.dept;

select * from stratos.dept
                      *
ERROR at line 1:
ORA-00942: table or view does not exist

If we run it under the schema “Nikos” but we will see that the change has been completed and the select returns results normally:

select * from nikos.dept;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     FLORIDA

To open the database so that it is accessible to all users, we run the following:

alter system disable restricted session;

System altered.