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