Oracle 18c - ALTER USER RENAME

Posted by Dirk Nachbar on Friday, March 09, 2018
2 years ago, I have published an article series about the undocumented feature "ALTER USER RENAME":

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.
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
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
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 :-)