12.1 DataPump Doesn't See Schema Triggers With Version=11.2 (Doc ID 2186664.1)

Last updated on SEPTEMBER 26, 2016

Applies to:

Oracle Database - Standard Edition - Version 12.1.0.2 to 12.1.0.2 [Release 12.1]
Information in this document applies to any platform.

Symptoms

If the compatibility of a 12.1 database is set to '11.2.0' or if version=11.2 is used for the export job,  DataPump cannot see the schema triggers.

The example below shows the issue:

- Create a TC schema with the following objects:

connect / as sysdba
create user tc identified by tc default tablespace users;
alter user tc quota unlimited on users;
grant create session, create trigger to tc;

create table tc.tab1
(
col1 number,
col2 number
);

insert into tab1 values (1,1);

commit;

create trigger tc.logontrigger AFTER LOGON ON tc.schema
begin
null;
end;
/

create or replace trigger tc.T_TEST_LOGON_ON_DATABASE
AFTER LOGON ON DATABASE
begin
null;
end;
/

create or replace trigger trigger tc.TRG_D
AFTER DROP OF
DECLARE x INTEGER;
BEGIN
IF DICTIONARY_OBJ_OWNER = 'TC' AND
DICTIONARY_OBJ_TYPE = 'TABLE'
THEN
EXECUTE IMMEDIATE 'select count(*) into x from dual' ;
END IF;
END;
/

SQL> set lines 150
SQL> col object_name form a30
SQL> select owner,status,object_type,object_name from dba_objects where owner='TC';

OWNER STATUS
-------------------------------------------------------------------------------------------------------------------------------- -------
OBJECT_TYPE OBJECT_NAME
----------------------- ------------------------------
TC VALID
TRIGGER T_TEST_LOGON_ON_DATABASE

TC VALID
TRIGGER LOGONTRIGGER

TC VALID
TRIGGER TRG_D

- With the default version (12.1), expdp behavior is correct:

> expdp system/oracle directory=dptest dumpfile=expdp_tc.dmp logfile=expdp_tc.log schemas=tc metrics=yes

[oracle@celdbsovm009 fconstan]$ expdp system/oracle directory=dptest dumpfile=expdp_tc.dmp logfile=expdp_tc.log schemas=tc metrics=yes

Export: Release 12.1.0.2.0 - Production on Mon Sep 26 09:48:54 2016
..........
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Completed 1 TABLE objects in 17 seconds
Processing object type SCHEMA_EXPORT/EVENT/TRIGGER
Completed 3 TRIGGER objects in 4 seconds
.........
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
/u01/users/fconstan/expdp_tc.dmp
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at Mon Sep 26 09:49:39 2016 elapsed 0 00:00:42


- In case version=11.2 is used, DataPump doesn't unload all triggers owned by TC:

> expdp system/oracle directory=dptest dumpfile=expdp_tcvers.dmp logfile=expdp_tcver.log schemas=tc version=11.2 metrics=y

Export: Release 12.1.0.2.0 - Production on Mon Sep 26 09:53:31 2016
.....
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Completed 1 TABLE objects in 14 seconds
Processing object type SCHEMA_EXPORT/EVENT/TRIGGER
Completed 2 TRIGGER objects in 2 seconds
............
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
/u01/users/fconstan/expdp_tcvers.dmp
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at Mon Sep 26 09:53:56 2016 elapsed 0 00:00:23

 

Changes

The compatibility of a 12.1 database is set to '11.2.0'.

  - OR -

12.1 expdp job is started with version=11.2.

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