 | | | disable pk works differently in oracle 9 and oracle 10? | disable pk works differently in oracle 9 and oracle 10? 2006-06-09 - By Connor McDonald
On 6/9/06, Mladen Gogala <gogala@(protected)> wrote:
>
>
> 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
>
>
>
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 (See ORA-00942.ora-code.com): 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@(protected)
web: http://www.oracledba.co.uk
"Semper in excremento, sole profundum qui variat"
<br><br>
<div><span class="gmail_quote">On 6/9/06, <b class="gmail_sendername">Mladen
Gogala</b> <<a href="mailto:gogala@(protected)">gogala@(protected)</a>
> wrote:</span>
<blockquote class="gmail_quote" style="PADDING-LEFT: 1ex; MARGIN: 0px 0px 0px 0
.8ex; BORDER-LEFT: #ccc 1px solid"><br>On 06/08/2006 01:40:26 PM, <a href=
"mailto:genegurevich@(protected)">genegurevich@(protected)
</a> wrote:<br>> Hi all:<br><br>><br>> SQL> create table
test1 (f1 number);<br>><br>> Table created.<br>><br>> SQL>
create unique index test1_pk on test1 (f1);<br>><br>> Index created.
<br>
><br>> SQL> alter table test1 add constraint test1_pk primary key 
; (f1) using<br>> index;<br>><br>> Table altered.<br>><br>>
SQL> select index_name from dba_indexes where table_name = 'TEST1';<br>
> TEST1_PK<br>><br>> SQL> alter table test1 disable
primary key;<br>><br>> Table altered.<br>><br>> SQL>  
;select index_name from dba_indexes where table_name = 'TEST1';<br>> TEST1_PK
<br>>
<br>> Here the index stays after the PK is disabled.<br>><br>> This is
a big difference IMO and I wonder whether this is a new feature in<br>>
oracle10 or whether this is something I am not<br>> doing correctly. If
anyone has any insight on that please let me know
<br>><br>> thank you<br>><br>> Gene Gurevich<br>> Oracle
Engineering<br>> 224-405-4079<br><br><br>Gene, it must be a bug in your
version. In my database, <a href="http://10.2.0.2">10.2.0.2</a>, the index goes
as well:
<br><br><br>Connected to:<br>Oracle Database 10g Enterprise Edition Release 10
.2.0.2.0 - Production<br>With the Partitioning, OLAP and Data Mining options<br>
<br>SQL> create table test1 (f1 number);<br><br>Table created.
<br><br>SQL> create unique index test1_pk on test1 (f1);<br><br>Index
created.<br><br>SQL> alter table test1 add constraint test1_pk primary key
(f1) using<br>2 index test1_pk;<br><br>Table altered.<br>
<br>SQL> alter table test1 disable constraint test1_pk drop index;
<br><br>Table altered.<br><br>SQL> select count(*) from user_indexes where
index_name='TEST1_PK';<br><br>COUNT(*)<br>-- ---- --<br>  
; 0<br><br>SQL><br><br><br>It's probably the infamous
RTFM bug in your version.
<br><br>--<br>Mladen Gogala<br><a href="http://www.mgogala.com">http://www
.mgogala.com</a><br><br>--<br><a href="http://www.freelists.org/webpage/oracle-l
">http://www.freelists.org/webpage/oracle-l</a><br><br><br></blockquote>
</div>
<div> </div>
<div>Well, yes Mladen, if you <em>change</em> 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 <em>defa</em>ult behaviour between v9 and v10, my <a href="http
://10.2.0.2">
10.2.0.2</a> results below:</div>
<div><br>SQL> select * from v$version;</div>
<p>BANNER<br>-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ------
<br>Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Prod<br>PL/SQL
Release 10.2.0.2.0 - Production<br>CORE 10.2.0.2.0  
; Production
<br>TNS for 32-bit Windows: Version 10.2.0.2.0 - Production<br>NLSRTL Version
10.2.0.2.0 - Production</p>
<p>SQL> drop table test1;<br>drop table test1<br>
*<br>ERROR at line 1:<br>ORA-00942 (See ORA-00942.ora-code.com): table
or view does not exist</p>
<p><br>SQL> create table test1 (f1 number);</p>
<p>Table created.</p>
<p>SQL> create unique index test1_pk on test1 (f1);</p>
<p>Index created.</p>
<p>SQL> alter table test1 add constraint test1_pk primary key (f1)
using index;</p>
<p>Table altered.</p>
<p>SQL> select index_name from dba_indexes where table_name = 'TEST1';</p>
<p>INDEX_NAME<br>-- ---- ---- ---- ---- ---- --<br>TEST1_PK</p>
<p>SQL> alter table test1 disable primary key;</p>
<p>Table altered.</p>
<p>SQL> select index_name from dba_indexes where table_name = 'TEST1';</p>
<p>INDEX_NAME<br>-- ---- ---- ---- ---- ---- --<br>TEST1_PK<br></p>
<div> </div>
<div>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<br clear="all"><br>-- <br>Connor
McDonald<br>===========================<br>email: <a href="mailto:connor
_mcdonald@(protected)">
connor_mcdonald@(protected)</a><br>web: <a href="http://www.oracledba
.co.uk">http://www.oracledba.co.uk</a><br><br>"Semper in excremento, sole
profundum qui variat" </div>
|
|
|