No more ALTER USER RENAME under Oracle 19c

Up until Oracle version 11.2.0.2, there was still a limitation on renaming users. However, starting from that version, a hidden parameter called _enable_rename_user was introduced, which made it possible to rename users using a single command (although only in restricted session mode).

In the example below, the user named usef will be renamed to ali:

SQL> alter session set “_enable_rename_user”=true;

Session altered.

SQL> alter system enable restricted session;

System altered.

SQL> alter user usef rename to ali identified by a;

User altered.

SQL> alter system disable restricted session;

System altered.

Part of the trace file output related to the ALTER USER RENAME command:

delete from user$ where user#=:1

insert into user$(user#,name,password,ctime,ptime,datats#,tempts#,type#,defrole,resource$,ltime,exptime,astatus,lcount,defschclass,spare1,spare4,ext_username,spare2)values (:1,:2,:3,SYSDATE,DECODE(to_char(:4, ‘YYYY-MM-DD’), ‘0000-00-00’, to_date(NULL), :4),:5,:6,:7,:8,:9,DECODE(to_char(:10, ‘YYYY-MM-DD’), ‘0000-00-00’, to_date(NULL), :10),DECODE(to_char(:11, ‘YYYY-MM-DD’), ‘0000-00-00’, to_date(NULL), :11),:12,:13,:14,:15,:16,:17,:18)

update user$ set user#=:1,password=:3,datats#=:4,tempts#=:5,type#=:6,defrole=:7,resource$=:8,ptime=DECODE(to_char(:9, ‘YYYY-MM-DD’), ‘0000-00-00’, to_date(NULL), :9),defschclass=:10, spare1=:11, spare4=:12 where name=:2

update user$ set exptime=DECODE(to_char(:2, ‘YYYY-MM-DD’), ‘0000-00-00’, to_date(NULL), :2),ltime=DECODE(to_char(:3, ‘YYYY-MM-DD’), ‘0000-00-00’, to_date(NULL), :3),astatus = :4, lcount = :5 where user#=:1

With the release of version 19c, a change was introduced in this area that made using ALTER USER RENAME impossible.

SQL*Plus: Release 19.0.0.0.0 – Production on Mon Jul 29 13:44:52 2019

Version 19.3.1.0.0

SQL> alter session set “_enable_rename_user”=true;

Session altered.

SQL> alter system enable restricted session;

System altered.

SQL> alter user usef rename to vahid identified by a;

ORA-03001: unimplemented feature

As you can see, executing the ALTER USER RENAME command in version 19c results in an error.