On 6/9/06, Mladen Gogala <gogala@sbcglobal.net> wrote:

On 06/08/2006 01:40:26 PM, genegurevich@discoverfinancial.com wrote:
> Hi all:

>
> SQL>  create table test1 (f1 number);
>
> Table created.
>
> SQL>  create unique index test1_pk on test1 (f1);
>
> Index created.
>
> SQL> alter table test1 add constraint test1_pk primary key  (f1) using
> index;
>
> Table altered.
>
> SQL> select index_name from dba_indexes where table_name = 'TEST1';
> TEST1_PK
>
> SQL>  alter table test1 disable primary key;
>
> Table altered.
>
> SQL>  select index_name from dba_indexes where table_name = 'TEST1';
> TEST1_PK
>
> Here the index stays after the PK is disabled.
>
> This is a big difference IMO and I wonder whether this is a new feature in
> oracle10 or whether this is something I am not
> doing correctly. If anyone has any insight on that please let me know
>
> thank you
>
> Gene Gurevich
> Oracle Engineering
> 224-405-4079


Gene, it must be a bug in your version. In my database, 10.2.0.2, the index goes as well:


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> create table test1 (f1 number);

Table created.

SQL> create unique index test1_pk on test1 (f1);

Index created.

SQL> alter table test1 add constraint test1_pk primary key  (f1) using
2  index test1_pk;

Table altered.

SQL> alter table test1 disable constraint test1_pk drop index;

Table altered.

SQL> select count(*) from user_indexes where index_name='TEST1_PK';

COUNT(*)
----------
        0

SQL>


It's probably the infamous RTFM bug in your version.

--
Mladen Gogala
http://www.mgogala.com

--
http://www.freelists.org/webpage/oracle-l

 

Well, yes Mladen, if you change the script, then you do get different results....but I'm not sure that's the point the OP was making.  Its a change in default behaviour between v9 and v10, my 10.2.0.2 results below:

SQL> select * from v$version;

 

I have to admit I like the new default...now if only I could set a unique index to unusable I'd be a happy camper

--
Connor McDonald
===========================
email: connor_mcdonald@yahoo.com
web:   http://www.oracledba.co.uk

"Semper in excremento, sole profundum qui variat"