[20190522]How to get dump or list parameters set at session level.txt

[20190522]How to get dump or list parameters set at session level.txt

1.環境:

SCOTT@book> @ ver1

PORT_STRING                    VERSION        BANNER

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

x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

SCOTT@book> @ spid

       SID    SERIAL# PROCESS                  SERVER    SPID       PID  P_SERIAL# C50

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

        30        535 42553                    DEDICATED 42554       26        245 alter system kill session '30,535' immediate;

alter session set sql_trace=true;

alter session set optimizer_mode=first_rows;

2.測試:

SYS@book> oradebug setospid 42554

Oracle pid: 26, Unix process pid: 42554, image: oracle@gxqyydg4 (TNS V1-V3)

SYS@book> oradebug dump modified_parameters 1;

Statement processed.

SYS@book> oradebug tracefile_name;

/u01/app/oracle/diag/rdbms/book/book/trace/book_ora_42554.trc

--//檢查/u01/app/oracle/diag/rdbms/book/book/trace/book_ora_42554.trc.

*** 2019-05-22 09:50:30.330

Received ORADEBUG command (#1) 'dump modified_parameters 1' from process 'Unix process pid: 43053, image: <none>'

DYNAMICALLY MODIFIED PARAMETERS:

  nls_language             = AMERICAN

  nls_territory            = AMERICA

  nls_sort                 = BINARY

  nls_date_language        = AMERICAN

  nls_date_format          = YYYY-MM-DD HH24:MI:SS

  nls_currency             = $

  nls_numeric_characters   = .,

  nls_iso_currency         = AMERICA

  nls_calendar             = GREGORIAN

  nls_time_format          = HH.MI.SSXFF AM

  nls_timestamp_format     = YYYY-MM-DD HH24:MI:SS.FF

  nls_time_tz_format       = HH.MI.SSXFF AM TZR

  nls_timestamp_tz_format  = YYYY-MM-DD HH24:MI:SS.FF TZH:TZM

  nls_dual_currency        = $

  nls_comp                 = BINARY

  sql_trace                = TRUE

  optimizer_mode           = FIRST_ROWS

*** 2019-05-22 09:50:30.330

Finished processing ORADEBUG command (#1) 'dump modified_parameters 1'

*** 2019-05-22 09:50:38.410

Received ORADEBUG command (#2) 'tracefile_name' from process 'Unix process pid: 43053, image: <none>'

*** 2019-05-22 09:50:38.410

Finished processing ORADEBUG command (#2) 'tracefile_name'