# 方法1：使用SQL查询
$ sqlplus / as sysdba

SQL> select value from v$diag_info where name = ‘Diag Trace’;VALUE
——————————————————————————–
/oracle/app/oracle/diag/rdbms/fgedudb/fgedudb/trace

# 方法2：查看user_dump_dest参数（11g及之前）
SQL> show parameter user_dump_dest;NAME TYPE VALUE
———————————— ———– ——————————
user_dump_dest string /oracle/app/oracle/diag/rdbms/fgedudb/fgedudb/trace

# 方法3：查看跟踪文件
$ cd /oracle/app/oracle/diag/rdbms/fgedudb/fgedudb/trace
$ ls -lh *.trc | head -20

-rw-r—– 1 oracle oinstall 10M Mar 31 23:00 fgedudb_ora_1234.trc
-rw-r—– 1 oracle oinstall 5M Mar 31 22:30 fgedudb_smon_5678.trc
-rw-r—– 1 oracle oinstall 2M Mar 31 22:00 fgedudb_pmon_9012.trc

# 查看跟踪文件大小统计
$ du -sh /oracle/app/oracle/diag/rdbms/fgedudb/fgedudb/trace
5.2G /oracle/app/oracle/diag/rdbms/fgedudb/fgedudb/trace

# 启动ADRCI
$ adrci

ADRCI: Release 19.0.0.0.0 – Production on Wed Mar 31 23:00:00 2026

Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.

ADR base = “/oracle/app/oracle”

# 设置ADR根目录
adrci> set base /oracle/app/oracle

# 查看ADR目录
adrci> show homes

ADR Homes:
diag/rdbms/fgedudb/fgedudb
diag/tnslsnr/rac1/listener

# 设置ADR Home
adrci> set homepath diag/rdbms/fgedudb/fgedudb

# 查看跟踪文件列表
adrci> show tracefile

diag/rdbms/fgedudb/fgedudb/trace/fgedudb_ora_1234.trc
diag/rdbms/fgedudb/fgedudb/trace/fgedudb_smon_5678.trc
diag/rdbms/fgedudb/fgedudb/trace/fgedudb_pmon_9012.trc

# 查看特定跟踪文件
adrci> show trace fgedudb_ora_1234.trc

# 清除旧跟踪文件（保留30天）
adrci> purge -age 720 -type trace

# 退出ADRCI
adrci> exit

# 查看最新的跟踪文件
$ ls -ltr /oracle/app/oracle/diag/rdbms/fgedudb/fgedudb/trace/*.trc | tail -10

-rw-r—– 1 oracle oinstall 10M Mar 31 23:00 fgedudb_ora_1234.trc

# 实时查看跟踪文件
$ tail -f /oracle/app/oracle/diag/rdbms/fgedudb/fgedudb/trace/fgedudb_ora_1234.trc

# 查找包含特定内容的跟踪文件
$ grep -l “ORA-01555” /oracle/app/oracle/diag/rdbms/fgedudb/fgedudb/trace/*.trc

/oracle/app/oracle/diag/rdbms/fgedudb/fgedudb/trace/fgedudb_ora_1234.trc

# 统计跟踪文件数量
$ ls -1 /oracle/app/oracle/diag/rdbms/fgedudb/fgedudb/trace/*.trc | wc -l
156

# 删除7天前的跟踪文件
$ find /oracle/app/oracle/diag/rdbms/fgedudb/fgedudb/trace -name “*.trc” -mtime +7 -delete

# 压缩30天前的跟踪文件
$ find /oracle/app/oracle/diag/rdbms/fgedudb/fgedudb/trace -name “*.trc” -mtime +30 -exec gzip {} \;

# 方法1：使用DBMS_SESSION
SQL> exec dbms_session.set_sql_trace(true);PL/SQL procedure successfully completed.

# 执行SQL语句
SQL> select * from employees;# 禁用跟踪
SQL> exec dbms_session.set_sql_trace(false);PL/SQL procedure successfully completed.

# 方法2：使用ALTER SESSION
SQL> alter session set sql_trace = true;Session altered.

# 执行SQL语句
SQL> select count(*) from orders;# 禁用跟踪
SQL> alter session set sql_trace = false;Session altered.

# 方法3：启用详细跟踪（包含等待事件和绑定变量）
SQL> alter session set events ‘10046 trace name context forever, level 12’;Session altered.

# 执行SQL语句
SQL> select * from customers where customer_id = 123;# 禁用跟踪
SQL> alter session set events ‘10046 trace name context off’;Session altered.

# 查找当前会话的跟踪文件
SQL> select value from v$diag_info where name = ‘Default Trace File’;VALUE
——————————————————————————–
/oracle/app/oracle/diag/rdbms/fgedudb/fgedudb/trace/fgedudb_ora_1234.trc

# 查找需要跟踪的会话
SQL> select sid, serial#, username, program, machine from v$session where username = ‘FGAPP_USER’;SID SERIAL# USERNAME PROGRAM MACHINE
———- ———- ———- —————– —————
123 45678 FGAPP_USER sqlplus@server1 server1

# 方法1：使用DBMS_SYSTEM
SQL> exec dbms_system.set_sql_trace_in_session(sid => 123, serial# => 45678, sql_trace => true);PL/SQL procedure successfully completed.

# 禁用跟踪
SQL> exec dbms_system.set_sql_trace_in_session(sid => 123, serial# => 45678, sql_trace => false);PL/SQL procedure successfully completed.

# 方法2：使用DBMS_MONITOR
SQL> exec dbms_monitor.session_trace_enable(session_id => 123, serial_num => 45678, waits => true, binds => true);PL/SQL procedure successfully completed.

# 禁用跟踪
SQL> exec dbms_monitor.session_trace_disable(session_id => 123, serial_num => 45678);PL/SQL procedure successfully completed.

# 方法3：使用事件
SQL> exec dbms_system.set_ev(si => 123, se => 45678, ev => 10046, le => 12, nm => ”);PL/SQL procedure successfully completed.

# 禁用跟踪
SQL> exec dbms_system.set_ev(si => 123, se => 45678, ev => 10046, le => 0, nm => ”);PL/SQL procedure successfully completed.

# 基本用法
$ tkprof fgedudb_ora_1234.trc output_file.prf

TKPROF: Release 19.0.0.0.0 – Development on Wed Mar 31 23:00:00 2026

Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.

# 查看分析报告
$ cat output_file.prf

TKPROF: Release 19.0.0.0.0 – Development on Wed Mar 31 23:00:00 2026

Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.

Trace file: fgedudb_ora_1234.trc
Sort options: default

********************************************************************************
count = number of times OCI procedure was executed
cpu = cpu time in seconds executing
elapsed = elapsed time in seconds executing
disk = number of physical reads of buffers from disk
query = number of buffers gotten for consistent read
current = number of buffers gotten in current mode (usually for update)
rows = number of rows processed by the fetch or execute call
********************************************************************************

select * from employees

call count cpu elapsed disk query current rows
——- —— ——– ———- ———- ———- ———- ———-
Parse 1 0.01 0.02 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.05 0.10 10 100 0 107
——- —— ——– ———- ———- ———- ———- ———-
total 4 0.06 0.12 10 100 0 107

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 107

Rows Row Source Operation
——- —————————————————
107 TABLE ACCESS FULL EMPLOYEES (cr=100 pr=10 pw=0 time=1000 us)

# 高级用法：按CPU时间排序
$ tkprof fgedudb_ora_1234.trc output_file.prf sort=(cpu)

# 包含执行计划
$ tkprof fgedudb_ora_1234.trc output_file.prf explain=system/password

# 包含等待事件
$ tkprof fgedudb_ora_1234.trc output_file.prf waits=yes

#!/bin/bash
# trace_file_management.sh – 跟踪文件管理脚本
# from:www.itpux.com.qq113257174.wx:itpux-com
# web `http://www.fgedu.net.cn`

ORACLE_SID=fgedudb
TRACE_DIR=/oracle/app/oracle/diag/rdbms/fgedudb/fgedudb/trace
LOG_FILE=/tmp/trace_management.log

echo “========================================” > $LOG_FILE
echo “Oracle Trace File Management” >> $LOG_FILE
echo “Date: $(date)” >> $LOG_FILE
echo “Database: $ORACLE_SID” >> $LOG_FILE
echo “========================================” >> $LOG_FILE
echo “” >> $LOG_FILE

# 1. 统计跟踪文件
echo “1. Trace File Statistics” >> $LOG_FILE
echo “————————” >> $LOG_FILE
echo “Total trace files: $(ls -1 $TRACE_DIR/*.trc 2>/dev/null | wc -l)” >> $LOG_FILE
echo “Total trace file size: $(du -sh $TRACE_DIR | awk ‘{print $1}’)” >> $LOG_FILE
echo “” >> $LOG_FILE

# 2. 查找大跟踪文件
echo “2. Large Trace Files (> 100MB)” >> $LOG_FILE
echo “——————————-” >> $LOG_FILE
find $TRACE_DIR -name “*.trc” -size +100M -exec ls -lh {} \; >> $LOG_FILE 2>/dev/null
echo “” >> $LOG_FILE

# 3. 清理旧跟踪文件（保留7天）
echo “3. Cleaning up old trace files (> 7 days)” >> $LOG_FILE
echo “—————————————–” >> $LOG_FILE
OLD_FILES=$(find $TRACE_DIR -name “*.trc” -mtime +7)
if [ -n “$OLD_FILES” ]; then
echo “Deleting files:” >> $LOG_FILE
echo “$OLD_FILES” >> $LOG_FILE
find $TRACE_DIR -name “*.trc” -mtime +7 -delete
else
echo “No old trace files to delete” >> $LOG_FILE
fi
echo “” >> $LOG_FILE

# 4. 压缩旧跟踪文件（保留30天）
echo “4. Compressing trace files (> 30 days)” >> $LOG_FILE
echo “————————————–” >> $LOG_FILE
COMPRESS_FILES=$(find $TRACE_DIR -name “*.trc” -mtime +30 2>/dev/null)
if [ -n “$COMPRESS_FILES” ]; then
echo “Compressing files:” >> $LOG_FILE
echo “$COMPRESS_FILES” >> $LOG_FILE
find $TRACE_DIR -name “*.trc” -mtime +30 -exec gzip {} \; 2>/dev/null
else
echo “No trace files to compress” >> $LOG_FILE
fi
echo “” >> $LOG_FILE

echo “========================================” >> $LOG_FILE
echo “Management Completed” >> $LOG_FILE
echo “========================================” >> $LOG_FILE

echo “Trace file management completed”
cat $LOG_FILE

# 创建logrotate配置文件
# vi /etc/logrotate.d/oracle-trace

/oracle/app/oracle/diag/rdbms/fgedudb/fgedudb/trace/*.trc {
daily
rotate 7
compress
delaycompress
missingok
notifempty
dateext
dateformat -%Y%m%d
sharedscripts
maxage 30
size 100M
}

# 测试logrotate配置
# logrotate -d /etc/logrotate.d/oracle-trace

# 手动执行轮换
# logrotate -f /etc/logrotate.d/oracle-trace

# 查看轮换后的日志
# ls -lh /oracle/app/oracle/diag/rdbms/fgedudb/fgedudb/trace/*.trc*

# 场景：用户反映某条SQL查询很慢

# 1. 查找正在执行的慢SQL
SQL> select sid, serial#, sql_id, sql_text, elapsed_time/1000000 as seconds
from v$sql
where elapsed_time > 10000000
order by elapsed_time desc;SID SERIAL# SQL_ID SQL_TEXT SECONDS
———- ———- ————- —————————————- ———-
123 45678 abc123def456 select * from orders where order_date > 125.5

# 2. 为该会话启用详细跟踪
SQL> exec dbms_monitor.session_trace_enable(session_id => 123, serial_num => 45678, waits => true, binds => true);PL/SQL procedure successfully completed.

# 3. 让用户再次执行SQL
# …用户执行SQL…

# 4. 禁用跟踪
SQL> exec dbms_monitor.session_trace_disable(session_id => 123, serial_num => 45678);PL/SQL procedure successfully completed.

# 5. 查找跟踪文件
SQL> select p.tracefile
from v$process p, v$session s
where p.addr = s.paddr
and s.sid = 123;TRACEFILE
——————————————————————————–
/oracle/app/oracle/diag/rdbms/fgedudb/fgedudb/trace/fgedudb_ora_1234.trc

# 6. 使用tkprof分析跟踪文件
$ tkprof fgedudb_ora_1234.trc slow_sql.prf sort=(cpu) explain=system/password

# 7. 查看分析报告，发现问题：全表扫描
# 解决方案：创建索引
SQL> create index idx_orders_order_date on orders(order_date);Index created.

# 8. 验证性能改善
SQL> select /*+ gather_plan_statistics */ * from orders where order_date > sysdate – 30;SQL> select * from table(dbms_xplan.display_cursor(null,null,’ALLSTATS LAST’));

# 场景：应用报告死锁错误

# 1. 检查告警日志中的死锁信息
$ grep -A 50 “ORA-00060” /oracle/app/oracle/diag/rdbms/fgedudb/fgedudb/trace/alert_fgedudb.log

Wed Mar 31 22:00:00 2026
ORA-00060: Deadlock detected. More info in file /oracle/app/oracle/diag/rdbms/fgedudb/fgedudb/trace/fgedudb_ora_1234.trc.

# 2. 查看死锁跟踪文件
$ cat /oracle/app/oracle/diag/rdbms/fgedudb/fgedudb/trace/fgedudb_ora_1234.trc

*** 2026-03-31 22:00:00.000
*** SESSION ID:(123.45678) 2026-03-31 22:00:00.000
*** CLIENT ID:() 2026-03-31 22:00:00.000
*** SERVICE NAME:(fgedudb) 2026-03-31 22:00:00.000
*** MODULE NAME:(sqlplus@server1) 2026-03-31 22:00:00.000
*** ACTION NAME:() 2026-03-31 22:00:00.000

Deadlock graph:
———Blocker(s)——– ———Waiter(s)———
Resource Name process session holds waits process session holds waits
TX-00010005-00001234 123 45678 X 124 45679 X
TX-00010006-00001235 124 45679 X 123 45678 X

session 45678: DID 0001-007B-00000001
session 45679: DID 0001-007C-00000001

Rows waited on:
Session 45678: obj – rowid = 00012345 – AAASdfAAEAAAAAeAAA
(dictionary objn – 74565, file – 4, block – 123, slot – 0)
Session 45679: obj – rowid = 00012346 – AAASdgAAEAAAAAfAAA
(dictionary objn – 74566, file – 4, block – 124, slot – 0)

# 3. 分析死锁原因
# 会话123更新表A的行1，等待表B的行2
# 会话124更新表B的行2，等待表A的行1

# 4. 查找涉及的对象
SQL> select owner, object_name, object_type from dba_objects where object_id = 74565;OWNER OBJECT_NAME OBJECT_TYPE
———- ——————– ——————-
FGAPP_USER ORDERS TABLE

SQL> select owner, object_name, object_type from dba_objects where object_id = 74566;OWNER OBJECT_NAME OBJECT_TYPE
———- ——————– ——————-
FGAPP_USER ORDER_ITEMS TABLE

# 5. 解决方案：统一更新顺序，先更新ORDER_ITEMS，再更新ORDERS
# 或者使用SELECT FOR UPDATE NOWAIT避免死锁

# 从跟踪文件中提取等待事件
$ grep “WAIT” fgedudb_ora_1234.trc | head -20

WAIT #140541740402440: nam=’db file sequential read’ ela= 12345 file#=4 block#=12345 blocks=1 obj#=74565 tim=1234567890123
WAIT #140541740402440: nam=’db file scattered read’ ela= 45678 file#=4 block#=12346 blocks=8 obj#=74565 tim=1234567890456
WAIT #140541740402440: nam=’log file sync’ ela= 78901 buffer#=1234 sync scn=123456789 tim=1234567890789

# 统计等待事件
$ grep “WAIT” fgedudb_ora_1234.trc | awk ‘{print $3}’ | sort | uniq -c | sort -rn

150 nam=’db file sequential read’
80 nam=’db file scattered read’
25 nam=’log file sync’
10 nam=’CPU’
5 nam=’SQL*Net message from client’

# 计算总等待时间
$ grep “WAIT” fgedudb_ora_1234.trc | awk -F’ela= ‘ ‘{print $2}’ | awk ‘{sum += $1} END {print “Total wait time: ” sum ” microseconds”}’

Total wait time: 123456789 microseconds

# 问题：某个跟踪文件持续增长，占用大量磁盘空间

# 1. 查找大跟踪文件
$ ls -lhS /oracle/app/oracle/diag/rdbms/fgedudb/fgedudb/trace/*.trc | head -10

-rw-r—– 1 oracle oinstall 5.2G Mar 31 23:00 fgedudb_ora_1234.trc

# 2. 查看跟踪文件内容，找出跟踪仍在进行的会话
$ tail -100 /oracle/app/oracle/diag/rdbms/fgedudb/fgedudb/trace/fgedudb_ora_1234.trc

*** 2026-03-31 23:00:00.000
*** SESSION ID:(123.45678) 2026-03-31 23:00:00.000

# 3. 检查该会话是否仍在启用跟踪
SQL> select s.sid, s.serial#, s.username, s.program, s.sql_trace
from v$session s
where s.sid = 123;SID SERIAL# USERNAME PROGRAM SQL_TRAC
———- ———- ———- —————– ——–
123 45678 FGAPP_USER sqlplus@server1 ENABLED

# 4. 禁用跟踪
SQL> exec dbms_monitor.session_trace_disable(session_id => 123, serial_num => 45678);PL/SQL procedure successfully completed.

# 5. 备份和清空跟踪文件（可选）
$ cp /oracle/app/oracle/diag/rdbms/fgedudb/fgedudb/trace/fgedudb_ora_1234.trc /backup/trace_backup/$ > /oracle/app/oracle/diag/rdbms/fgedudb/fgedudb/trace/fgedudb_ora_1234.trc

# 6. 检查所有会话的跟踪状态
SQL> select sid, serial#, username, sql_trace, sql_trace_waits, sql_trace_binds
from v$session
where sql_trace = ‘ENABLED’;# 7. 建立监控，防止长时间启用跟踪
# 创建定期检查脚本，自动禁用超过24小时的跟踪

# Oracle自带工具
1. tkprof
– 功能：格式化分析SQL跟踪文件
– 优点：Oracle官方工具，使用简单
– 适用：SQL性能分析

2. trcsess
– 功能：合并多个跟踪文件
– 优点：可以按会话、时间等条件筛选
– 适用：多个会话的跟踪分析

3. ADRCI
– 功能：管理ADR、查看和清除跟踪文件
– 优点：Oracle官方工具，功能完整
– 适用：日常跟踪文件管理

4. DBMS_MONITOR
– 功能：启用/禁用会话级、客户端级跟踪
– 优点：功能强大，控制精细
– 适用：灵活的跟踪配置

# OS工具
1. grep/sed/awk
– 功能：文本搜索、过滤、处理
– 优点：快速、灵活、无需安装
– 适用：快速分析跟踪文件

2. tail/head
– 功能：查看文件开头和结尾
– 优点：简单易用
– 适用：实时查看跟踪文件

3. find
– 功能：查找和操作文件
– 优点：强大的文件查找和处理能力
– 适用：跟踪文件清理和管理

# 第三方工具
1. TOAD
– 功能：图形化查看和分析跟踪文件
– 优点：界面友好，功能强大
– 适用：开发和调试

2. PL/SQL Developer
– 功能：集成的跟踪文件分析
– 优点：与开发环境集成
– 适用：开发人员使用

3. OS Watcher
– 功能：系统和数据库监控
– 优点：Oracle提供的免费工具
– 适用：性能分析和故障诊断

# 自定义工具
1. Shell/Python脚本
– 功能：自定义跟踪文件管理和分析
– 优点：灵活、可定制
– 适用：特定需求的管理和分析

2. cron/scheduled tasks
– 功能：定时执行任务
– 优点：系统自带，配置简单
– 适用：定期清理和监控