如何在Exp或Imp过程中使用trace

Solution

Please do the following:

Create a sql_trace file for the export process with the bind variables (please
follow the steps):

1. Start the export shadow process by executing at the command line:

DOS> exp username/password

(Don't answer the questions raised on the screen until you reach step 4 below)

2. Identify the shadow process ospid using SQL*Plus:

SQL> SELECT p.spid,p.addr,p.pid,s.sid,SUBSTR(s.username,1,15) "USERNAME",
SUBSTR(s.program,1,15) "PROGRAM"
FROM v$process p,v$sessions
WHERE s.paddr=p.addr
AND addr=(SELECT paddr FROM v$session WHERE UPPER(program) LIKE 'EXP%');

Note: If the issue is related to the traditional import instead of export, change "LIKE 'EXP%'" in the above sql statement to LIKE 'IMP%'.

3. In SQL*Plus, use oradebug to attach to this (O)SPID, set trace file size to unlimited and
turn on Event 10046 at level 12:

SQL> oradebug setospid xxx (xxx = spid from above query)
SQL> oradebug unlimit
SQL> oradebug tracefile_name
SQL> oradebug Event 10046 trace name context forever, level 12

4. Star the table export/import by responding to the interactive mode questions in your
export session on step #1 above and wait for a while until you believe the process is hung or too slow.

5. After waiting for a while, find the trace file generated by in the 10046 trace event
as shown in step #3 above by the use of the "oradebug tracefile_name" parameter.

6. Run tkprof as follows using the resulting trace file from user_dump_dest above in
step #5 above.:

DOS> cd c:/orant/bin

DOS> tkprof <10046_trace_file>.trc <output_file>.out waits=y sort=exeela