After DataPump Import (IMPDP) With SEGMENT_ATTRIBUTES:N The Primary Key Partitioned Index Is Non-partitioned (Doc ID 1506773.1)

Last updated on NOVEMBER 22, 2012

Applies to:

Oracle Server - Enterprise Edition - Version 11.2.0.2.0 and later
Information in this document applies to any platform.

Symptoms

After DataPump import with TRANSFORM=SEGMENT_ATTRIBUTES:N of a partitioned table with a primary key local partitioned index, the primary key becomes global non-partitioned.

The example below describes the issue:

-- create a partitioned table with a PK local partitioned index:
CREATE TABLE TC.TAB1
(
   A_KEY       VARCHAR2(30) NOT NULL ENABLE,
   ADDRESS_KEY VARCHAR2(30),
   B_TYPE      VARCHAR2(50) NOT NULL ENABLE,
   PRIMARY KEY (A_KEY, B_TYPE)   USING INDEX STORAGE (BUFFER_POOL DEFAULT) LOCAL ENABLE
)
PARTITION BY HASH (A_KEY, B_TYPE)
(
   PARTITION SYS_P47481 TABLESPACE USERS,
   PARTITION SYS_P47482 TABLESPACE USERS
); 

select dbms_metadata.get_ddl ('CONSTRAINT', 'SYS_C007880', 'TC') from dual;

DBMS_METADATA.GET_DDL('CONSTRAINT', 'SYS_C007880', 'TC')
--------------------------------------------------------------------------------
ALTER TABLE "TC"."TAB1" ADD PRIMARY KEY ("A_KEY", "B_TYPE")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) LOCAL
(PARTITION "SYS_P47481"
TABLESPACE "USERS" ,
PARTITION "SYS_P47482"
TABLESPACE "USERS" )  ENABLE

-- export the TAB1 table:
#> expdp tc/tc directory=dptest tables=tab1 dumpfile=fp.dmp

Export: Release 11.2.0.3.0 - Production on Mon Nov 19 12:27:43 2012
......
Starting "TC"."SYS_EXPORT_TABLE_01":  tc/******** directory=dptest tables=tab1 dumpfile=fp.dmp
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 0 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
. . exported "TC"."TAB1":"SYS_P47481"                        0 KB       0 rows
. . exported "TC"."TAB1":"SYS_P47482"                        0 KB       0 rows
Master table "TC"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for TC.SYS_EXPORT_TABLE_01 is:
  /tmp/fp.dmp
Job "TC"."SYS_EXPORT_TABLE_01" successfully completed at 12:27:57

-- drop the table and recreate it through impdp:
#> impdp tc/tc dumpfile=fp.dmp tables=tab1 transform=SEGMENT_ATTRIBUTES:N directory=dptest

Import: Release 11.2.0.3.0 - Production on Mon Nov 19 12:29:58 2012
................................
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning option
Master table "TC"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "TC"."SYS_IMPORT_TABLE_01":  tc/******** dumpfile=fp.dmp tables=tab1 transform=SEGMENT_ATTRIBUTES:N directory=dptest
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "TC"."TAB1":"SYS_P47481"                        0 KB       0 rows
. . imported "TC"."TAB1":"SYS_P47482"                        0 KB       0 rows
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Job "TC"."SYS_IMPORT_TABLE_01" successfully completed at 12:30:00

-- after impdp, the PK is:
SQL> select dbms_metadata.get_ddl ('CONSTRAINT', 'SYS_C007897', 'TC') from dual;

DBMS_METADATA.GET_DDL('CONSTRAINT','SYS_C007897','TC')
--------------------------------------------------------------------------------
ALTER TABLE "TC"."TAB1" ADD PRIMARY KEY ("A_KEY", "B_TYPE")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
TABLESPACE "USERS"  ENABLE

==> The Primary Key becomes global non-partitioned after the import

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