Logon Triggers are not Fired During Expdp and Impdp Sessions (Doc ID 739508.1)

Last updated on NOVEMBER 28, 2016

Applies to:

Oracle Server - Enterprise Edition - Version: 10.1.0.2 to 11.1.0.7
This problem can occur on any platform.

Symptoms

You created:

- a non-global application context
- a user logon schema trigger which sets a value for that context
- a table
- a row trigger which attempts to read that context value out

Then you export/import the table using DataPump (expdp/impdp) and the values set by context in logon trigger are missing.

Steps to reproduce:

-- create environment
connect / as sysdba
drop user usr cascade;
drop user app cascade;
purge dba_recyclebin;

create user app identified by passwd default tablespace users temporary tablespace temp;
grant connect, resource to app;

connect app/passwd
create or replace package mypck is
  procedure setvalue (p_cvalue varchar2);
end mypck;
/

create or replace package body mypck is
  procedure setctxval (p_centry varchar2, p_cvalue varchar2) is
  begin
    dbms_session.set_context ('MYCTX', 'MYENTRY', p_cvalue);
  end setctxval;

  procedure setvalue (p_cvalue varchar2) is
  begin
    setctxval ('MYENTRY', p_cvalue);
  end setvalue;
end mypck;
/

create table mytab
(
   mycol1 varchar2(10) default sys_context ('MYCTX', 'MYENTRY') not null,
   mycol2 varchar2(10)
);

create or replace trigger mytab_trg
before insert on mytab
for each row
begin
  if :new.mycol1 = sys_context ('MYCTX', 'MYENTRY') then
    null;
  else
    raise_application_error (-20001, 'APP.MYTAB_TRG: Values don''t match (column: '|| nvl (:new.mycol1, '<NULL>' )||', context: '||nvl (sys_context ('MYCTX', 'MYENTRY'), '<NULL>')||').');
  end if;
end mytab_trg;
/

connect / as sysdba
drop context myctx;
purge dba_recyclebin;

create context myctx using app.mypck;

create or replace directory mydir as 'd:\temp';

create user usr identified by passwd default tablespace users temporary tablespace temp;

grant create session to usr;
grant create table to usr;
grant unlimited tablespace to usr;
grant exp_full_database to usr;
grant imp_full_database to usr;
grant select any table to usr;
grant insert any table to usr;
grant execute on app.mypck to usr;

create or replace trigger usr.usr_trg_al after logon on usr.schema
begin
  app.mypck.setvalue ('XYZ');
end usr_trg_al;
/

connect usr/passwd
insert into app.mytab (mycol2) values ('ABC');
commit;

Select the inserted row to demonstrate the logon trigger sets the context:

SQL> select * from app.mytab;

This returns:

MYCOL1     MYCOL2
---------- ----------
XYZ        ABC

Then start DataPump export/import:

#> del d:\temp\mytab*.*
#> expdp usr/passwd directory=mydir tables=app.mytab dumpfile=mytab.dmp logfile=mytab.exp.log content=data_only

This ends with:

Export: Release 10.2.0.1.0 - Production on Tuesday, 28 October, 2008 11:38:48
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options
Starting "USR"."SYS_EXPORT_TABLE_01": usr/******** directory=mydir tables=app.mytab dumpfile=mytab.dmp logfile=mytab.exp.log content=data_only
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
. . exported "APP"."MYTAB"                  5.234 KB          1 rows
Master table "USR"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for USR.SYS_EXPORT_TABLE_01 is:
D:\TEMP\MYTAB.DMP
Job "USR"."SYS_EXPORT_TABLE_01" successfully completed at 11:39:02

Re-import the table with:

#> impdp usr/passwd directory=mydir tables=app.mytab dumpfile=mytab.dmp logfile=mytab.imp.log table_exists_action=truncate

and this ends with:

Import: Release 10.2.0.1.0 - Production on Tuesday, 28 October, 2008 11:39:03
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options
Master table "USR"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "USR"."SYS_IMPORT_TABLE_01": usr/******** directory=mydir tables=app.mytab dumpfile=mytab.dmp logfile=mytab.imp.log table_exists_action=truncate
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
ORA-31693: Table data object "APP"."MYTAB" failed to load/unload and is being skipped due to error:
ORA-20001: APP.MYTAB_TRG: Values don't match (column: XYZ, context: <NULL>).
ORA-06512: at "APP.MYTAB_TRG", line 5
ORA-04088: error during execution of trigger 'APP.MYTAB_TRG'
Job "USR"."SYS_IMPORT_TABLE_01" completed with 1 error(s) at 11:39:07

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms