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 collect the actual execution plan from queries using Extended Event and how do we read its data - 2 December 2024
• How do we find what permissions a user has on an Oracle database - 1 November 2024
• How we enable Unified Auditing in Oracle Database - 7 October 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.