[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

有这个查看语句，以及等待事件方便许多。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/267265/viewspace-1082628/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/267265/viewspace-1082628/