No more ALTER USER RENAME under Oracle 19c

Posted by Dirk Nachbar on Thursday, June 20, 2019
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 :-(

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
#
$ sqlplus sys/<password>@db19cpdb1 as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Jun 20 13:14:31 2019
Version 19.3.0.0.0
 
Copyright (c) 1982, 2019, Oracle.  All rights reserved.
 
 
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
 
SQL> select banner from v$version;
 
BANNER
--------------------------------------------------------------------------------
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - 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:

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
#
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" (https://community.oracle.com/ideas/2334) 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: ,