How to get dump or list parameters set at session level?

Using oradebug one can get a dump of sessions parameters that are modified at session level, like optimization parameters. SQL> alter session set sql_trace=true; Session altered. SQL> alter session set optimizer_mode=first_rows; Session altered. -- connect to session SQL> oradebug setmypid Statement processed. SQL> oradebug dump modified_parameters 1; Statement processed. SQL> oradebug tracefile_name; i:\db\oracle\testdb\diagnostic_dest\diag\rdbms\testdb_a\testdb\trace\testdb_ora_4908.trc Contents of the tracefile: … *** 2012-05-28 14:35:25.005 Processing Oradebug command ‘dump … Continue reading How to get dump or list parameters set at session level?

Internal parameters

— view oracle internal parameters, note this can be run by SYS user — name, value, default value, session modifiable, system modifiable, description select a.ksppinm name, b.ksppstvl value, b.ksppstdf deflt, decode(bitand(a.ksppiflg/256,3),1, 'True', 'False') SESSMOD, decode(bitand(a.ksppiflg/65536,3),1,'IMMEDIATE',2,'DEFERRED',3,'IMMEDIATE','FALSE') SYSMOD, a.ksppdesc description from sys.x$ksppi a, sys.x$ksppcv b where a.indx = b.indx and a.ksppinm like '\_%' escape '\' order by name / — If SYSMOD = Immediate (The Parameter can … Continue reading Internal parameters