Truth behind show user

SQLPLUS command line provides a show user help which can print which user you are logged in as. Just learned new today that it is not hitting the DB to get the results.

Tom, who else can bring it to light. I am just 12 years down the line to have read his message.

The content points to this article

The SQLPLUS caches the username as soon as a "connection" is established and saves it for reuse. Whenever somebody issues a show user, it just returns from the cache.

Internally for the first time it will be doing a select user from dual; to set the value. Its like a global variable

Next time onwards it will simply return from its variable instead of running a query. Even if the Connection is killed, SQLPLUS returns the variable value. (This was new to me)

----Copy pasting shamelessly the example from Tom Kyte's article for my reference:

[tkyte@desktop tkyte]$ sqlplus /
 
SQL*Plus: Release 10.1.0.4.0 - Production on Wed Aug 3 10:01:01 2005
 
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
 
 
Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.4.0 - Production
With the Partitioning, OLAP and Data Mining options
 
ops$tkyte@ORA10G> @getspid
 
DEDICATED_SE CLIENTPID
------------ ------------
8391         8389
 
ops$tkyte@ORA10G> !kill -9 8391
 
ops$tkyte@ORA10G> show user
USER is "OPS$TKYTE"
ops$tkyte@ORA10G> select user from dual;
select user from dual
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
 
 
ERROR:
ORA-03114: not connected to ORACLE

 
 
ops$tkyte@ORA10G> show user
USER is "OPS$TKYTE"
ops$tkyte@ORA10G>