[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'