Tuning RMAN - RMAN Recipes for Oracle Database 12c - page 512

begin
if user='RCAT' then
dbms_monitor.session_trace_enable(null, null, true, true);
SELECT 'RMAN SQL TRACE FILE' INTO trace_string FROM dual;
end if;
end;
/

The prior bit of code assumes the recovery catalog user is named RCAT. You'll have to modify that to match the
recovery catalog user name in your environment. Ensure that your recovery catalog user has alter session privileges:

SQL> grant alter session to rcat;

Now when a connection to the recovery catalog is made via the RCAT user, a trace file is created that contains the
SQL statements and statistics for that session. See the subsection “Analyzing Trace File Information” in this recipe for
details on processing the trace file.
Ensure you drop the trigger after you're done gathering SQL tracing information:

SQL> drop trigger trace_rcat;

Analyzing Trace File Information
To view the trace file, first determine its location:

SQL> select value from v$diag_info where name = 'Diag Trace';

VALUE
--------------------------------------------
/ora01/app/oracle/diag/rdbms/rcat/rcat/trace

Now navigate to the trace file directory:

$ cd /ora01/app/oracle/diag/rdbms/rcat/rcat/trace

■ if you're using a version prior to Oracle Database 11g, you can determine the trace file directory by inspecting
the user_dump_dest initialization parameter.

Note

Next search for the string RMAN SQL TRACE FILE in the trace files (this string was embedded into the trace file
via SQL from the prior two subsection examples):

grep "RMAN SQL TRACE FILE" *.trc
rcat_ora_23006.trc ...

In this example, the name of the trace file is rcat_ora_23006.trc. Now run the TKPROF (trace kernel profiler)
utility to transform information in the trace file into a human readable form:

$ tkprof rcat_ora_23006.trc myout.txt