Everyone:
This is the answer from oracle
So in 9.2
alter table test1 disable primary key ;
Is the same as
alter table test1 disable primary key DROP index;
But in 10.2
alter table test1 disable primary key ;
Is the same as
alter table test1 disable primary key KEEP index;
This is also documented for disable constraint in bug 3835171
Bottom line is that the default behavior of the alter table disable primary
key command has changed between
versions 9 and 10
thanks to everyone who replied to my post
thank you
Gene Gurevich
Oracle Engineering
224-405-4079
genegurevich@(protected)
overfinancial.com
Sent by: To
oracle-l-bounce@(protected)>
reelists.org cc
Subject
06/08/2006 12:40 disable pk works differently in
PM oracle 9 and oracle 10?
Please respond to
genegurevich@(protected)
overfinancial.com
Hi all:
I have noticed that something that I was able to do in oracle9 can't be
done in oracle10. This is very annoying and
I would appreciate any thoughts on this:
Oracle 9:
SQL> select * from v$version;
Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
PL/SQL Release 9.2.0.4.0 - Production
CORE 9.2.0.3.0 Production
TNS for IBM/AIX RISC System/6000: Version 9.2.0.4.0 - Production
NLSRTL Version 9.2.0.4.0 - Production
SQL> drop table test1;
Table dropped.
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';
no rows selected
As you see when I disable the primary key, my index goes away as well. When
I do the same in oracle 10G however
things are different:
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bi
PL/SQL Release 10.2.0.2.0 - Production
CORE 10.2.0.2.0 Production
TNS for IBM/AIX RISC System/6000: Version 10.2.0.2.0 - Productio
NLSRTL Version 10.2.0.2.0 - Production
5 rows selected.
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';
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
--
http://www.freelists.org/webpage/oracle-l
--
http://www.freelists.org/webpage/oracle-l
