No more ALTER USER RENAME under Oracle 19c

Posted by Dirk Nachbar on Thursday, June 20, 2019 with No comments
Some times ago, I have published a blog series about the undocumented feature "ALTER USER RENAME":
Now I had some time to test, if this undocumented feature is still available under the latest Oracle 19c Database and I have to say: NO. Oracle took away this feature from the latest Oracle 19c :-(

$ sqlplus sys/<password>@db19cpdb1 as sysdba
SQL*Plus: Release - Production on Thu Jun 20 13:14:31 2019

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Connected to:
Oracle Database 19c Enterprise Edition Release - Production

SQL> select banner from v$version;

Oracle Database 19c Enterprise Edition Release - Production

SQL> create user test identified by "Oracle19c" default tablespace users quota unlimited on users;

User created.

SQL> grant connect, resource, create table, create view to test;

Grant succeeded.

SQL> connect test/Oracle19c@db19cpdb1

SQL> create table t1 (id number, col1 varchar2(50));

Table created.

SQL> insert into t1 values (1, 'Test 1');

1 row created.

SQL> commit;

Commit completed.

SQL> create view v1 as select * from t1;

View created.

SQL> exit

Now let's try if the undocumented feature "ALTER USER RENAME" is still working:

sqlplus sys/<password>@db19cpdb1 as sysdba

SQL> alter session set "_enable_rename_user"=true;

Session altered.

SQL> alter system enable restricted session;

System altered.

SQL> alter user test rename to test_new identified by "Oracle19c";

alter user test rename to test_new identified by "Oracle19c"
ERROR at line 1:
ORA-03001: unimplemented feature

SQL> alter system disable restricted session;

System altered.

As you can see, if you try to perform the ALTER USER RENAME command, you will receive immediately an "ORA-03001: unimplemented feature" :-(

I really hope, that one day Oracle will pick up the Database Idea proposal "Need a command to rename a schema" ( from the Database Idea Community Forum. If you haven't currently voted up for this Database Idea in the Database Idea Community Forum, please do so ...

Categories: ,