oracle19c之后不支持修改用户名

SQL> select banner from v$version;

BANNER
------------------------------------------------------------------------------------------------------------------------------------
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

SQL> alter session set "_enable_rename_user"=true;

Session altered.

SQL> alter system enable restricted session;

System altered.

SQL> alter user jyc rename to zxm identified by jyc;
alter user jyc rename to zxm identified by jyc
               *
ERROR at line 1:
ORA-03001: unimplemented feature

SQL> alter system disable restricted session;

System altered.

测试在18c和11g还可以修改，12c，19c不可以修改，奇怪了。

以下是18c的测试:

• Part 1: ALTER USER RENAME - A half official option: http://dirknachbar.blogspot.ch/2016/03/alter-user-rename-half-official-option.html
• Part 2: ALTER USER RENAME - Part 2: http://dirknachbar.blogspot.ch/2016/03/alter-user-rename-part-2.html
• Part 3: ALTER USER RENAME - Part 3: http://dirknachbar.blogspot.ch/2016/03/alter-user-rename-part-3.html

As I was getting access to an Oracle 18c Database, I was trying directly, if the ALTER USER RENAME still works => IT'S STILL WORKING :-)

Let's create a test user with one table and one view.

?

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

sqlplus sys/<password>@pdb01 as sysdba   SQL> select banner from v$version;   BANNER -------------------------------------------------------------------------------- Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production   SQL> create user test identified by "Oracle18c" default tablespace users quota unlimited on users;   User created.   SQL> grant connect, resource, create table, create view to test;   Grant succeeded.   SQL> connect test/Oracle18c@pdb01   SQL> create table t1 (id number, col1 varchar2(20));   Table created.   SQL> insert into t1 values (1,'Test 1');   1 row created.       SQL> insert into t1 values (2, 'Test 2');   1 row created.   SQL> commit;   Commit complete.   SQL&gt create view v1 as select * from t1;

 

Now let's rename the above created user TEST to TEST_NEW

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

sqlplus sys/<password>@pdb01 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 "Oracle18c";   User altered.   SQL> alter system disable restricted session;   System altered.

 

Now let's try to connect with the renamed user TEST_NEW

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

sqlplus test_new/Oracle18c@pdb01 as sysdba   SQL> select * from t1;    ID COL1 ---------- --------------------   1 Test 1   2 Test 2   SQL> select * from v1;    ID COL1 ---------- --------------------   1 Test 1   2 Test 2

So even with Oracle18c the undocumented feature ALTER USER RENAME is still working :-)