disable pk works differently in oracle 9 and oracle 10? 2006-06-09 - By genegurevich@(protected)
Mladen,
thanks. I was not aware of the "drop index" clause in the alter table
disable constraint.
thank you
Gene Gurevich
Oracle Engineering
224-405-4079
Mladen Gogala
<gogala@(protected)
.net> To
Sent by: genegurevich@(protected)
oracle-l-bounce@(protected) cc
reelists.org oracle-l <oracle-l@(protected)>
Subject
Re: disable pk works differently in
06/08/2006 06:08 oracle 9 and oracle 10?
PM
Please respond to
gogala@(protected)
net
On 06/08/2006 01:40:26 PM, genegurevich@(protected) 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
--
http://www.freelists.org/webpage/oracle-l
|
|
