Scripts:找出后台trace文件的脚本sess_user_trace_file_location.sql
来源:互联网 发布:百度云备案域名出售 编辑:程序博客网 时间:2024/04/29 09:25
-- +----------------------------------------------------------------------------+
-- | Jeffrey M. Hunter |
-- | jhunter@idevelopment.info |
-- | www.idevelopment.info |
-- |----------------------------------------------------------------------------|
-- | Copyright (c) 1998-2012 Jeffrey M. Hunter. All rights reserved. |
-- |----------------------------------------------------------------------------|
-- | DATABASE : Oracle |
-- | FILE : sess_user_trace_file_location.sql |
-- | CLASS : Session Management |
-- | PURPOSE : Oracle writes TRACE to the directory based on the value of your |
-- | "user_dump_dest" parameter in init.ora file. The trace files |
-- | use the "System Process ID" as part of the file name to ensure |
-- | a unique file for each user session. The following query helps |
-- | the DBA to determine where the TRACE files will be written and |
-- | the name of the file it would create for its particular |
-- | session. |
-- | NOTE : As with any code, ensure to test this script in a development |
-- | environment before attempting to run it in production. |
-- +----------------------------------------------------------------------------+
SET TERMOUT OFF;
COLUMN current_instance NEW_VALUE current_instance NOPRINT;
SELECT rpad(instance_name, 17) current_instance FROM v$instance;
SET TERMOUT ON;
PROMPT
PROMPT +------------------------------------------------------------------------+
PROMPT | Report : User Session Trace File Location |
PROMPT | Instance : ¤t_instance |
PROMPT +------------------------------------------------------------------------+
SET ECHO OFF
SET FEEDBACK 6
SET HEADING ON
SET LINESIZE 180
SET PAGESIZE 50000
SET TERMOUT ON
SET TIMING OFF
SET TRIMOUT ON
SET TRIMSPOOL ON
SET VERIFY OFF
CLEAR COLUMNS
CLEAR BREAKS
CLEAR COMPUTES
COLUMN "Trace File Path" FORMAT a80 HEADING 'Your trace file with path is:'
SELECT
a.trace_path || ' > ' || b.trace_file "Trace File Path"
FROM
( SELECT value trace_path
FROM v$parameter
WHERE name='user_dump_dest'
) a
, ( SELECT c.instance || '_ora_' || spid ||'.trc' TRACE_FILE
FROM v$process,
(select lower(instance_name) instance from v$instance) c
WHERE addr = ( SELECT paddr
FROM v$session
WHERE (audsid, sid) = ( SELECT
sys_context('USERENV', 'SESSIONID')
, sys_context('USERENV', 'SID')
FROM dual
)
)
) b
/
-- | Jeffrey M. Hunter |
-- | jhunter@idevelopment.info |
-- | www.idevelopment.info |
-- |----------------------------------------------------------------------------|
-- | Copyright (c) 1998-2012 Jeffrey M. Hunter. All rights reserved. |
-- |----------------------------------------------------------------------------|
-- | DATABASE : Oracle |
-- | FILE : sess_user_trace_file_location.sql |
-- | CLASS : Session Management |
-- | PURPOSE : Oracle writes TRACE to the directory based on the value of your |
-- | "user_dump_dest" parameter in init.ora file. The trace files |
-- | use the "System Process ID" as part of the file name to ensure |
-- | a unique file for each user session. The following query helps |
-- | the DBA to determine where the TRACE files will be written and |
-- | the name of the file it would create for its particular |
-- | session. |
-- | NOTE : As with any code, ensure to test this script in a development |
-- | environment before attempting to run it in production. |
-- +----------------------------------------------------------------------------+
SET TERMOUT OFF;
COLUMN current_instance NEW_VALUE current_instance NOPRINT;
SELECT rpad(instance_name, 17) current_instance FROM v$instance;
SET TERMOUT ON;
PROMPT
PROMPT +------------------------------------------------------------------------+
PROMPT | Report : User Session Trace File Location |
PROMPT | Instance : ¤t_instance |
PROMPT +------------------------------------------------------------------------+
SET ECHO OFF
SET FEEDBACK 6
SET HEADING ON
SET LINESIZE 180
SET PAGESIZE 50000
SET TERMOUT ON
SET TIMING OFF
SET TRIMOUT ON
SET TRIMSPOOL ON
SET VERIFY OFF
CLEAR COLUMNS
CLEAR BREAKS
CLEAR COMPUTES
COLUMN "Trace File Path" FORMAT a80 HEADING 'Your trace file with path is:'
SELECT
a.trace_path || ' > ' || b.trace_file "Trace File Path"
FROM
( SELECT value trace_path
FROM v$parameter
WHERE name='user_dump_dest'
) a
, ( SELECT c.instance || '_ora_' || spid ||'.trc' TRACE_FILE
FROM v$process,
(select lower(instance_name) instance from v$instance) c
WHERE addr = ( SELECT paddr
FROM v$session
WHERE (audsid, sid) = ( SELECT
sys_context('USERENV', 'SESSIONID')
, sys_context('USERENV', 'SID')
FROM dual
)
)
) b
/
0 0
- Scripts:找出后台trace文件的脚本sess_user_trace_file_location.sql
- Scripts:给出系统pid找出sql的脚本pid4sql.sql
- Scripts:根据sql文本找出相应SQL信息的脚本find_sql.sql
- Scripts:找出磁盘读最多的SQL的脚本 perf_top_sql_by_disk_reads.sql
- Scripts:找出使用最多buffer get的SQL脚本perf_top_sql_by_buffer_gets.sql
- Scripts:找出使用最高的10个表的脚本perf_top_10_tables.sql
- Scripts:列出用户信息的脚本sec_users.sql
- Scripts:找出10个最高使用的过程perf_top_10_procedures.sql
- Scripts:查看数据文件使用率的脚本(包括临时表空间的文件哦)dba_file_space_usage.sql
- Scripts:重建索引的脚本(看懂脚本再用)rebuild_indx.sql
- Scripts:查看回滚的SQL的脚本 huigun.sql
- 获取 trace 文件名词的脚本
- Scripts:生成随机数的SQL脚本dba_random_number.sql
- Scripts:查询等待事件的SQL脚本owi_event_names.sql
- Scripts:查看未提交的事务的脚本CurrentActivity.sql
- Scripts:报告数据库中的top segment的脚本dba_top_segments.sql
- Scripts:显示AWR中执行计划的脚本dplan_allstats.sql
- Scripts:诊断DRM信息的脚本DRMDIAG.SQL
- 安装JDK出现"Windows Installer程序包有问题,此安装需要的DLL不能运行"解决方法
- 深入理解计算机系统之旅(二)信息在计算机中的表示和处理
- ArrayList遍历的4种方法
- Android 使用意图播放本地视频
- NIO--SocketChannel发送HTTP请求
- Scripts:找出后台trace文件的脚本sess_user_trace_file_location.sql
- Linux对外连接端口数限制
- MyBatis3整合Spring3(工数)
- leetcode Add Two Numbers
- 数据同步处理方案
- OpenGL ES之glUniform函数
- 让SD卡转接成SSD固态硬盘
- js data 格式转换
- Scripts:报告所有用户session信息的脚本sess_user_sessions.sql