[20160720]檢視了解oracle跟蹤事件.txt

[20160720]檢視了解oracle跟蹤事件.txt

--作為dba偶爾會使用一些跟蹤事件，比較有名的是10046，10053.其他使用很少事情，更別說記憶。
--如果查詢特定的事件可以使用oerr 命令。例子：

$ oerr ora 10046
10046, 00000, "enable SQL statement timing"
// *Cause:
// *Action:

--透過如下命令可以獲得全表跟蹤事件：
SET linesize 120
SET feedback off
SET SERVEROUTPUT ON

DECLARE
   err_msg   VARCHAR2 (1200);
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;
/

--BTW：如果修改err_num範圍1 .. 9999,可以獲得常見錯誤表。
--但是這樣，要模糊查詢確實不是非常方便。實際上oerr讀取的就是$ORACLE_HOME/rdbms/mesg/oraus.msb，而原檔案就是
--$ORACLE_HOME/rdbms/mesg/oraus.msg 文字檔案。

--直接開啟查詢就可以瞭解許多。在簡單一點寫一個命令,grep ^10[0-9][0-9][0-9]  $ORACLE_HOME/rdbms/mesg/oraus.msg | grep -i $1
--就可以查詢相關資訊事件。

--可以寫一個別名放在.bashrc 中。例如：
alias ooerr='grep ^10[0-9][0-9][0-9]  $ORACLE_HOME/rdbms/mesg/oraus.msg | grep -i $1'

$ ooerr 10046
10046, 00000, "enable SQL statement timing"

$ ooerr undo
10015, 00000, "Undo Segment Recovery"
10016, 00000, "Undo Segment extend"
10017, 00000, "Undo Segment Wrap"
10023, 00000, "Create Save Undo Segment"
10024, 00000, "Write to Save Undo"
10026, 00000, "Apply Save Undo"
10041, 00000, "dump undo records skipped"
10044, 00000, "free list undo operations"
10048, 00000, "Undo segment shrink"
10201, 00000, "consistent read undo application"
10218, 00000, "dump uba of applied undo"
10221, 00000, "show changes done with undo"
10226, 00000, "trace CR applications of undo for data operations"
10251, 00000, "check consistency of transaction table and undo block"
10291, 00000, "die in tbsdrv to test control file undo"
10449, 00000, "enable trace of kst for undo manageability features diagnostics"
10511, 00000, "turn off SMON check to cleanup undo dictionary"

--這樣查詢就比較方便。