On 6/9/06, Mladen Gogala <gogala@xxxxxxxxxxxxx> wrote:
On 06/08/2006 01:40:26 PM, genegurevich@xxxxxxxxxxxxxxxxxxxxx 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
-- //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 *defa*ult behaviour between v9 and v10, my 10.2.0.2 results below:
SQL> select * from v$version;
BANNER ---------------------------------------------------------------- Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Prod PL/SQL Release 10.2.0.2.0 - Production CORE 10.2.0.2.0 Production TNS for 32-bit Windows: Version 10.2.0.2.0 - Production NLSRTL Version 10.2.0.2.0 - Production
SQL> drop table test1;
drop table test1
*
ERROR at line 1:
ORA-00942: table or view does not exist
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';
INDEX_NAME ------------------------------ TEST1_PK
SQL> alter table test1 disable primary key;
Table altered.
SQL> select index_name from dba_indexes where table_name = 'TEST1';
INDEX_NAME ------------------------------ TEST1_PK
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@xxxxxxxxx web: http://www.oracledba.co.uk
