[20140217]在toad使用跟蹤檔案.txt

[20140217]在toad使用跟蹤檔案.txt

我使用toad版本是12.0.0.61,今天使用生成跟蹤檔案，發現在介面上可以訪問跟蹤檔案的內容做一個測試看看。

SCOTT@test> @ver

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

在toad 的session browser介面上，選擇要跟蹤的會話，如圖：

現在的版本支援3種方式，dbms_monitor,dbms_system,dbms_support.執行如下命令：

SCOTT@test> variable b number;
SCOTT@test> exec :b := 30

PL/SQL procedure successfully completed.

SCOTT@test> select * from dept where deptno=:b ;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        30 SALES          CHICAGO

使用SQL Tracker的內容如下：
--------------------------------------------------------------------------------
Timestamp: 2014/2/17 15:05:33
begin sys.dbms_monitor.session_trace_disable(110,189); end;
Elapsed time: 0.003
--------------------------------------------------------------------------------
Timestamp: 2014/2/17 15:05:59
select instance_name from v$instance
Elapsed time: 0.001
--------------------------------------------------------------------------------
Timestamp: 2014/2/17 15:05:59
select * from v$version where banner like '%Windows%'
Elapsed time: 0.005
--------------------------------------------------------------------------------
Timestamp: 2014/2/17 15:05:59
select a.tracefile
from v$process a, v$session b
where a.addr = b.paddr
and b.sid = 110
and b.serial# = 189
Elapsed time: 0.011
--------------------------------------------------------------------------------
Timestamp: 2014/2/17 15:05:59
select directory_name
from   sys.DBA_DIRECTORIES
where  lower(directory_path) in ('/u01/app/oracle11g/diag/rdbms/test/test/trace','/u01/app/oracle11g/diag/rdbms/test/test/trace/')
Elapsed time: 0.001
--------------------------------------------------------------------------------
Timestamp: 2014/2/17 15:05:59
DECLARE
  H UTL_FILE.FILE_TYPE;
BEGIN
  H := UTL_FILE.FOPEN(:dirname, :filename,'r', 4000);
  :id := H.id;
  :dt := H.datatype;
end;
dirname=['TRACE']
filename=['test_ora_24372_127_0_0_1.trc']
id=[754538248]
dt=[1]
Elapsed time: 0.002
--------------------------------------------------------------------------------
Timestamp: 2014/2/17 15:05:59
DECLARE
  H utl_file.file_type;
  L varchar2(4000);
  Buffer varchar2(32767);
  BuffLen Number;
  err Boolean;
  LogData clob;
  LineCount Number;
BEGIN
  err := false;
  H.id := 754538248;
  H.datatype := 1;
  dbms_lob.CreateTemporary(lob_loc=>LogData, cache=>true, dur=>dbms_lob.call);
  dbms_lob.Open (LogData, dbms_lob.lob_readwrite);
  LineCount := 0;
  while (not err) and (Linecount < 1000) loop
    begin
      utl_file.Get_Line (H, L);
      LineCount := LineCount + 1;
      Buffer := Buffer || L || chr(13) || chr(10);
      BuffLen := Length(Buffer);
      if BuffLen > 28000 then
        dbms_lob.WriteAppend(LogData,BuffLen,Buffer);
        Buffer := null;
      end if;
    exception
      when others then
      err := true;
    end;
  end loop;
  BuffLen := Length(Buffer);
  if BuffLen > 0 then
    dbms_lob.WriteAppend(LogData,BuffLen,Buffer);
    Buffer := null;
  end if;
  if err then
    :done := 1;
  else
    :done := 0;
  end if;
  :outClob := LogData;
  dbms_lob.freetemporary(LogData);
end;
done=[1]
outClob=[ ]
Elapsed time: 0.026
-------------------------------------------------------------------------------
Timestamp: 2014/2/17 15:05:59
DECLARE
  H UTL_FILE.FILE_TYPE;
BEGIN
  H.id := 754538248;
  H.datatype := 1;
  UTL_FILE.FCLOSE (H);
end;
Elapsed time: 0.003

可以發現如下介面，發現一個錯誤，取出的繫結變數值不對。如圖：

另外可以從database=>diagnose=>trace file browser可以訪問。第一次要選擇create objects in toad schema或者create objects in my schema。
--說明：我在10g下這個過程失敗。建立這個java的東西失敗，不知道為什麼？那位知道請告訴我，謝謝！

CREATE OR REPLACE AND RESOLVE JAVA SOURCE NAMED TOAD."ToadDirList" AS
  import java.io.*;
  import java.sql.*;
  import java.util.Date;
  import java.text.SimpleDateFormat;
  import oracle.jdbc.driver.OracleSQLException;

  public class ToadDirList
  {
    public static void getList(String directory) throws IOException, SQLException
    {
      Boolean exists = (new File(directory)).exists();
      if (exists)
      {
          File path = new File(directory);
          String[] list = path.list();
          String element;

          for(int i = 0; i < list.length; i++)
          {
            element = list[i];
            String fpath = directory + "/" + list[i];
            File f = new File(fpath);
            long len;
            Date  date;
            String ftype;
            String sqldate;
            SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd kk:mm:ss");
            if (f.isFile())
            {
              len   = f.length();
              date  = new Date(f.lastModified());
              sqldate = df.format(date) ;
              ftype = "F";
            }
            else
            {
              len   = 0;
              sqldate  = null;
              ftype = "D";
            }
            try
            {
              #sql { INSERT INTO Toad_dir_listing (file_name, file_size, type, modified)
                     VALUES (:element, :len, :ftype, to_date(:sqldate,'YYYY-MM-DD HH24:MI:SS')) };
            }
            catch (OracleSQLException e)
            { /* Sometimes the date doesn't get translated propertly.  Proceeed without it. */
              #sql { INSERT INTO Toad_dir_listing (file_name, file_size, type, modified)
                     VALUES (:element, :len, :ftype, null) };
            } /* try..catch */
          } /* for loop */
      } /* directory exists */
      else
      {
      throw new IOException("Folder " + directory + " does not exist on server.");
      }
    } /* getlist */
};

SQL> show error
Errors for JAVA SOURCE "ToadDirList":

LINE/COL ERROR
-------- -----------------------------------------------------------------
0/0      ToadDirList:11: incompatible types
0/0      found   : boolean
0/0      required: java.lang.Boolean
0/0      Boolean exists = (new File(directory)).exists();
0/0      ^
0/0      2 errors
0/0      found   : java.lang.Boolean
0/0      required: boolean
0/0      if (exists)
0/0      ^
0/0      ToadDirList:12: incompatible types

有這個檢視語句，以及等待事件方便許多。