My Oracle Support Banner

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

Last updated on FEBRUARY 25, 2019

Applies to:

Oracle Database - Standard Edition - Version 12.1.0.2 to 12.1.0.2 [Release 12.1]
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Backup Service - Version N/A and later
Oracle Database Cloud Service - Version N/A and later
Information in this document applies to any platform.


NOTE: In the images and/or the document content below, the user information and data used represents fictitious data. Any similarity to actual persons, living or dead, is purely coincidental and not intended in any manner.

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 <TABLESPACE_NAME>;
alter user tc quota unlimited on <TABLESPACE_NAME>;
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 12.1 database is set to '11.2.0'.

  - OR -

12.1 expdp job is started with version=11.2.

Cause

To view full details, sign in with your My Oracle Support account.

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


In this Document
Symptoms
Changes
Cause
Solution
References


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.