How to get session parameters changed by the user in Oracle?
This one is provided by one of the oracle gurus

There are several Initialization Parameters that influence the Optimizer and that can be modified with ALTER SESSION. We can see whether certain sessions have settings different from the System-wide settings with a query like

QL&gt; set lines 200
col name for a40
col username for a10
col value for a40
select a.sid,c.username,a.name,a.value 
from v$ses_optimizer_env a 
join v$sys_optimizer_env b on a.id=b.id 
join v$session c on a.sid=c.sid 
where a.value&lt;&gt;b.value
and c.username is not null
and c.username not in ('SYS','SYSTEM','DBSNMP') 
order by a.sid,a.name;