This was first published on https://blog.dbi-services.com/get-trace-file-from-server-to-client (2017-08-20)
Republishing here for new followers. The content is related to the the versions available at the publication date
The old way to get a user dump trace file, for sql_trace (10046), Optimizer compilation trace (10053), lock trace (10704), Optimizer execution trace (10507),… is to go to the server trace directory. But if you don’t have access to the server (as in the ☁) the modern (12cR2) way is to select from V$DIAG_TRACE_FILE_CONTENTS. Before everybody is on 12.2 I’m sharing here a sqlplus script that I use for a long time to get the trace file to the client. This is a script to run with sqlplus passing as a parameter the name of the trace file you want to write to (because the original name on the server is not very meaningful). I close all cursors to get all info such as STAT lines. I get the trace file name from v$diag_info. If a directory exists I use it. If not I create one, named UDUMP, which I’ll remove at the end. Then the file is read by utl_file and spooled on client though dbms_output. At the end, I remove the trace file from the server.
Here is the script – comments welcome.
set serveroutput on feedback off verify off termout off linesize 1024 trimspool on echo off spool &1..trc declare fd utl_file.file_type; line varchar2(1024); l_tracename varchar2(512); l_directory_path all_directories.directory_path%TYPE; l_directory_name all_directories.directory_name%TYPE; l_directory_created boolean; procedure t (msg in varchar2) is begin dbms_output.put_line(msg); end; begin /* use old parameter _cached_open_cursors to close all open cursors */ for r in (select 1 from v$session_cursor_cache where count>0) loop dbms_session.set_close_cached_open_cursors(true); rollback; commit; dbms_session.set_close_cached_open_cursors(false); end loop; /* get trace directory and trace file name */ select value into l_directory_path from v$diag_info where name='Diag Trace'; select substr(replace(value,l_directory_path,''),2) into l_tracename from v$diag_info where name='Default Trace File'; /* get directory name for it, or try to create it */ l_directory_created:=false; begin select directory_name into l_directory_name from all_directories where directory_path = l_directory_path and rownum=1; exception when no_data_found then begin l_directory_name:='UDUMP'; execute immediate 'create directory '||l_directory_name||' as '''||l_directory_path||''''; l_directory_created:=true; exception when others then raise_application_error(-20000,'You must CREATE DIRECTORY '||l_directory_name||' as ''' ||l_directory_path||'''; or be granted CREATE DIRECTORY.'); end; end; /* opens the trace file */ begin fd:=utl_file.fopen(l_directory_name,l_tracename,'R'); exception when others then raise_application_error(-20001,'Impossible to open file: '||l_tracename||' in '||l_directory_name||' ( '||l_directory_path||' )'); end; /* read the trace file and prints it */ begin loop begin utl_file.get_line(fd,line); dbms_output.put_line(line); exception when no_data_found then exit; when others then dbms_output.put_line('!!! error while reading file '||l_tracename||' in '||l_directory_name||' ( '||l_directory_path||' )'); end; end loop; /* close and remove the file from the server */ utl_file.fclose(fd); utl_file.fremove(l_directory_name,l_tracename); exception when others then raise_application_error(-20002,'Impossible to remove: '||l_tracename||' in '||l_directory_name||' ( '||l_directory_path||' )'); end; begin /* drop directory if created */ if l_directory_created then execute immediate 'drop directory '||l_directory_name; end if; exception when others then raise_application_error(-20002,'Impossible to remove directory: '||l_directory_name||' ( '||l_directory_path||' )'||sqlerrm); end; end; / spool off
Hi Franck, I was checking your code as I’m in need of getting trace files located my remote DB machine. As I don’t have access to this remote server, Thought your code could help.
Anyway, after launching this piece of code, I’ve been returned this error message:
Just changed the code (adding sqlerrm and sqlcode) a bit to show the real error happening at the following sentence: utl_file.fopen(l_directory_name,l_tracename,’R’);
I ended up with the error:
Checked on Don Burleson site for a quick error explanation (http://www.dba-oracle.com/t_ora_29283_invalid_file_operation.htm) and it seems to be a problem with privilege access at SO level.
If I have not misunderstand you, the only way to do what I am expecting would be having access privileges to the DB server OR having 12cR2 in order to use that mentioned view?
Thanks in advance and thanks for this recipe.
Btw, I would delete the t procedure you are not longer using.
You should not waste time on this dba-oracle.com in my opinion Access privileges cannot be a problem because the file is written and read by same user and even same process. Are you sure the file is there? Maybe you didn’t trace anything. You can run this before to be sure there’s something written to the trace: