Indexes Associated With Primary Key Constraints Of Imported Tables Are Not Dropped When Constraints Are Disabled (Doc ID 887208.1)

Last updated on MAY 13, 2021

Applies to:

Symptoms

Create a table with primary key constraint with "using index" clause. There are no indexes defined for this table before the primary key constraint is created. As such, when creating the constraint, an unique index with the same name with the constraint name is created. If the constraint is disabled, the index is dropped.

The table is exported and then imported. After the import when the constraint is disabled the index is not dropped anymore.

This happens with both Data Pump and classical export/import tools.

NOTE:  In the testcase content below, the user information and metadata used represents fictitious data.  Any similarity to actual persons, living or dead, is purely coincidental and not intended in any manner.

Example:

1. Create the table and its primary key.

sqlplus /nolog

SQL> conn test/test
Connected.

SQL> create table pktest( n integer );

Table created.

SQL> alter table pktest add (
2 constraint pk_pktest
3 primary key(n)
4 USING INDEX
5 );

Table altered.

SQL> select index_name from user_indexes where table_name = 'PKTEST';

INDEX_NAME
------------------------------
PK_PKTEST

SQL> select constraint_name, constraint_type, index_name, status
2 from user_constraints where table_name='PKTEST';

CONSTRAINT_NAME C INDEX_NAME STATUS
------------------------------ - ------------------------------ --------
PK_PKTEST P PK_PKTEST ENABLED

SQL> alter table pktest disable CONSTRAINT pk_pktest;

Table altered.

SQL> select constraint_name, constraint_type, index_name, status
2 from user_constraints where table_name='PKTEST';

CONSTRAINT_NAME C INDEX_NAME STATUS
------------------------------ - ------------------------------ --------
PK_PKTEST P DISABLED

SQL> select index_name from user_indexes where table_name = 'PKTEST';

no rows selected


2. Export table:

expdp test/test tables=pktest directory=dp_dir dumpfile=test.dmp

Export: Release 10.2.0.4.0 - Production on Monday, 14 September, 2009 15:20:25

Copyright (c) 2003, 2007, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "TEST"."SYS_EXPORT_TABLE_01": test/******** tables=pktest directory=dp_dir dumpfile=test.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/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
. . exported "TEST"."PKTEST" 0 KB 0 rows
Master table "TEST"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for TEST.SYS_EXPORT_TABLE_01 is:
D:\DP_DIR\TEST.DMP
Job "TEST"."SYS_EXPORT_TABLE_01" successfully completed at 15:20:38

3. Drop the table:

SQL> conn test/test
Connected.
SQL> drop table pktest;

Table dropped.

4. Import it:

impdp test/test tables=pktest directory=dp_dir dumpfile=test.dmp

Import: Release 10.2.0.4.0 - Production on Monday, 14 September, 2009 16:24:10

Copyright (c) 2003, 2007, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "TEST"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "TEST"."SYS_IMPORT_TABLE_01": test/******** tables=pktest directory=dp_dir dumpfile=test.dmp
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "TEST"."PKTEST" 0 KB 0 rows
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Job "TEST"."SYS_IMPORT_TABLE_01" successfully completed at 16:24:13


5. Check the primary key constraint and its index behavior:

SQL> conn test/test
Connected.
SQL> select constraint_name, constraint_type, index_name, status
2 from user_constraints where table_name='PKTEST';

CONSTRAINT_NAME C INDEX_NAME STATUS
------------------------------ - ------------------------------ --------
PK_PKTEST P PK_PKTEST ENABLED

SQL> select index_name from user_indexes where table_name = 'PKTEST';

INDEX_NAME
------------------------------
PK_PKTEST

SQL> alter table pktest disable CONSTRAINT pk_pktest;

Table altered.

SQL> select constraint_name, constraint_type, index_name, status
2 from user_constraints where table_name='PKTEST';

CONSTRAINT_NAME C INDEX_NAME STATUS
------------------------------ - ------------------------------ --------
PK_PKTEST P DISABLED

SQL> select index_name from user_indexes where table_name = 'PKTEST';

INDEX_NAME
------------------------------
PK_PKTEST

Changes

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.