DataPump 10.2 is truncating lengthy views and triggers (Doc ID 976627.1)

Last updated on DECEMBER 17, 2009

Applies to:

Oracle Server - Enterprise Edition - Version: 10.2.0.4 to 10.2.0.4 - Release: 10.2 to 10.2
HP OpenVMS Alpha

Symptoms


===================
-- Problem Statement:

When using DataPump to export from a 10.2 database and import into a 10.2 database, the DDL for lengthy views and triggers is truncated. The source database has a block size of 2048 (db_block_size = 2048).  The target database has a block size of 8192.

Example:

expdp scott/tiger schemas=scott dumpfile=scott.dmp
impdp scott/tiger sqlfile=sql.out dumpfile=scott.dmp


The sql.out shows:

-- new object type path is: SCHEMA_EXPORT/VIEW/VIEW
CREATE FORCE VIEW "SCOTT"."TZVSESS" ("TZVSESS_SESSION_NUMBER",
"TZVSESS_USER", "TZVSESS_CSHR_END_DATE", "TZVSESS_FEED_DOC_CODE", "TZVSESS_ACTIVITY_DATE", "TZVSESS_FEED_DATE", "TZVSESS_TRAN_NUMBER", "TZVSESS_RECEIPT_NUMBER", "TZVSESS_DETAIL_CODE", "TZVSESS_DESC", "TZVSESS_DCAT_CODE", "TZVSESS_AMOUNT", "TZVSESS_NAME", "TZVSESS_ID", "TZVSESS_SRCE") AS ;

This view is actually 126 lines long, but only 8 lines are extracted with DataPump.
No errors are reported back to your screen.
No errors are logged in the DataPump logs.

Extracting the views and triggers on the source database with the DBMS_METADATA package also truncates the DDL.

SQL> set long 100000
SQL> select dbms_metadata.get_ddl('VIEW','TZVSESS','SCOTT') from dual;


DBMS_METADATA.GET_DDL('VIEW','TZVSESS','SCOTT')
------------------------------------------------------------------------------
CREATE OR REPLACE FORCE VIEW "SCOTT"."TZVSESS" ("TZVSESS_SESSION_NUMBER",
"TZVSESS_USER", "TZVSESS_CSHR_END_DATE","TZVSESS_FEED_DOC_CODE",
"TZVSESS_ACTIVITY_DATE", "TZVSESS_FEED_DATE", "TZVSESS_TRAN_NUMBER", "TZVSESS_RECEIPT_NUMBER", "TZ VSESS_DETAIL_CODE", "TZVSESS_DESC", "TZVSESS_DCAT_CODE", "TZVSESS_AMOUNT", "TZVS ESS_NAME", "TZVSESS_ID", "TZVSESS_SRCE") AS


Using the classic Export and Import on the same source and target databases works correctly, with no truncation of lengthy views or triggers.

The same triggers and views are extracted correctly with DataPump if the source database has a larger block size such as 8192.

The same triggers and views are extracted correctly with the DBMS_METADATA package if the source database has a larger block size such as 8192.

Changes

None.

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