How I Copied Triggers from one Database to Another

(This happened on Oracle 11.2.) In an earlier example, I started to look at doing a full expdp and impdp. I'm not sure why, but the Data Pump import produced a number of errors. There were well over 200 like this: 

ORA-39112: Dependent object type TRIGGER:"SRCE"."ADDRESS_BIU" skipped, base object type TABLE:"SRCE"."ADDRESS" creation failed 

This message suggested that the SRCE.ADDRESS table had not been created in the output database but, in fact, it had: 

SQL> desc srce.address

Name                       Null?    Type

-------------------------- -------- ------------------

UPDATE_COUNT               NOT NULL NUMBER

ADDRESS_ID                 NOT NULL NUMBER

SUB_PREMISES                        VARCHAR2(30)

PREMISES_NAME                       VARCHAR2(30)

PO_BOX                              VARCHAR2(30)

PREMISES_NUMBER                     VARCHAR2(10)

DEP_THOROUGHFARE                    VARCHAR2(30)

THOROUGHFARE                        VARCHAR2(30)

DOUB_DEP_LOCALITY                   VARCHAR2(30)

DEP_LOCALITY                        VARCHAR2(30)

POST_TOWN                           VARCHAR2(30)

COUNTY                              VARCHAR2(30)

POSTCODE                            VARCHAR2(8)

USES                                NUMBER

LAST_UPD_BY                         VARCHAR2(8)

LAST_UPD_TIME                       DATE

COUNTRY                             VARCHAR2(30)

BUILDING_GROUP_NAME                 VARCHAR2(30)

TCN                                 NUMBER 

However, none of the triggers had been created so my output database was over 200 triggers short, which, as you might expect, stopped it working properly.

 

I tried to extract DDL from the input database to create the triggers as follows but this failed too:

SQL> EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM -

> (DBMS_METADATA.SESSION_TRANSFORM,'PRETTY',true);

 

PL/SQL procedure successfully completed.

 

SQL> EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM -

> (DBMS_METADATA.SESSION_TRANSFORM,'SQLTERMINATOR',true);

 

PL/SQL procedure successfully completed.

 

SQL> select dbms_metadata.get_ddl

  2  ('TRIGGER','ADDRESS_BIU','SRCE') from dual

  3  /

ERROR:

ORA-03113: end-of-file on communication channel

Process ID: 13899

Session ID: 95 Serial number: 10069

 

no rows selected

 

SQL> conn / as sysdba

Connected.

SQL> select open_mode from v$database

  2  /

 

OPEN_MODE

--------------------

READ WRITE

 

1 row selected.

 

SQL>

 

I wrote the following script and ran it against the input database:

 

col trigger_body newline

col slash newline

set feedback off

set pages 0

set lines 200

set long 100000

set longchunksize 200

set trimspool on

spool create_triggers.sql

select 'create or replace trigger '||

description,

trigger_body,

'/' slash

from dba_triggers

where owner = 'SRCE'

/

spool off

 

This is part of the SPOOL file:

 

create or replace trigger srce.ptr_product_pk_bi

  BEFORE INSERT ON ptr_product

  FOR EACH ROW

DECLARE

  -- local variables here

BEGIN

  IF :new.product_id IS NULL THEN

    SELECT ptr_product_id_seq.nextval

    INTO   :new.product_id

    FROM   DUAL;

  END IF;

END ptr_product;

/

 

… and when I ran it against the output database, it worked like this:

 

SQL> l

  1  create or replace trigger srce.ptr_product_pk_bi

  2    BEFORE INSERT ON ptr_product

  3    FOR EACH ROW

  4  DECLARE

  5    -- local variables here

  6  BEGIN

  7    IF :new.product_id IS NULL THEN

  8      SELECT ptr_product_id_seq.nextval

  9      INTO   :new.product_id

 10      FROM   DUAL;

 11    END IF;

 12* END ptr_product;

SQL> /

 

Trigger created.

 

SQL>

The SQL in the SPOOL file created the other 200+ missing triggers in the same way. When I get a moment, I will try to understand what caused the problems I had with impdp and dbms_metadata.get_ddl.