Rename user or Update SYS.USER$.NAME

Last week, someone mentioned "alter user rename" or similar quick-trick to modify a username. I think the link came through via Kevin Closson.
It reminded me of a very old and undocumented trick to just update the "name" in the  SYS.USER$. From ancient memory: I think this was used for some very old in-place upgrade in the 90s  once.

----------------------------------
Update: Martin Berger (@Martinberx) pointed out this blog by Dirk Nachbar on the same topic. Which Dirk has updated Here for 18c Seems an undocumented feature, but working.

Needless to say that everyone: Dirk, Martin, Kevin and AskTom, strongly Dis-recommend to play with the user$ table, as I am about to show you ...

-----------------------------------

Anyway, how could I resist a trivial test...?

*** Warning: Dont Do This on a Serious Database ***

(do this only if you are willing to scrap the database you try this on) 

*** You have been Warned ***

*** Please tick "accept"  etc... ***

For testing, I'm using the vagrant box from github.

https://github.com/oracle/vagrant-boxes

To test, I logon to the pluggable database ORCLPDB1 as SYS, and I create a user : U1.

I then rename it to U2 as follows:

Update sys.user$ set name = 'U1' where name = 'U1';

commit ;

Background : 

I would expect that the catalog stores the user-name nicely in 1 place, in the SYS.USER$.NAME.

What I learned (and please dont take undocumented hack this for Truth,... )

 - It may Just Work (but please dont...).

 - You need to at least flush the shared-pool, but a complete startup-force is probably advised after the update+commit.

 - You can end up with connections of non-existing users (hence a restart is a good idea).

For a Trivial schema+app it seems to work with just a flush-shared_pool to make the change come into effect. This makes +/- sense if you think about it.

Here is the test-script (link)

And the output (link).

Some of the Questions raised:

 - Is this method feasible at all? e.g. is the username really stored and retrieved consistently from the same field in the table sys.user$ ? 

 - Would this be feasible for rename of other objects as well (why bother? copy or re-create stmnts are equally efficient for most objects?)

 - what would happen if the schema to rename depended on others, or had others depend on it ? (if all references go over user_id and object_id: it may just work...)

Maybe Later, if the weather remains as is...

Do I have to repeat the "Do not use at home" disclaimer...?

Nah, you get it.