Skip to Main Content

Database Software

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Please ask technical questions in the appropriate category. Thank you!

DBMS_DATAPUMP Import duplicates the table data when PDB is in Upgrade mode

User_T-OracleApr 11 2024 — edited Apr 11 2024

Hello

Recently I'm seeing some weird behaviour regarding DBMS_DATAPUMP Import when my PDB is in Upgrade mode.

Here is the table structure:

create table HR.t03
(
   id  number,
   col1  varchar2(30)
   constraint pk_t03 primary key
);
insert into HR.t03 values (1,'INDIA');
insert into HR.t03 values (2,'USA');

Here is my export statement

 DECLARE 
   EXPORT_JOB_NAME VARCHAR2(256) := 'TABLE_81111_EXPORT_JOB'; 
   DMP_FILE_NAME VARCHAR2(256)   := 'TABLE_81111_FILE.DMP'; 
   DIRECTORY_NAME VARCHAR2(256)  := 'LOG_FILE_DIR'; 
   LOG_FILE_NAME VARCHAR2(256)   := 'TABLE_81111_EXPORT_JOB.LOG'; 
   SCHEMA_NAME VARCHAR2(128)     := 'HR'; 
   TABLE_NAME VARCHAR2(128)      := 'T03'; 
   L_DP_HANDLE  NUMBER;
BEGIN 
   L_DP_HANDLE := DBMS_DATAPUMP.OPEN( 
        OPERATION   => 'EXPORT', 
        JOB_MODE    => 'TABLE', 
        REMOTE_LINK => NULL, 
        JOB_NAME    => EXPORT_JOB_NAME, 
        VERSION     => 'LATEST'); 
      DBMS_DATAPUMP.ADD_FILE( 
        HANDLE    => L_DP_HANDLE, 
        FILENAME  => DMP_FILE_NAME,  
        DIRECTORY => DIRECTORY_NAME); 
      DBMS_DATAPUMP.ADD_FILE( 
        HANDLE    => L_DP_HANDLE,  
        FILENAME  => LOG_FILE_NAME,  
        DIRECTORY => DIRECTORY_NAME,  
        FILETYPE  => DBMS_DATAPUMP.KU$_FILE_TYPE_LOG_FILE); 
  DBMS_DATAPUMP.METADATA_FILTER ( 
       HANDLE => L_DP_HANDLE, 
       NAME   => 'SCHEMA_EXPR', 
       VALUE  => '= '''||SCHEMA_NAME||'''');  
  DBMS_DATAPUMP.METADATA_FILTER (  
       HANDLE => L_DP_HANDLE, 
       NAME   => 'NAME_EXPR', 
       VALUE  => '= '''||TABLE_NAME||''''); 
  DBMS_DATAPUMP.SET_PARAMETER(HANDLE => L_DP_HANDLE, NAME => 'METRICS', VALUE => 1); 
  DBMS_DATAPUMP.START_JOB(L_DP_HANDLE); 
END;

Here is my import statement

DECLARE 
   IMPORT_JOB_NAME VARCHAR2(256) := 'TABLE_81111_IMPORT_JOB5'; 
   DMP_FILE_NAME VARCHAR2(256)   := 'TABLE_81111_FILE.DMP'; 
   DIRECTORY_NAME VARCHAR2(256)  := 'LOG_FILE_DIR'; 
   LOG_FILE_NAME VARCHAR2(256)   := 'TABLE_81111_IMPORT_JOB.LOG'; 
   L_DP_HANDLE  NUMBER;         
BEGIN 
   L_DP_HANDLE := DBMS_DATAPUMP.OPEN( 
        OPERATION   => 'IMPORT', 
        JOB_MODE    => 'TABLE', 
        REMOTE_LINK => NULL, 
        JOB_NAME    => IMPORT_JOB_NAME, 
        VERSION     => 'LATEST'); 
      DBMS_DATAPUMP.ADD_FILE( 
        HANDLE    => L_DP_HANDLE, 
        FILENAME  => DMP_FILE_NAME,  
        DIRECTORY => DIRECTORY_NAME); 
      DBMS_DATAPUMP.ADD_FILE( 
        HANDLE    => L_DP_HANDLE,  
        FILENAME  => LOG_FILE_NAME,  
        DIRECTORY => DIRECTORY_NAME,  
        FILETYPE  => DBMS_DATAPUMP.KU$_FILE_TYPE_LOG_FILE); 
      DBMS_DATAPUMP.SET_PARAMETER(HANDLE => L_DP_HANDLE, NAME => 'METRICS', VALUE => 1); 
      DBMS_DATAPUMP.SET_PARAMETER(HANDLE => L_DP_HANDLE, NAME => 'TABLE_EXISTS_ACTION', 
                                   VALUE => 'TRUNCATE'); 
  
  DBMS_DATAPUMP.START_JOB(L_DP_HANDLE); 
END;

This Import is failed with this error

ORA-31684: Object type CONSTRAINT:"HR"."PK_T03" already exists

Surprisingly after the import failure HR.T03 looks like this:

ID   COL1
--   ----
1    INDIA
2    USA
1    INDIA
2    USA

Though I mention TABLE_EXISTS_ACTION as TRUNCATE still DataPump acting like APPEND and COL1 is still Primary key, I'm not sure how duplicate values are stored there.

I tried same thing in a PDB with Normal mode, it is working as expected.

Can anybody make me understand why DBMS_DATAPUMP Import behaves differently when PDB is in Upgrade mode?

Is there any user guide or any document where these mentioned?

-Thank you so much

Comments
Post Details
Added on Apr 11 2024
6 comments
75 views