My Oracle Support Banner

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

Last updated on MAY 18, 2021

Applies to:

Gen 1 Exadata Cloud at Customer (Oracle Exadata Database Cloud Machine) - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Oracle Database Backup Service - Version N/A and later
Oracle Database Cloud Exadata Service - Version N/A 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 the testcase user:

create user tc identified by <PASSWORD>
default tablespace users
temporary tablespace temp;
grant connect, resource, unlimited tablespace to tc;
grant read,write on directory DATA_PUMP_DIR to tc;

-- create a partitioned table with a PK local partitioned index:
connect tc/<PASSWORD>

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
);

 

-- Review the Primary Key Constraint DDL

set linesize 128
col constraint_name format a25;
select constraint_name from user_constraints where table_name='TAB1' and constraint_type='P';

CONSTRAINT_NAME
-------------------------
SYS_C007880

set long 100000
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/<PASSWORD> directory=DATA_PUMP_DIR tables=tab1 dumpfile=<DUMPFILE> logfile=<LOGFILE>

Export: Release 11.2.0.3.0 - Production on Mon Nov 19 12:27:43 2012
......
Starting "TC"."SYS_EXPORT_TABLE_01": tc/******** directory=DATA_PUMP_DIR tables=tab1 dumpfile=<DUMPFILE> logfile=<LOGFILE>
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:
<DUMPFILE>
Job "TC"."SYS_EXPORT_TABLE_01" successfully completed at 12:27:57

 

-- drop the table and recreate it through impdp:

>sqlplus tc/tc
drop table tab1 purge;
exit

#> impdp tc/<PASSWORD> directory=DATA_PUMP_DIR dumpfile=<DUMPFILE> tables=tab1 transform=SEGMENT_ATTRIBUTES:N 

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/******** directory=DATA_PUMP_DIR dumpfile=<DUMPFILE> tables=tab1 transform=SEGMENT_ATTRIBUTES:N 
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 changed as confirmed:

SQL>connect tc/<PASSWORD>

set linesize 128
col constraint_name format a25;
select constraint_name from user_constraints where table_name='TAB1' and constraint_type='P';

CONSTRAINT_NAME
-------------------------
SYS_C007897

 

set long 100000
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

 

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

 

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
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.