Hi,
I am working on Oracle database version is 11.2.0.4.0.
I have PL/SQL packages, which run from few minutes to couple of hours.
My requirement is that for each package execution, it should generate a trace file and once it is generated, it should be put in a table.
So far, I was able to generate trace files using below code:
DECLARE
l_job_id VARCHAR2(10) := '1234'; -- unique sequence generated value
BEGIN
dbms_session.set_identifier(l_job_id);
dbms_monitor.client_id_trace_enable(client_id => l_job_id);
mypackage.proc1;
dbms_monitor.client_id_trace_disable(client_id => l_job_id);
END;
/
It would generate a trace file with client id value as (1234) in the directory set in the parameter 'user_dump_dest':
SELECT value
FROM V$PARAMETER
WHERE name = 'user_dump_dest';
When I run below command from SQL Plus, it creates trace file with its name containing job id e.g. in above case 1234.trc, in my directory:
!trcsess output=/mydir/1234.trc clientid=1234 /u01/app/oracle/diag/rdbms/rs5dev/rs5dev/trace/*.trc
But I want to run above command from within my anonymous block i.e. within PL/SQL. I will create Oracle directory for it.
Also, once trace file is generated using above command, I want to put that file in an Oracle table, so that it can be later on downloaded from my Oracle APEX application.
Is above solution achievable? If so, please advice.
Thank you,
Chintan Gandhi