One of really cool feature is ability to trace (and record) Oracle trace file from any client, releasing the whole task from DBAs to developers, where this, at least on DEV instance, task belong.
After initial calling Database|Diagnose|Trace File Browser menu option, you get dialog, which should be run as privileged Oracle user, because additional TOAD user's objects has to be created. This is possible only if you have previously created Toad server side objects, which in this release is somehow hidden under Database|Administer|Toad security...
Directory option is not enabled, because some TOAD schema objects were not previously created. So, when you choose "Create objects in TOAD schema" button, next dialog is shown:
The problem
Choosing "Yes" start predefined TOAD objects creating script. This script on 10g version finish with ORA-24344 and only partial set of objects were created. Problem is that java code (which is interface for simulation of file browse from Oracle):Boolean exists = (new File(directory)).exists();which should be:
boolean exists = (new File(directory)).exists();Small "b" should be defined.
Interesting, as mentioned, on Oracle 11gR2 (that I tested) this code pass. Even thought you fix this Java source, still some other objects were missing and Trace browser was still not possible to run.
The Solution
Because there is no way to guess what is still missing in TOAD schema, I have tried to trace session on other (11gR2 instance), where this script was running without problems. Here is the whole Toad trace file.Editing this gave me a proper solution. Here is the "cleaned" Toad trace file code:
connect toad/<pwd>@db; drop table TOAD.Toad_dir_listing ; create global temporary table TOAD.Toad_dir_listing ( file_name varchar2(255), file_size number, type varchar2(1), modified date ) on commit delete rows ; 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 */ }; create or replace procedure TOAD.Populate_Toad_Dir_Listing( p_directory in varchar2 ) as language java name 'ToadDirList.getList( java.lang.String )' ; grant select on TOAD.TOAD_DIR_LISTING to PUBLIC ; grant execute on TOAD.POPULATE_TOAD_DIR_LISTING to PUBLIC ; create or replace directory TOAD_BDUMP_DIR as 'C:\ORACLE\diag\rdbms\xe\xe\trace\' ;with replacing 'C:\ORACLE\diag\rdbms\xe\xe\trace\' value to your actual database trace directory. When I create objects with shown script and return to "Trace file browser" dialog again I was still unable to proceed but problematic part was far away from me and I was able to continue:
The end
Every new product carry new features and unfortunately new bugs. Some of them are solvable (with workaround like this) but some of them need to be fix by Quest.A small advice for Quest for Oracle 11.6 version. If you do not really need x64 bit version, rather install x86 one. Seems that many bugs are raised because new Delphi 64 bit compiler and partial incompatibility with some components that are part of Toad.
Hope this helps someone
Cheers!
Toad 12.1 adds really cool automatic deadlock detection and focus to trace file browser - read this blog: http://www.toadworld.com/products/toad-for-oracle/b/weblog/archive/2013/08/14/toad-12-1-offers-automatic-trace-file-deadlock-detection.aspx
ReplyDelete