Re: disable pk works differently in oracle 9 and oracle 10?

From: Connor McDonald <mcdonald.connor_at_gmail.com>
Date: Fri, 9 Jun 2006 12:59:17 +0800
Message-ID: <5e3048620606082159w4bb1b9d4y994d063a0a57e38d@mail.gmail.com>

On 6/9/06, Mladen Gogala <gogala_at_sbcglobal.net> wrote:
>
>
> On 06/08/2006 01:40:26 PM, genegurevich_at_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 *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_at_yahoo.com
web:   http://www.oracledba.co.uk

"Semper in excremento, sole profundum qui variat"

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

Received on Thu Jun 08 2006 - 23:59:17 CDT