Situatie

The purpose of this document it to explain how Data Pump can be traced using a database logon trigger.

1. Create a database after logon trigger to enable Event 10046 for Data Pump  DM and DW processes :

sqlplus 

connect / as sysdba

 

CREATE OR REPLACE TRIGGER sys.DATA_PUMP_TRIGGER
AFTER LOGON ON DATABASE
DECLARE
  v_program v$session.program%TYPE;
  v_dyn_sql VARCHAR2(100)
BEGIN
  SELECT    substr (program, -5, 2)
  INTO   v_program
  FROM   v$session
  WHERE  sid = (SELECT DISTINCT sid FROM v$mystat);
  IF v_program = ‘DW’ or v_program= ‘DM’ THEN
    EXECUTE IMMEDIATE ‘alter session set tracefile_identifier = ‘||’datapump’;
    EXECUTE IMMEDIATE ‘alter session set statistics_level=ALL’;
    EXECUTE IMMEDIATE ‘alter session set max_dump_file_size=UNLIMITED’;
    EXECUTE IMMEDIATE ‘alter session set events ”10046 trace name context forever, level 12”’;
  END IF;
END;
/

2. Run Data Pump Export/Import with the following parameters added to the command line:

Solutie

Tip solutie

Permanent