在dotConnect for Oracle上如何使用OracleTrace組件呢？稍后將會(huì)用實(shí)例來(lái)展示。先來(lái)看一下OracleTrace組件，這個(gè)組件主要是用于跟蹤在服務(wù)器端執(zhí)行的SQL和PL / SQL命令，SQL跟蹤位于服務(wù)器上的日志文件中，而L/SQL跟蹤位于數(shù)據(jù)庫(kù)表中。

    調(diào)用SqlTraceStart()方法或是SQL跟蹤開(kāi)始時(shí)設(shè)置可用屬性為true，將會(huì)開(kāi)啟SQL 和PL/SQL跟蹤，SQL跟蹤自動(dòng)選擇跟蹤日志文件寫(xiě)入跟蹤，通過(guò)的GetTraceFileName （）方法可以獲取該文件的完整路徑，使用MaxTraceFileSize屬性來(lái)限制日志文件的大小，SqlTraceMode屬性將會(huì)指定哪種類型的數(shù)據(jù)應(yīng)在跟蹤的時(shí)候搜集。

     PL / SQL跟蹤需要在服務(wù)器上安裝的dbms_trace包，PLSQL_TRACE_RUNS 和PLSQL_TRACE_EVENTS表創(chuàng)建來(lái)存儲(chǔ)日志，這個(gè)可以通過(guò)在Oracle數(shù)據(jù)庫(kù)/客戶端安裝包中執(zhí)行tracetab.sql實(shí)現(xiàn)。第一個(gè)表中主要用于跟蹤運(yùn)行時(shí)的信息，第二個(gè)表則要包括執(zhí)行跟蹤后的事件描述。

    調(diào)用SqlTraceStart()方法或是SQL跟蹤開(kāi)始時(shí)設(shè)置可用屬性為true，開(kāi)啟SQL 和PL/SQL跟蹤，也將會(huì)開(kāi)啟SQL跟蹤。你需要權(quán)限來(lái)執(zhí)行一個(gè)dbms_trace包用于開(kāi)始PL / SQL跟蹤。通過(guò)PlSqlTracePause 和PlSqlTraceResume方法 ，PL/SQL跟蹤將會(huì)被暫停和恢復(fù)，PlSqlTraceLimit方法允許限制在數(shù)據(jù)庫(kù)中用于數(shù)據(jù)跟蹤的存儲(chǔ)容量，PlSqlTraceMode屬性將會(huì)指定跟蹤類型。

    OracleTrace組件使用示例：

[C#]

// Initialize and open the connection to your Oracle server
// Connect as "system" to have the privileges needed to operate with the DBMS_TRACE package
OracleConnection connection = new OracleConnection();
connection.Server = "ORA";
connection.UserId = "system";
connection.Password = "password";
connection.ConnectMode = OracleConnectMode.SysDba;
connection.Open();

// Create the OracleTrace object representing the DBMS_TRACE package functionality.
OracleTrace trace = new OracleTrace(connection);

// Check the full path of the file where the trace log is stored
string path = trace.GetTraceFileName();
Console.WriteLine("The trace file: " + path);

// Create and execute a simple select query
// Observe that nothing was written to the trace file            
OracleCommand command = new OracleCommand("select * from scott.dept", connection);
command.ExecuteReader();            

// Start tracing the SQL commands execution.
trace.SqlTraceStart();

// Again execute the same command. 
// The trace log contains now detailed information on the query performed.            
command.ExecuteReader();

// Set the trace to observe the PL/SQL commands execution.
// The PL/SQL trace log is stored in the special database tables.
// By default, the PL/SQL trace mode is not set, 
// thus we need to change it, e.g., to tracing all PL/SQL calls.
trace.PlSqlTraceMode = PlSqlTraceMode.AllCalls;
trace.PlSqlTraceStart();

// Now tracing for both SQL and PL/SQL execution is enabled. 
// Note that this may be done by setting the Enabled property to true as well.

// Create an PL/SQL command.            
string plsqlCode = @"
DECLARE
i INTEGER;
BEGIN
i:= 1;
FOR rec IN (SELECT DeptNo FROM Scott.Dept
   WHERE RowNum <= 4 ORDER BY DeptNo)
LOOP
   UPDATE Scott.Dept
   SET DName = :NameArr(i)
   WHERE DeptNo = Rec.DeptNo;
   i:= i + 1;
END LOOP;
END;
";
string[] nameArray = { "Accounting", "Research", "Sales", "Operations" };
command = new OracleCommand(plsqlCode,connection);
command.Parameters.Add("NameArr", OracleDbType.VarChar);
command.Parameters["NameArr"].ArrayLength = 4;

[Visual Basic]

Dim connection As New OracleConnection
connection.Server = "ORA"
connection.UserId = "system"
connection.Password = "password"
connection.ConnectMode = 2
connection.Open()
Dim trace As New OracleTrace(connection)
Console.WriteLine(("The trace file: " & trace.GetTraceFileName))
Dim command As New OracleCommand("select * from scott.dept", connection)
command.ExecuteReader()
trace.SqlTraceStart()
command.ExecuteReader()
trace.PlSqlTraceMode = 1
trace.PlSqlTraceStart()
Dim plsqlCode As String = VbCrlf & 
"DECLARE" & VbCrlf & 
"i INTEGER;" & VbCrlf & 
"BEGIN" & VbCrlf & 
"i:= 1;" & VbCrlf &
"FOR rec IN (SELECT DeptNo FROM Scott.Dept" & VbCrlf & 
"   WHERE RowNum <= 4 ORDER BY DeptNo)" & VbCrlf & 
"LOOP" & VbCrlf & "   UPDATE Scott.Dept" & VbCrlf &
"   SET DName = :NameArr(i)" & VbCrlf & 
"   WHERE DeptNo = Rec.DeptNo;" & VbCrlf & 
"   i:= i + 1;" & VbCrlf & 
"END LOOP;" & VbCrlf & 
"END;" & VbCrlf

Dim nameArray As String() = New String() {"Accounting", "Research", "Sales", "Operations"}
command = New OracleCommand(plsqlCode, connection)
command.Parameters.Add("NameArr", &H1C)
command.Parameters("NameArr").ArrayLength = 4