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

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_at_disc                                             
             overfinancial.com                                             
             Sent by:                                                   To 
             oracle-l-bounce_at_f         "oracle-l" <oracle-l_at_freelists.org> 
             reelists.org                                               cc 
                                                                           
                                                                   Subject 
             06/08/2006 12:40          disable pk works differently in     
             PM                        oracle 9 and oracle 10?             
                                                                           
                                                                           
             Please respond to                                             
             genegurevich_at_disc                                             
             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 Received on Tue Jun 13 2006 - 15:31:15 CDT