Oracle Fusion Middleware & Application Server

Thursday, March 24, 2016

ALTER USER RENAME - Part 3

This is part 3 of the ALTER USER RENAME series


Inspired by a Twitter Comment from Franck Pachot and as I love to test really funny and strange things with Oracle Products.



I was trying to rename the SYS user and the SYSTEM user.

SQL> alter session set "_enable_rename_user"=true;
SQL> alter system enable restricted session;
SQL> alter user sys rename to mysys identified by "Oracle12c";
alter user sys rename to mysys identified by "Oracle12c"
           *
ERROR at line 1:
ORA-42289: may not rename specified user

The same applies for the SYSTEM user


Wednesday, March 23, 2016

ALTER USER RENAME - Part 2

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#,
  defrole,resource$,ltime,exptime,astatus,lcount,defschclass,spare1,spare4,
  ext_username,spare2)
values
 (: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  ... :-)

ALTER USER RENAME series:




ALTER USER RENAME - A half official option

Many times DBA's have to rename / copy complete users/schemas from the current name to a new name within one database. Since several years DBA's are asking for a simple method inside Oracle to execute something like "ALTER USER RENAME".

Common approaches to rename users/schemas are:
  • IMPDP with REMAP_SCHEMA
  • update of sys.user$
For the option "update of sys.user$", which is in my opinion a really bad choice, see Tom Kyte's comment 



For the option "IMPD with REMAP_SCHEMA", its official but can be time consuming.

It would be really cool to have a simple SQL Statement like "ALTER USER RENAME". In the official Oracle Documentation "Database SQL Language Reference" under ALTER USER is nothing documented for a RENAME option, but it exists !!!! :-)

How does it work?

Let's say we have a user called DEMO and we want to rename this user to DEMO_NEW. The user DEMO got tables, views and functions.

# Connect as SYS to your database
conn / as sysdba
# At first we set an undocumented parameter to enable the RENAME option
alter session set "_enable_rename_user"=true;
# Bring the Database to restricted session, in order to avoid Memory Problems for huge schema
alter system enable restricted session;
# Now lets RENAME the user DEMO to DEMO_NEW
# and provide a password for the new user DEMO_NEW
alter user DEMO rename to DEMO_NEW identified by "demo";
# Disable restricted session
alter system disable restricted session;

That's it :-)

The ALTER USER RENAME works from Oracle 11.2.0.2.0 going, my above test case was done with Oracle 12.1.0.2.0.

So why is this option with ALTER USER RENAME "half official"?

  • It's not documented in the official Oracle Documentation
  • It requires an undocumented parameter
  • But there are Patches available for the ALTER USER RENAME within My Oracle Support
    • For Example: Patch 10217802 which fixes an ORA-4030 for ALTER USER RENAME

The question now is why Oracle doesn't make this cool feature official?

So far, I found one limitation, if the user holds a type which is referenced within a table you will get following error for the ALTER USER RENAME:

ORA-42287: cannot rename user on whose type a table depends

If anybody finds more limitation while testing this cool feature, just drop me message here.

But remember, DON'T DO THIS IN PRODUCTION !! Its not an official option, there might be some serious kickback effects.


ALTER USER RENAME series: