For these parameters that happen to be part of Oracle's optimizer
environment, you can query V$SES_OPTIMIZER_ENV. However the V$ only shows
you documented parameters, but sometimes you want to know if the target
sessions has modified some underscore ones too.
So you can query the underlying X$ table, like my sesopt.sql does (I also
have sysopt.sql and sqlopt.sql to list optimizer parameters used during
hard parsing of any cursor):
SQL> @sesopt 12 skip
Show compilation environment of session 12 parameter skip
SID PARAMETER ISD VALUE
---------- ---------------------------------------- ---
-------------------------
12 skip_unusable_indexes YES TRUE
12 _optimizer_skip_scan_enabled YES TRUE
12 _dimension_skip_null YES TRUE
12 _optimizer_skip_scan_guess YES FALSE
12 _cdb_view_no_skip_migrate YES FALSE
12 _cdb_view_no_skip_restricted YES FALSE
If you want to see any other parameter (also the ones not part of optimizer
env), you an use oradebug:
SQL> ORADEBUG SETMYPID
Statement processed.
SQL> ORADEBUG DUMP MODIFIED_PARAMETERS 1
Statement processed.
This will dump any parameters the session has changed to trace:
*** 2020-02-17T16:17:28.835264-05:00
Processing Oradebug command 'DUMP MODIFIED_PARAMETERS 1'
DYNAMICALLY MODIFIED PARAMETERS:
nls_date_format = "YYYY-MM-DD HH24:MI:SS"
_optimizer_skip_scan_enabled= FALSE
--
Tanel Poder
https://tanelpoder.com/seminar
On Mon, Feb 17, 2020 at 3:59 PM Cee Pee <carlospena999@xxxxxxxxx> wrote:
Hi
Is there a way to find out the parameter values for a different user's
session that is currently executing. I can log in as sys into the database.
CP
