查了资料，有两种情况，一种Oracle 10G的一个BUG：3894962，但一直没有发布补丁。

Note:292096.1,Note:370633.1这两篇文章有介绍，当导出导入表的时候，如果有主键约束及索引，索引导入的时候没有和主键约束建立依赖，所以删除约束时，索引没有被删除。

另一种情况是Oracle 10G的一项改变,Note:309821.1

提供的解决办法就是加上删除索引的语法：alter table < table > drop constraint < constraint > drop index;
注意：Drop index的语法是9i才支持的。

<script language="JavaScript"></script>

The information in this document applies to:

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

Symptoms

Drop constraint does not drop a associated index in 10G

Cause

In 10g, a change was made to an internal function "atbdui" to not to drop the user index when the constraint using the index is dropped.

In 9i, this behavior is different the user index
gets dropped when the constraint is dropped.

Select index_name,generated from dba_indexes where index_name='< ;index_name >';

Generated column would show 'Y' If the index is system generated.If 'N' ,it is user generated.

Solution

This behaviour is because of the code changes made in 10G.

Use the workaround:

alter table < table > drop constraint < constraint > drop index;

Note

If the index is non unique, we can still use them for enforcing primary key constraints or unique constraints.

But dropping the constraint does not drop the non-unique index.
This behaviour is seen from 8174 to 10.2.0.0.

Sample Output

SQL> create table test( a number );

Table created.

SQL> create index ind on test ( a );

Index created.

SQL> alter table test add constraint c1_pk primary key(a) using index;

Table altered.

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

INDEX_NAME
------------------------------
IND

SQL> alter table test drop constraint c1_pk;

Table altered.

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

INDEX_NAME
------------------------------
IND

References

Note 292096.1 - Encountering ora-00955 when trying to create primary key on an imported table in 10g

Keywords

'USING~INDEX'