查看当前sql_trace文件名
SELECT d.VALUE|| '/'
|| LOWER (RTRIM (i.INSTANCE, CHR (0)))
|| '_ora_'
|| p.spid
|| '.trc'
AS "trace_file_name"
FROM (SELECT p.spid
FROM v$mystat m, v$session s, v$process p
WHERE m.statistic# = 1 AND s.SID = m.SID AND p.addr = s.paddr) p,
(SELECT t.INSTANCE
FROM v$thread t, v$parameter v
WHERE v.NAME = 'thread'
AND (v.VALUE = 0 OR t.thread# = TO_NUMBER (v.VALUE))) i,
(SELECT VALUE
FROM v$parameter
WHERE NAME = 'user_dump_dest') d;
查询结果：/u01/app/oracle/diag/rdbms/omrep/omrep/trace/omrep_ora_3409.trc
alter session set sql_trace=true;
show parameter nls_date_format; value显示为空
alter session set sql_trace=false;
!tkprof /u01/app/oracle/diag/rdbms/omrep/omrep/trace/omrep_ora_3409.trc scott.sql
!cat scott.sql
显示为：
SELECT NAME NAME_COL_PLUS_SHOW_PARAM,DECODE(TYPE,1,'boolean',2,'string',3,
'integer',4,'file',5,'number', 6,'big integer', 'unknown') TYPE,
DISPLAY_VALUE VALUE_COL_PLUS_SHOW_PARAM
FROM
V$PARAMETER WHERE UPPER(NAME) LIKE UPPER(:NMBIND_SHOW_OBJ) ORDER BY
NAME_COL_PLUS_SHOW_PARAM,ROWNUM
实际show parameter nls_date_format，就是执行的上面的语句
alter session set nls_date_format='yyyymmdd';
show parameter nls_date_format; value还是为空
select * from v$nls_parameters where parameter like '%NLS_DATE%'; 显示为yymmdd
select * from v$fixed_view_definition where view_name='V$PARAMETER'; 显示为from GV$PARAMETER
select * from v$fixed_view_definition where view_name='GV$PARAMETER'; 显示为 from x$ksppi x, x$ksppcv y
select * from v$fixed_view_definition where view_name='V$NLS_PARAMETERS';显示为 from from GV$NLS_PARAMETERS
select * from v$fixed_view_definition where view_name='GV$NLS_PARAMETERS';显示为from x$nls_parameters
可能alter session的时候x$ksppi x, x$ksppcv y 和x$nls_parameters没有同步
不过alter system set nls_date_format='yyyymmdd';之后show parameter nls_date_format; value有值