A very important part in the chain of tools is Method R Trace - an extension to SQL Developer - as it speeds up developers a lot: When they generate a tracefile, they can access it immediately and do not need to ask and wait for a DBA to get and provide the file. On the other side, I as a lazy dba want developers to serve themselves.
Therein Method R Trace specializes.
Beside the functionality to trace a current statement/script, in Version 2.0.0.43 of Method R Trace There was another functionality (called feature 2) to list all tracefiles 
and download them.
Unfortunately this functionality disappeared in Version 3.0.0.1 as I mentioned earlier.
As I really like that feature I implemented a poor mans getTracefile functionality in SQL Developer 4.1 with a mixture of Method R Trace Installation Script from version 2.0.0.43 and 3.0.0.1(+patch).
The outcome is not so nice looking or integrated, but it provides the general functionality.
You can list all the tracefiles in a User Defined Report:
When you click a Tracefile the child report gets populated with a bfile locator associated with the specific physical LOB file.
Here SQL Developer is very kind to provide a context menue for this bfile locator.
And you can decide to store the file locally or view it directly. There is no fancy integration into Method R Trace like tagging, filter or whatever, so you must process the file manually.
To get this functionality an extension to the MRTRACE schema is required, and of course the user defined Report must be installed in SQL Developer.
For a proper MRTRACE schema you need both installation scripts from Method R Trace 3.0.0.1 and 2.0.0.43. First install the 3.0.0.1 script (+patch). Afterwards install only the level two feature set objects from 2.0.0.43.
Additional grants are required as well:
grant select on dba_directories to mrtrace;
And as these objects, grants and synonyms needs to be created. I create them for user MRTRACE.
/* 0.3 */
/* 20150511 - bergerma - add full schema reference (MRTRACE) */
/* as MRTRACE lacks create session and has NOPASSWORD */
/* add JAVA_GRANTS as trace directories changed */
/* requires MrTrace 2.0.0.43 objects! */
/* http://berxblog.blogspot.com/2015/05/poor-mans-gettracefile.html for details */
connect SYS
grant select on dba_directories to mrtrace; -- <- make sure this user is correct
connect SYSTEM/:system_pwd
create or replace TYPE MRTRACE.TRACEFILE AS OBJECT (
trace_path varchar2(200),
trace_name varchar2(200),
filesize NUMBER(15,0),
filedate date
);
/
create or replace TYPE MRTRACE.TRACEFILES IS TABLE OF MRTRACE.TRACEFILE;
/
CREATE OR REPLACE
PACKAGE MRTRACE.MRTRACE_BX AS
/* ***************************************************************************
* berx - 20150507 - initial release - for testers only
* see http://berxblog.blogspot.com/2015/05/poor-mans-gettracefile.html
* for details
*************************************************************************** */
function get_version return varchar2;
function get_tracefiles_java return TRACEFILES pipelined;
function get_bfile(file_name IN varchar2, directory_name IN varchar2)
return bfile;
END MRTRACE_BX;
/
CREATE OR REPLACE
PACKAGE BODY MRTRACE.MRTRACE_BX AS
/* ***************************************************************************
* berx - 20150507 - initial release - for testers only
* berx - 20150508 - 0.2 - minor adaptions
* berx - 20150511 - 0.3 - add full schema reference
*
* see http://berxblog.blogspot.com/2015/05/poor-mans-gettracefile.html
* for details
* requirements
* GRANT SELECT ON DBMS_DIRECTORIES TO :MRTRACE_USER
*************************************************************************** */
FUNCTION get_version
RETURN VARCHAR2
AS
v_myversion varchar2(10) := '0.3';
v_required_mrtrace varchar2(20) := '2.0.0.43';
v_actual_mrtrace varchar2(20);
v_warning_string1 varchar2(60) := ' - MRTrace_bx requires MRTRACE level two feature set ';
v_warning_string2 varchar2(30) := ' but the version is: ';
BEGIN
v_actual_mrtrace := MRTRACE.get_version;
if v_actual_mrtrace = v_required_mrtrace then RETURN v_myversion;
else RETURN v_myversion || v_warning_string1 || v_required_mrtrace ||
v_warning_string2 || v_actual_mrtrace;
END IF;
END get_version;
FUNCTION get_tracefiles_java
RETURN TRACEFILES pipelined
AS
VRETURNARRAY SimpleStringArrayType;
v_cnt NUMBER := 0;
BEGIN
MRTRACE.GET_TRACEFILES_JAVA(vReturnArray => VRETURNARRAY);
v_cnt:=VRETURNARRAY.count;
FOR i IN VRETURNARRAY.first..VRETURNARRAY.last
LOOP
pipe row( TRACEFILE( SUBSTR(VRETURNARRAY(i),1,instr(VRETURNARRAY(i), '|', 1, 1)-1) -- PATH
, SUBSTR(VRETURNARRAY(i),
instr(VRETURNARRAY(i), '|', 1, 1) + 1,
instr(VRETURNARRAY(i), '|', 1, 2)-instr(VRETURNARRAY(i), '|', 1, 1)-1) -- FILENAME
, to_number( SUBSTR(VRETURNARRAY(i),
instr(VRETURNARRAY(i), '|', 1, 2) +1,
instr(VRETURNARRAY(i), '|', 1, 3)-instr(VRETURNARRAY(i), '|', 1, 2)-1)
) -- SIZE
, (TO_DATE('19700101000000', 'YYYYMMDDHH24MISS') +
to_number( SUBSTR(VRETURNARRAY(i),
instr(VRETURNARRAY(i), '|', 1, 3)+1,
LENGTH(VRETURNARRAY(i))-instr(VRETURNARRAY(i), '|', 1, 3))) / 86400000
)
) ); -- pipe row ...
END LOOP;
RETURN;
END get_tracefiles_java;
FUNCTION get_bfile(file_name IN varchar2, directory_name IN varchar2)
RETURN bfile
AS
v_path VARCHAR2(200);
v_dirname VARCHAR2(100) := 'METHODR_UDUMP_1';
v_bfile BFILE;
BEGIN
v_path := directory_name;
BEGIN
select min(dir.DIRECTORY_NAME) into v_dirname
from dba_directories dir
where dir.directory_path = v_path
and dir.directory_name like 'METHODR_UDUMP%';
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.put_line (
'Error in get_bfile where raised:');
DBMS_OUTPUT.put_line (
DBMS_UTILITY.format_error_backtrace);
RAISE;
END;
v_bfile := bfilename(v_dirname,file_name);
RETURN v_bfile;
END get_bfile;
END MRTRACE_BX;
/
DECLARE -- JAVA_GRANTS
v_path varchar2(200);
c_path sys_refcursor;
BEGIN
open c_path for 'select DIRECTORY_PATH from dba_directories where directory_name like ''METHODR_%''';
loop
fetch c_path into v_path;
exit when c_path%notfound;
if ( v_path is null )then
raise_application_error(-20009, 'Could not get the value of the "METHODR_" directory from dba_directories.', false);
end if;
execute immediate 'BEGIN DBMS_JAVA.GRANT_PERMISSION(''MRTRACE'',' || '''SYS:java.io.FilePermission'',''' || v_path || ''', ''read'' ); END;';
execute immediate 'BEGIN DBMS_JAVA.GRANT_PERMISSION(''MRTRACE'',' || '''SYS:java.io.FilePermission'',''' || v_path || '/-'', ''read'' ); END;';
end loop;
close c_path;
END;
grant execute on MRTRACE.MRTRACE_BX to PUBLIC;
create public synonym mrtrace_bx for mrtrace.mrtrace_bx;
At last this report is required in SQL Developer:
<?xml version="1.0" encoding="UTF-8" ?> <displays> <display id="04e5e018-014a-1000-8001-ac193119805b" type="" style="Table" enable="true"> <name><![CDATA[get tracefiles]]></name> <description><![CDATA[create a list of tracefiles in Instances UDUMP directory and makes specific files available it's based on Method-R Method R Trace functionality (version 2.0.0.43 needed) with an extension MrTrace_bx ]]></description> <tooltip><![CDATA[create a list of tracefiles in Instances UDUMP directory and makes specific files available ]]></tooltip> <drillclass><![CDATA[null]]></drillclass> <CustomValues> <TYPE>horizontal</TYPE> </CustomValues> <query> <sql><![CDATA[select TRACE_NAME , FILESIZE , FILEDATE , TRACE_PATH from table(mrtrace_bx.get_tracefiles_java)]]></sql> </query> <pdf version="VERSION_1_7" compression="CONTENT"> <docproperty title="-" author="-" subject="-" keywords="-" /> <cell toppadding="2" bottompadding="2" leftpadding="2" rightpadding="2" horizontalalign="LEFT" verticalalign="TOP" wrap="true" /> <column> <heading font="null" size="10" style="NORMAL" color="-16777216" rowshading="-1" labeling="FIRST_PAGE" /> <footing font="null" size="10" style="NORMAL" color="-16777216" rowshading="-1" labeling="NONE" /> <blob blob="EMBED" zip="false" /> </column> <table font="Agency FB" size="10" style="NORMAL" color="-16777216" userowshading="false" oddrowshading="-1" evenrowshading="-1" showborders="true" spacingbefore="12" spacingafter="12" horizontalalign="LEFT" /> <header enable="false" generatedate="false"> <data> null </data> </header> <footer enable="false" generatedate="false"> <data value="null" /> </footer> <security enable="false" useopenpassword="false" openpassword="null" encryption="EXCLUDE_METADATA"> <permission enable="false" permissionpassword="null" allowcopying="true" allowprinting="true" allowupdating="false" allowaccessdevices="true" /> </security> <pagesetup papersize="LETTER" orientation="1" measurement="in" margintop="1.0" marginbottom="1.0" marginleft="1.0" marginright="1.0" /> </pdf> <display id="null" type="" style="Table" enable="true"> <name><![CDATA[get tracefile]]></name> <description><![CDATA[access the specific tracefile ]]></description> <tooltip><![CDATA[Specific tracefile from list above ]]></tooltip> <drillclass><![CDATA[null]]></drillclass> <CustomValues> <TYPE>horizontal</TYPE> </CustomValues> <query> <sql><![CDATA[select mrtrace_bx.get_bfile(:TRACE_NAME, :TRACE_PATH) as " Tracefile " from dual]]></sql> <binds> <bind id="TRACE_NAME"> <prompt><![CDATA[TRACE_NAME]]></prompt> <tooltip><![CDATA[TRACE_NAME]]></tooltip> <value><![CDATA[NULL_VALUE]]></value> <bracket><![CDATA[null]]></bracket> </bind> <bind id="TRACE_PATH"> <prompt><![CDATA[TRACE_PATH]]></prompt> <tooltip><![CDATA[TRACE_PATH]]></tooltip> <value><![CDATA[NULL_VALUE]]></value> <bracket><![CDATA[null]]></bracket> </bind> </binds> </query> <pdf version="VERSION_1_7" compression="CONTENT"> <docproperty title="-" author="-" subject="-" keywords="-" /> <cell toppadding="2" bottompadding="2" leftpadding="2" rightpadding="2" horizontalalign="LEFT" verticalalign="TOP" wrap="true" /> <column> <heading font="null" size="10" style="NORMAL" color="-16777216" rowshading="-1" labeling="FIRST_PAGE" /> <footing font="null" size="10" style="NORMAL" color="-16777216" rowshading="-1" labeling="NONE" /> <blob blob="EMBED" zip="false" /> </column> <table font="null" size="10" style="NORMAL" color="-16777216" userowshading="false" oddrowshading="-1" evenrowshading="-1" showborders="true" spacingbefore="12" spacingafter="12" horizontalalign="LEFT" /> <header enable="false" generatedate="false"> <data> null </data> </header> <footer enable="false" generatedate="false"> <data value="null" /> </footer> <security enable="false" useopenpassword="false" openpassword="null" encryption="EXCLUDE_METADATA"> <permission enable="false" permissionpassword="null" allowcopying="true" allowprinting="true" allowupdating="false" allowaccessdevices="false" /> </security> <pagesetup papersize="LETTER" orientation="1" measurement="in" margintop="1.0" marginbottom="1.0" marginleft="1.0" marginright="1.0" /> </pdf> </display> </display> </displays>
If you see any issues with these script and report please tell me, I only had limited possibilities to test them.
Keine Kommentare:
Kommentar veröffentlichen