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

Last updated on OCTOBER 14, 2009

Applies to:

Oracle Server - Enterprise Edition - Version: 10.2.0.4 to 11.2.0.0
This problem can occur on any platform.

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.

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

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