Posted by Dirk Nachbar on Wednesday, March 23, 2016 with No comments
It seems that my first blog post today "ALTER USER RENAME - A half official option" got some great attentions :-)

How did I come to the ALTER USER RENAME command? Just a lucky punch due to my customer. My customer were trying to rename an user in an Oracle Database with several objects (tables, view, functions, types and so) by simply updating the sys.user$ table :-)
The result were better than expected, just synonyms had to be recreated, but a function which is referencing a type caused some problems thats leads to an undocumented ORA-00600.

So I was starting to do some searches in My Oracle Support and finally came across the My Oracle Support Note 10217802.8

Its simply a Bug summing up for a discovered ORA-4030 errors message for the command "ALTER USER .. RENAME". Quick check in the official SQL Language Reference Guides, shows clearly there is no "ALTER USER RENAME" option. So whats going on here? Next search in My Oracle Support with simply the search string "10217802" brings up some Patches for the Bug

Patches for a non-existing feature sounds really interesting :-) when you check out the patches, you will see that the patches were created 10th November 2010 !!!!

So since more than 5 years the really cool feature is existing, but nowhere really described.

What I discover so far:

  • Users which are holding TABLES, VIEWS, FUNCTIONS, PROCEDURES can be renamed without any problems
  • Even GRANTS will be transferred correctly :-)
  • Problems I discovered so far with TYPES and SYNONYMS
    • SYNONYM owner will be switched correctly, but the table_owner stays untouched, so the synonym will point to an invalid reference. Not nice, but its ok

Whats underneath the ALTER USER RENAME command?

The easiest way is just set trace event 10046 :-)

alter session set tracefile_identifier='RENAME';
alter session set events '10046 trace name context forever,level 12';
alter session set "_enable_rename_user"=true;
alter system enable restricted session;
alter user DEMO rename to DEMO_NEW identified by "demo";
alter session set events '10046 trace name context off';
alter system disable restricted session;

In generell the ALTER USER RENAME is doing some modifications (update, delete and insert) against the sys.user$ table and other sys tables, here some examples from the 10046 tracefile:

update user$ set ext_username=:1
where ext_username = (select name from user$ where user#=:2)

delete from user$
where user#=:1

insert into user$(user#,name,password,ctime,ptime,datats#,tempts#,type#,
 (:1,:2,:3,SYSDATE,DECODE(to_char(:4, 'YYYY-MM-DD'), '0000-00-00',
  to_date(NULL), :4),:5,:6,:7,:8,:9,DECODE(to_char(:10, 'YYYY-MM-DD'),
  '0000-00-00', to_date(NULL), :10),DECODE(to_char(:11, 'YYYY-MM-DD'),
  '0000-00-00', to_date(NULL), :11),:12,:13,:14,:15,:16,:17,:18)

update user$ set user#=:1,password=:3,datats#=:4,tempts#=:5,type#=:6,defrole=
  :7,resource$=:8,ptime=DECODE(to_char(:9, 'YYYY-MM-DD'), '0000-00-00',
  to_date(NULL), :9),defschclass=:10, spare1=:11, spare4=:12
where name=:2

update user$ set exptime=DECODE(to_char(:2, 'YYYY-MM-DD'), '0000-00-00',
  to_date(NULL), :2),ltime=DECODE(to_char(:3, 'YYYY-MM-DD'), '0000-00-00',
  to_date(NULL), :3),astatus = :4, lcount = :5
where user#=:1

And what Oracle says?
Official reply from My Oracle Support:

The fact that a certain fix for an undocumented feature is included in a bundle patch such a PSU does not make it official at all. The only official and supported features are those present in the documentation.

Lets see, if some day this cool feature will become official  ... :-)