Some of  the complex issues that we face in our day-day operations can be resolved by tracing with or without Oracle support.  It depends on how much you want to grind yourself as understanding some of the trace files are not for faint hearts.  I always end up spending time googling for appropriate  syntax or trace number. Sometimes you  are lucky enough to get  to the intended document but sometimes you end up at wrong place.   So here I am compiling a one stop shop for all tracing needs as series.  In  Part-I , I will be discussing SPM tracing and Datapump tracing.  SPM and datapump have nothing in common , just  picked them randomly

—
SPM Tracing: There may be many  reasons why one would trace SPM.  Few of the reasons I can think of is to diagnose SPM’s not being created or to see more information of plan evolution.  SPM Tracing can be enabled either of following ways

•     exec dbms_spm.configure(‘spm_tracing’,1);

I could not find documentation about spm_tracing option  in 11gR2. Two documented parameters supported by dbms_spm.configure procedure are  space_budget_percent and plan_retention_weeks.  Only references for parameter spm_tracing was found in metalink with values 0 (disable tracing) and 1(enable tracing). The actual command however takes any values you provide.  You can query sys.smb$config to verify tracing is enabled or disabled

SQL> exec dbms_spm.configure('spm_tracing',1); 

PL/SQL procedure successfully completed.

SQL> 
SQL>  select parameter_name, parameter_value from sys.smb$config;

PARAMETER_NAME                 PARAMETER_VALUE
------------------------------ ---------------
SPACE_BUDGET_PERCENT                        25
PLAN_RETENTION_WEEKS                       523
SPM_TRACING                                  1

•     alter session set events ‘trace [sql_planmanagement.*]’;

Noticed the 2nd command; If does not look familiar then you are not  alone. This is the new syntax introduced with 11g also referred to as universal tracing.  Unfortunately I was not able to get this working after trying different options. I will follow-up with Oracle on this command.
SQL>
SQL> alter session set events ‘trace [sql_planmanagement.*]’;
ERROR:
ORA-49100: Failed to process event statement [trace [sql_planmanagement.*]]
ORA-48904: String [SQL_PLANMANAGEMENT] is not a valid component name

SQL> alter session set events ‘trace {sql_plan_management.*}’;
ERROR:
ORA-49100: Failed to process event statement [trace {sql_plan_management.*}]
ORA-49109: Library Name [SQL_PLAN_MANAGEMENT] not found

SQL>

—

DataPump Tracing:  Datapump can be traced in multiple ways either at database level or at command level or with 10046 trace.  With datapump , tracing is as complex as analyzing the trace files.  Never understood the reasoning behind  exposing hexadecimals to users unless this was intended for Oracle support. Datapump tracing  is enabled with  TRACE parameter using 7 digit hexadecimal. The first three digits enable the trace  while last four digits are usually “0300”
At the database level, datapump tracing can be enabled with initialization parameter EVENT 39089.  Again you have to find the correct  hexadecimal value and  bounce the database.

—

Example:(Init.ora setting)

EVENT=”39089 trace name context forever,level 0x300″ .

—

Example (alter system ,10046 trace)
alter system set events ‘sql_trace {process : pname = dw | pname = dm} level=12’; Just replace 12 with off to disable tracing. Be aware of this command , it will take any argument for process name.

—
SQL> alter system set events ‘sql_trace {process : pname = dw } level=12’;

System altered.

—

SQL> alter system set events ‘sql_trace {process : pname =djsljl} level=12′;  ==> Incorrect value

System altered.

SQL>
—-

—

Example (Command level)
expdp system/manager DIRECTORY=mydata_dir DUMPFILE=full_db.dmp LOGFILE=full_db.log full=y TRACE=480300

—

—

Here is information about  the hexadecimals supported trace levels supported.  Oracle also supports combination of these values

================================================================================
Hex     | Purpose
================================================================================
10300   |  SHDW: To trace the Shadow process (API) (expdp/impdp)
20300   |  KUPV: To trace Fixed table
40300   |  'div' To trace Process services
80300   |  KUPM: To trace Master Control Process (MCP,DM)
100300  |  KUPF: To trace File Manager
200300  |  KUPC: To trace Queue services
400300  |  KUPW: To trace Worker process(DW)
800300  |  KUPD: To trace Data Package
1000300 |  META: To trace Metadata Package
1FF0300 |  'all' To trace all components(full tracing)
================================================================================

Tagged: 39089, datapump tracing; spm tracing, exec dbms_spm.configure, sql_planmanagement.*