轉載-Oracle 常用的dump命令
Oracle 跟蹤事件 set event
http://blog.csdn.net/tianlesoftware/archive/2009/12/10/4977827.aspx
| 事件 | 說明 | 例子 |
| Event 10013 - Monitor Transaction Recovery | 在Startup時跟蹤事務恢復 | ALTER SESSION SET EVENTS '10013 trace name context forever, level 1'; |
| Event 10015 - Dump Undo Segment Headers- | 在事務恢復後做Dump回退段頭資訊 | ALTER SESSION SET EVENTS '10015 trace name context forever, level 1'; |
| Event 10032 - Dump Sort Statistics | Dump排序的統計資訊 | ALTER SESSION SET EVENTS '10032 trace name context forever, level 10'; |
| Event 10033 - Dump Sort Intermediate Run Statistics | 排序過程中，記憶體排序區和臨時表空間的互動情況 | ALTER SESSION SET EVENTS '10033 trace name context forever, level 10'; |
| Event 10045 - Trace Free List Management Operations | FREELIST的管理操作 | ALTER SESSION SET EVENTS '10045 trace name context forever, level 1'; |
| Event 10046 - Enable SQL Statement Trace | 跟蹤SQL,有執行計劃,邦定變數和等待的統計資訊,level 12最詳細。 | ALTER SESSION SET EVENTS '10046 trace name context forever, level 12'; LEVEL定義如下： 1:SQL 語句，執行計劃和執行狀態 4:1的內容加上繫結變數資訊 8:1的資訊加上等待事件資訊 12:1＋4＋8 |
| Event 10053 - Dump Optimizer Decisions | 在分析SQL語句時,Dump出優化器所做的選擇,級別level 1最詳細 | ALTER SESSION SET EVENTS '10053 trace name context forever, level 1'; LEVEL定義如下： 1：狀態和估算資訊 2：只顯示估算資訊 |
| Event 10060 - Dump Predicates | DUMP SQL語句中的斷語資訊。需要在需要DUMP的使用者下建立以下表 CREATE TABLE kkoipt_table (c1 INTEGER, c2 VARCHAR2(80)); 斷語資訊會寫入該表 CREATE TABLE kkoipt_table (c1 INTEGER, c2 VARCHAR2(80)); 斷語資訊會寫入該表 |
ALTER SESSION SET EVENTS '10060 trace name context forever, level 1'; |
| Event 10065 - Restrict Library Cache Dump Output for State Object Dumps | 限制物件狀態DUMP的時候LIBRARY CACHE資訊的詳細程度 | ALTER SESSION SET EVENTS '10065 trace name context forever, level level'; |
| Event 10079 - Dump SQL*Net Statistics- | Dump SQL*NeT的統計資訊 | ALTER SESSION SET EVENTS '10079 trace name context forever, level 2'; |
| Event 10081 - Trace High Water Mark Changes | HWM的改變 | ALTER SESSION SET EVENTS '10081 trace name context forever, level 1'; |
| Event 10104 - Dump Hash Join Statistics | HASH JOIN的統計資訊 | ALTER SESSION SET EVENTS '10104 trace name context forever, level 10'; |
| Event 10128 - Dump Partition Pruning Information | 分割槽表調整資訊 | ALTER SESSION SET EVENTS '10128 trace name context forever, level level'; Level取值： 1 Dump pruning descriptor for each partitioned object 0x0002 Dump partition iterators 0x0004 Dump optimizer decisions about partition-wise joins 0x0008 Dump ROWID range scan pruning information 在9.0.1或者後面的版本,在level 2後還需要建立如下的表: CREATE TABLE kkpap_pruning ( partition_count NUMBER, iterator VARCHAR2(32), partition_level VARCHAR2(32), order_pt VARCHAR2(12), call_time VARCHAR2(12), part# NUMBER, subp# NUMBER, abs# NUMBER ); |
| Event 10200 - Dump Consistent Reads | DUMP一致讀的資訊 | ALTER SESSION SET EVENTS '10200 trace name context forever, level 1'; |
| Event 10201 - Dump Consistent Read Undo Application | DUMP一致性讀涉及UNDO資訊的內容 | ALTER SESSION SET EVENTS '10201 trace name context forever, level 1'; |
| Event 10220 - Dump Changes to Undo Header | Dump出Undo頭資訊的改變 | ALTER SESSION SET EVENTS '10220 trace name context forever, level 1'; |
| Event 10221 - Dump Undo Changes | Dump Undo的改變 | ALTER SESSION SET EVENTS '10221 trace name context forever, level 7'; |
| Event 10224 - Dump Index Block Splits / Deletes | 索引塊的分裂和D刪除資訊 | ALTER SESSION SET EVENTS '10224 trace name context forever, level 1'; |
| Event 10225 - Dump Changes to Dictionary Managed Extents | DUMP欄位管理的擴充套件變化 | ALTER SESSION SET EVENTS '10225 trace name context forever, level 1'; |
| Event 10231 | 全表掃描時跳過壞塊，在有壞塊的情況下做資料拯救時很有用 | ALTER SYSTEM SET EVENTS '10231 trace name context forever,level 10'; |
| Event 10241 - Dump Remote SQL Execution | 遠端SQL語句的執行資訊 | ALTER SESSION SET EVENTS '10241 trace name context forever, level 1'; |
| Event 10246 - Trace PMON Process | 跟蹤PMON程式 | 只能修改引數，不能用ALTER SYSTEM event = "10246 trace name context forever, level 1" |
| Event 10248 - Trace Dispatcher Processes | 跟蹤DISPATCHER的工作情況 | event = "10248 trace name context forever, level 10" |
| Event 10249 - Trace Shared Server (MTS) Processes- | 跟蹤共享伺服器的工作情況 | event = "10249 trace name context forever, level 10" |
| Event 10270 - Debug Shared Cursors | 跟蹤共享CURSORS的情況 | event = "10270 trace name context forever, level 10" |
| Event 10299 - Debug Prefetching | 跟蹤表資料塊和索引資料塊的PREFETCHING | event = "10299 trace name context forever, level 1" |
| Event 10357 - Debug Direct Path | ALTER SESSION SET EVENTS '10357 trace name context forever, level 1'; | |
| Event 10390 - Dump Parallel Execution Slave Statistics | 跟蹤並行操作中的SLAVE的狀態 | ALTER SESSION SET EVENTS '10390 trace name context forever, level 1; |
| Event 10391-Dump Parallel Execution Granule Allocation | 跟蹤並行操作的粒度 | ALTER SESSION SET EVENTS '10391 trace name context forever, level 2'; |
| Event 10393 - Dump Parallel Execution Statistics | 跟蹤並行操作的狀態（每個SLAVE單獨列出狀態） | ALTER SESSION SET EVENTS '10393 trace name context forever, level 1'; |
| Event 10500 - Trace SMON Process | 跟蹤SMON程式 | event = "10500 trace name context forever, level 1" |
| Event 10608 - Trace Bitmap Index Creation | 跟蹤BITMAP索引建立的詳細過程 | ALTER SESSION SET EVENTS '10608 trace name context forever, level 10'; |
| Event 10704 - Trace Enqueues | 跟蹤鎖的使用情況 | ALTER SESSION SET EVENTS '10704 trace name context forever, level 1'; |
| Event 10706 - Trace Global Enqueue Manipulation | 跟蹤全域性鎖的使用情況 | ALTER SESSION SET EVENTS '10706 trace name context forever, level 1'; |
| Event 10708 - Trace RAC Buffer Cache | 跟蹤RAC環境下的BUFFER CACHE | ALTER SESSION SET EVENTS '10708 trace name context forever, level 10'; |
| Event 10710 - Trace Bitmap Index Access | 跟蹤點陣圖索引的訪問情況 | ALTER SESSION SET EVENTS '10710 trace name context forever, level 1'; |
| Event 10711 - Trace Bitmap Index Merge Operation | 跟蹤點陣圖索引合併操作 | ALTER SESSION SET EVENTS '10711 trace name context forever, level 1'; |
| Event 10712 - Trace Bitmap Index OR Operation | 跟蹤點陣圖索引或操作情況 | ALTER SESSION SET EVENTS '10712 trace name context forever, level 1'; |
| Event 10713 - Trace Bitmap Index AND Operation | 跟蹤點陣圖索引與操作 | ALTER SESSION SET EVENTS '10713 trace name context forever, level 1'; |
| Event 10714 - Trace Bitmap Index MINUS Operation | 跟蹤點陣圖索引minus操作 | ALTER SESSION SET EVENTS '10714 trace name context forever, level 1'; |
| Event 10715 - Trace Bitmap Index Conversion to ROWIDs Operation | 跟蹤點陣圖索引轉換ROWID操作 | ALTER SESSION SET EVENTS '10715 trace name context forever, level 1'; |
| Event 10716 - Trace Bitmap Index Compress/Decompress | 跟蹤點陣圖索引壓縮和解壓縮情況 | ALTER SESSION SET EVENTS '10716 trace name context forever, level 1'; |
| Event 10717 - Trace Bitmap Index Compaction | ALTER SESSION SET EVENTS '10717 trace name context forever, level 1'; | |
| Event 10719 - Trace Bitmap Index DML | 跟蹤點陣圖索引列的DML操作（引起點陣圖索引改變的DML操作） | ALTER SESSION SET EVENTS '10719 trace name context forever, level 1'; |
| Event 10730 - Trace Fine Grained Access Predicates | 跟蹤細粒度審計的斷語 | ALTER SESSION SET EVENTS '10730 trace name context forever, level 1'; |
| Event 10731 - Trace CURSOR Statements | 跟蹤CURSOR的語句情況 | ALTER SESSION SET EVENTS '10731 trace name context forever, level level'; LEVEL定義 1 Print parent query and subquery 2 Print subquery only |
| Event 10928 - Trace PL/SQL Execution | 跟蹤PL/SQL執行情況 | ALTER SESSION SET EVENTS '10928 trace name context forever, level 1'; |
| Event 10938 - Dump PL/SQL Execution Statistics | 跟蹤PL/SQL執行狀態。使用前需要執行rdbms/admin下的tracetab.sql | ALTER SESSION SET EVENTS '10938 trace name context forever, level 1'; |
| flush_cache | 重新整理BUFFER CACHE | ALTER SESSION SET EVENTS 'immediate trace name flush_cache'; |
| DROP_SEGMENTS | 手工刪除臨時段。當這些臨時段無法自動清除的時候可以手工清除 | alter session set events 'immediate trace name DROP_SEGMENTS level ts#+1'; ts#是指要刪除臨時段的表空間的ts# |
一.Memory Dumps
1).Global Area
ALTER SESSION SET EVENTS ‘immediate trace name global_area level n’;
1 包含PGA
2 包含SGA
4 包含UGA
8 包含indrect memory
2).Library Cache
ALTER SESSION SET EVENTS ‘immediate trace name library_cache level n’;
1 library cache統計資訊
2 包含hash table histogram
3 包含object handle
4 包含object結構（Heap 0）
3).Row Cache
ALTER SESSION SET EVENTS ‘immediate trace name row_cache level n’;
1 row cache統計資訊
2 包含hash table histogram
8 包含object結構
4).Buffers
ALTER SESSION SET EVENTS ‘immediate trace name buffers level n’;
1 buffer header
2 level 1 + block header
3 level 2 + block contents
4 level 1 + hash chain
5 level 2 + hash chain
6 level 3 + hash chain
8 level 4 + users/waiters
9 level 5 + users/waiters
10 level 6 + users/waiters
5).Buffer
ALTER SESSION SET EVENTS ‘immediate trace name buffer level n’;
n為某個指定block的rdba，該命令可以轉儲某個block在buffer中的所有版本。
6).Heap
ALTER SESSION SET EVENTS ‘immediate trace name heapdump level level’;
1 PGA摘要
2 SGA摘要
4 UGA摘要
8 Current call(CGA)摘要
16 User call(CGA)摘要
32 Large call(LGA)摘要
1025 PGA內容
2050 SGA內容
4100 UGA內容
8200 Current call內容
16400 User call內容
32800 Large call內容
7).Sub Heap
Oracle 9.0.1版本之前
ALTER SESSION SET EVENTS ‘immediate trace name heapdump_addr level n’;
若n為subheap的地址，轉儲的是subheap的摘要資訊
若n為subheap的地址+1，轉儲的則是subheap的內容
Oracle 9.2.0版本之後
ALTER SESSION SET EVENTS ‘immediate trace name heapdump_addr level n, addr m’;
其中m為subheap的地址
n為1轉儲subheap的摘要，n為2轉儲subheap的內容
8).Process State
ALTER SESSION SET EVENTS ‘immediate trace name processstate level n’;
9).System State
ALTER SESSION SET EVENTS ‘immediate trace name systemstate level n’;
10).Error State
ALTER SESSION SET EVENTS ‘immediate trace name errorstack level n’;
0 Error stack
1 level 0 + function call stack
2 level 1 + process state
3 level 2 + context area
11).Hang Analysis
ALTER SESSION SET EVENTS ‘immediate trace name hanganalyze level n’;
12).Work Area
ALTER SESSION SET EVENTS ‘immediate trace name workareatab_dump level n’;
1 SGA資訊
2 Workarea Table摘要資訊
3 Workarea Table詳細資訊
13).Latches
ALTER SESSION SET EVENTS ‘immediate trace name latches level n’;
1 latch資訊
2 統計資訊
14).Events
ALTER SESSION SET EVENTS ‘immediate trace name events level n’;
1 session
2 process
3 system
15).Locks
ALTER SESSION SET EVENTS ‘immediate trace name locks level n’;
16).Shared Server Process
ALTER SESSION SET EVENTS ‘immediate trace name shared_server_state level n’;
n取值為1~14
17).Background Messages
ALTER SESSION SET EVENTS ‘immediate trace name bg_messages level n’;
n為pid+1
二.File Dumps
1).Block
Oracle 7之前
ALTER SESSION SET EVENTS ‘immediate trace name blockdump level n’;
n為block的rdba
Oracle8以後
ALTER SYSTEM DUMP DATAFILE file# BLOCK block#;
ALTER SYSTEM DUMP DATAFILE file#
BLOCK MIN minimum_block#
BLOCK MAX maximum_block#;
2).Tree Dump
ALTER SESSION SET EVENTS ‘immediate trace name treedump level n’;
n為object_id
3).Undo Segment Header
ALTER SYSTEM DUMP UNDO_HEADER ’segment_name’;
4).Undo for a Transaction
ALTER SYSTEM DUMP UNDO BLOCK ’segment_name’ XID xidusn xidslot xidsqn;
5).File Header
ALTER SESSION SET EVENTS ‘immediate trace name file_hdrs level n’;
1 控制檔案中的檔案頭資訊
2 level 1 + 檔案頭資訊
3 level 2 + 資料檔案頭資訊
10 level 3
6).Control file
ALTER SESSION SET EVENTS ‘immediate trace name controlf level n’;
1 檔案頭資訊
2 level 1 + 資料庫資訊 + 檢查點資訊
3 level 2 + 可重用節資訊
10 level 3
7).Redo log Header
ALTER SESSION SET EVENTS ‘immediate trace name redohdr level n’;
1 控制檔案中的redo log資訊
2 level 1 + 檔案頭資訊
3 level 2 + 日誌檔案頭資訊
10 level 3
8).Redo log
ALTER SYSTEM DUMP LOGFILE ‘FileName’;
ALTER SYSTEM DUMP LOGFILE ‘FileName’
SCN MIN MinimumSCN
SCN MAX MaximumSCN
TIME MIN MinimumTime
TIME MAX MaximumTime
LAYER Layer
OPCODE Opcode
DBA MIN FileNumber . BlockNumber
DBA MAX FileNumber . BlockNumber
RBA MIN LogFileSequenceNumber . BlockNumber
RBA MAX LogFileSequenceNumber . BlockNumber;
其中time = (((((yyyy – 1988)) * 12 + mm – 1) * 31 + dd – 1) * 24 + hh) * 60 + mi) * 60 + ss;
9).Loghist
ALTER SESSION SET EVENTS ‘immediate trace name loghist level n’;
1 dump控制檔案中最早和最遲的日誌歷史項
1 dump 2^n個日誌歷史項
三， Trace 檔案使用示例
udump下的trc檔案可以通過配置不讓產生,利用命令
alter system set sql_trace=false;
其他的不能修改，只能手動的啟動trace，手動的關閉trace.
比如：
alter session set events 'immediate trace name library_cache|controlf|systemstate|processstate|file_hdrs|REDOHDR level 10';
alter session set events 'immediate trace name off';
alter session set events '10046 trace name context forever,level 12';
alter session set events '10046 trace name context off';
alter system set events '10046 trace name context forever,level 12';
alter system set events '10046 trace name context off';
1. 使用autotrace：
set autotrace ON | ON EXPLAIN | ON STATISTICS | TRACEONLY | TRACEONLY EXPLAIN
set autotrace off
這個用法是最簡單方便的，執行sql結束之後，會自動在同個視窗顯示sql的執行計劃和統計資訊。
2. 使用set events context：
SQL> alter session set sql_trace=true;
SQL> alter session set sql_trace=false;
屬於當前session級設定
大多數的情況下，我們使用sql_trace跟蹤當前程式。通過跟蹤當前程式可以發現當前操作的後臺資料庫遞迴活動(這在研究資料庫新特性時尤其有效)，研究SQL執行，發現後臺錯誤等。
alter session set events '10046 trace name context forever,level 12';
alter session set events '10046 trace name context off';
10046事件概述：
10046事件是Oracle提供的內部事件，是對SQL_TRACE的增強.
10046事件可以設定以下四個級別:
1 - 啟用標準的SQL_TRACE功能,等價於sql_trace
4 - Level 1 加上繫結值(bind values)
8 - Level 1 + 等待事件跟蹤
12 - Level 1 + Level 4 + Level 8
類似sql_trace，10046事件可以在全域性設定，也可以在session級設定。
3. 使用set events immediate
alter session set events 'immediate trace name library_cache|controlf|systemstate|processstate|file_hdrs|REDOHDR level 10';
alter session set events 'immediate trace name off';
Level =1 ,轉儲Library cache統計資訊
Level =2 ,轉儲hash table概要
Level =4 ,轉儲Library cache物件，只包含基本資訊
Level =8 ,轉儲Library cache物件，包含詳細資訊(包括child references,pin waiters等)
Level =16,增加heap sizes資訊
Level =32,增加heap資訊
9i清理buffer cache
alter session set events 'immediate trace name flush_cache level 1';
alter session set events = 'immediate trace name flush_cache'
4. 使用set events errorstack
alter session set events 'err_num trace name errorstack level 10';
alter session set events 'err_num trace name errorstack off'
err_num=報錯程式碼，如ORA-00942 應該在err_num填入942
5. 使用dbms_support(trace別的session)：
exec dbms_support.start_trace_in_session(sid=>XX,seiral#=>XXX,wait=>true,binds=>true);
exec dbms_support.stop_trace_in_session(sid=>XX,seiral#=>XXX);
這類trace用到很少，以後再研究。
6. 使用oradebug(trace別的session)：
oradebug setospid (PID 能從ps -ef 中得出)
oradebug unlimit
oradebug event 10046 trace name context forever,level 12;
oradebug event 10046 trace name context off;
這類同樣用到很少。
7. 使用dbms_system.set_ev：
exec dbms_system.set_ev(sid,serial#,10046,trace_level,'username');
exec dbms_system.set_ev(sid,serial#,10046,0,'username');
Oracle 跟蹤事件 set event
http://blog.csdn.net/tianlesoftware/archive/2009/12/10/4977827.aspx
一.Memory Dumps
1).Global Area
ALTER SESSION SET EVENTS ‘immediate trace name global_area level n’;
1 包含PGA
2 包含SGA
4 包含UGA
8 包含indrect memory
2).Library Cache
ALTER SESSION SET EVENTS ‘immediate trace name library_cache level n’;
1 library cache統計資訊
2 包含hash table histogram
3 包含object handle
4 包含object結構（Heap 0）
3).Row Cache
ALTER SESSION SET EVENTS ‘immediate trace name row_cache level n’;
1 row cache統計資訊
2 包含hash table histogram
8 包含object結構
4).Buffers
ALTER SESSION SET EVENTS ‘immediate trace name buffers level n’;
1 buffer header
2 level 1 + block header
3 level 2 + block contents
4 level 1 + hash chain
5 level 2 + hash chain
6 level 3 + hash chain
8 level 4 + users/waiters
9 level 5 + users/waiters
10 level 6 + users/waiters
5).Buffer
ALTER SESSION SET EVENTS ‘immediate trace name buffer level n’;
n為某個指定block的rdba，該命令可以轉儲某個block在buffer中的所有版本。
RDBA (Relative Data Block Address)，rdba就是rowid中的rfile#+block# 通過查詢ROWID可以得出。
6).Heap
ALTER SESSION SET EVENTS ‘immediate trace name heapdump level level’;
1 PGA摘要
2 SGA摘要
4 UGA摘要
8 Current call(CGA)摘要
16 User call(CGA)摘要
32 Large call(LGA)摘要
1025 PGA內容
2050 SGA內容
4100 UGA內容
8200 Current call內容
16400 User call內容
32800 Large call內容
7).Sub Heap
Oracle 9.0.1版本之前
ALTER SESSION SET EVENTS ‘immediate trace name heapdump_addr level n’;
若n為subheap的地址，轉儲的是subheap的摘要資訊
若n為subheap的地址+1，轉儲的則是subheap的內容
Oracle 9.2.0版本之後
ALTER SESSION SET EVENTS ‘immediate trace name heapdump_addr level n, addr m’;
其中m為subheap的地址
n為1轉儲subheap的摘要，n為2轉儲subheap的內容
8).Process State
ALTER SESSION SET EVENTS ‘immediate trace name processstate level n’;
9).System State
ALTER SESSION SET EVENTS ‘immediate trace name systemstate level n’;
10).Error State
ALTER SESSION SET EVENTS ‘immediate trace name errorstack level n’;
0 Error stack
1 level 0 + function call stack
2 level 1 + process state
3 level 2 + context area
11).Hang Analysis
ALTER SESSION SET EVENTS ‘immediate trace name hanganalyze level n’;
12).Work Area
ALTER SESSION SET EVENTS ‘immediate trace name workareatab_dump level n’;
1 SGA資訊
2 Workarea Table摘要資訊
3 Workarea Table詳細資訊
13).Latches
ALTER SESSION SET EVENTS ‘immediate trace name latches level n’;
1 latch資訊
2 統計資訊
14).Events
ALTER SESSION SET EVENTS ‘immediate trace name events level n’;
1 session
2 process
3 system
15).Locks
ALTER SESSION SET EVENTS ‘immediate trace name locks level n’;
16).Shared Server Process
ALTER SESSION SET EVENTS ‘immediate trace name shared_server_state level n’;
n取值為1~14
17).Background Messages
ALTER SESSION SET EVENTS ‘immediate trace name bg_messages level n’;
n為pid+1
二.File Dumps
1).Block
Oracle 7之前
ALTER SESSION SET EVENTS ‘immediate trace name blockdump level n’;
n為block的rdba
Oracle8以後
ALTER SYSTEM DUMP DATAFILE file# BLOCK block#;
ALTER SYSTEM DUMP DATAFILE file#
BLOCK MIN minimum_block#
BLOCK MAX maximum_block#;
2).Tree Dump
ALTER SESSION SET EVENTS ‘immediate trace name treedump level n’;
n為object_id
3).Undo Segment Header
ALTER SYSTEM DUMP UNDO_HEADER ’segment_name’;
4).Undo for a Transaction
ALTER SYSTEM DUMP UNDO BLOCK ’segment_name’ XID xidusn xidslot xidsqn;
5).File Header
ALTER SESSION SET EVENTS ‘immediate trace name file_hdrs level n’;
1 控制檔案中的檔案頭資訊
2 level 1 + 檔案頭資訊
3 level 2 + 資料檔案頭資訊
10 level 3
6).Control file
ALTER SESSION SET EVENTS ‘immediate trace name controlf level n’;
1 檔案頭資訊
2 level 1 + 資料庫資訊 + 檢查點資訊
3 level 2 + 可重用節資訊
10 level 3
7).Redo log Header
ALTER SESSION SET EVENTS ‘immediate trace name redohdr level n’;
1 控制檔案中的redo log資訊
2 level 1 + 檔案頭資訊
3 level 2 + 日誌檔案頭資訊
10 level 3
8).Redo log
ALTER SYSTEM DUMP LOGFILE ‘FileName’;
ALTER SYSTEM DUMP LOGFILE ‘FileName’
SCN MIN MinimumSCN
SCN MAX MaximumSCN
TIME MIN MinimumTime
TIME MAX MaximumTime
LAYER Layer
OPCODE Opcode
DBA MIN FileNumber . BlockNumber
DBA MAX FileNumber . BlockNumber
RBA MIN LogFileSequenceNumber . BlockNumber
RBA MAX LogFileSequenceNumber . BlockNumber;
其中time = (((((yyyy – 1988)) * 12 + mm – 1) * 31 + dd – 1) * 24 + hh) * 60 + mi) * 60 + ss;
9).Loghist
ALTER SESSION SET EVENTS ‘immediate trace name loghist level n’;
1 dump控制檔案中最早和最遲的日誌歷史項
1 dump 2^n個日誌歷史項
三， Trace 檔案使用示例
udump下的trc檔案可以通過配置不讓產生,利用命令
alter system set sql_trace=false;
其他的不能修改，只能手動的啟動trace，手動的關閉trace.
比如：
alter session set events 'immediate trace name library_cache|controlf|systemstate|processstate|file_hdrs|REDOHDR level 10';
alter session set events 'immediate trace name off';
alter session set events '10046 trace name context forever,level 12';
alter session set events '10046 trace name context off';
alter system set events '10046 trace name context forever,level 12';
alter system set events '10046 trace name context off';
1. 使用autotrace：
set autotrace ON | ON EXPLAIN | ON STATISTICS | TRACEONLY | TRACEONLY EXPLAIN
set autotrace off
這個用法是最簡單方便的，執行sql結束之後，會自動在同個視窗顯示sql的執行計劃和統計資訊。
2. 使用set events context：
SQL> alter session set sql_trace=true;
SQL> alter session set sql_trace=false;
屬於當前session級設定
大多數的情況下，我們使用sql_trace跟蹤當前程式。通過跟蹤當前程式可以發現當前操作的後臺資料庫遞迴活動(這在研究資料庫新特性時尤其有效)，研究SQL執行，發現後臺錯誤等。
alter session set events '10046 trace name context forever,level 12';
alter session set events '10046 trace name context off';
10046事件概述：
10046事件是Oracle提供的內部事件，是對SQL_TRACE的增強.
10046事件可以設定以下四個級別:
1 - 啟用標準的SQL_TRACE功能,等價於sql_trace
4 - Level 1 加上繫結值(bind values)
8 - Level 1 + 等待事件跟蹤
12 - Level 1 + Level 4 + Level 8
類似sql_trace，10046事件可以在全域性設定，也可以在session級設定。
3. 使用set events immediate
alter session set events 'immediate trace name library_cache|controlf|systemstate|processstate|file_hdrs|REDOHDR level 10';
alter session set events 'immediate trace name off';
Level =1 ,轉儲Library cache統計資訊
Level =2 ,轉儲hash table概要
Level =4 ,轉儲Library cache物件，只包含基本資訊
Level =8 ,轉儲Library cache物件，包含詳細資訊(包括child references,pin waiters等)
Level =16,增加heap sizes資訊
Level =32,增加heap資訊
9i清理buffer cache
alter session set events 'immediate trace name flush_cache level 1';
alter session set events = 'immediate trace name flush_cache'
4. 使用set events errorstack
alter session set events 'err_num trace name errorstack level 10';
alter session set events 'err_num trace name errorstack off'
err_num=報錯程式碼，如ORA-00942 應該在err_num填入942
5. 使用dbms_support(trace別的session)：
exec dbms_support.start_trace_in_session(sid=>XX,seiral#=>XXX,wait=>true,binds=>true);
exec dbms_support.stop_trace_in_session(sid=>XX,seiral#=>XXX);
這類trace用到很少，以後再研究。
6. 使用oradebug(trace別的session)：
oradebug setospid (PID 能從ps -ef 中得出)
oradebug unlimit
oradebug event 10046 trace name context forever,level 12;
oradebug event 10046 trace name context off;
這類同樣用到很少。
7. 使用dbms_system.set_ev：
exec dbms_system.set_ev(sid,serial#,10046,trace_level,'username');
exec dbms_system.set_ev(sid,serial#,10046,0,'username');
通過DBMS_SYSTEM.SET_EV系統包來實現對sql的跟蹤
######################################################
我們可以通過以下程式碼查詢得到Oracle的診斷事件及說明，通過這些，我想就足夠了。列舉在這裡給自己參考。
1.程式碼
SET linesize 120
SET feedback off
SET SERVEROUTPUT ON
DECLARE
err_msg VARCHAR2(120);
BEGIN
dbms_output.enable (1000000);
FOR err_num IN 10000..10999
LOOP
err_msg := SQLERRM (-err_num);
IF err_msg NOT LIKE '%Message '||err_num||' not found%' THEN
dbms_output.put_line (err_msg);
END IF;
END LOOP;
END;
2.列表
ORA-10000: controlfile debug event, name 'control_file' ORA-10001: controlfile crash event1 ORA-10002: controlfile crash event2 ORA-10003: controlfile crash event3………………省略輸出了。
相關文章
- 轉：Oracle常用dump命令Oracle
- Oracle 常用dump命令 - 轉Oracle
- Oracle常用dump命令(轉)Oracle
- （轉）Oracle常用dump命令介紹Oracle
- Oracle 常用 Dump 命令收集(轉帖)Oracle
- Oracle常用dump命令Oracle
- Oracle 常用dump命令Oracle
- Oracle常用dump命令介紹[轉貼]Oracle
- ZT:Oracle常用dump命令Oracle
- Oracle常用dump命令，備查。Oracle
- Oracle DBA常用命令 [ 轉載]Oracle
- oracle dump 命令Oracle
- 【轉載】Oracle RAC 常用維護工具和命令Oracle
- Oracle常用dump命令，記錄一下備查Oracle
- 【備查】oracle dump命令Oracle
- Oracle常用dump命令備查_20091207Oracle
- 【轉】oracle裡的常用命令Oracle
- vi常用命令（轉載）
- zt_eygle大師_常用dump命令
- dump oracle events（轉）Oracle
- 轉載-oracle Shrink命令的使用Oracle
- oracle裡常用命令(轉)Oracle
- Oracle DataGuard 常用維護命令參考手冊 [轉載記錄]Oracle
- ORACLE中的DUMP轉儲方法Oracle
- Oracle中DUMP的轉儲方法Oracle
- DUMP函式(轉載eygle)函式
- HP-UX常用命令-轉載UX
- oracle 跟蹤檔案和轉儲命令及常用轉儲命令(轉)Oracle
- dump命令
- Oracle：dump轉儲檔案Oracle
- 常用Oracle命令Oracle
- Oracle data guard常用維護操作命令(轉)Oracle
- Oracle 平時常用的命令!Oracle
- 入侵oracle資料庫時常用的操作命令整理(轉)Oracle資料庫
- （轉）oracle dump block格式說明OracleBloC
- Oracle中DUMP轉儲方法 收藏Oracle
- 常用的apt dpkg命令(轉)APT
- 常用UNIX命令(轉)