Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

One of the largest tables in our system has its primary key setup as a globally ranged partitioned (in huge, useless, multi-billion value ranges) non-unique index. Its values are just a sequence value and they are definitely unique. I am wanting to change the index to just a normal global (the table is partitioned), unique index.

Can I do that without dropping the constraint and index and then rebuilding - during which time the table would be without this vital index?

share|improve this question
    
Does the table itself have a proper PK? You can't use DBMS_REDEFINITION unless it has. –  Phil Jan 10 '13 at 14:38
    
@Phil 'proper PK'? The PK is what I need to change. The PK is an int, from a sequence, but its been stupidly defined as the partitioned non-unique range . –  rfusca Jan 10 '13 at 14:52
add comment

1 Answer 1

up vote 3 down vote accepted

You can do this with DBMS_REDEFINITION, but you'll need double the space occupied by the table.

If I read your question correctly, this should do it:

[oracle@node1 ~]$ sqlplus phil/phil

SQL*Plus: Release 11.2.0.2.0 Production on Sun Jan 6 18:47:12 2013

Copyright (c) 1982, 2010, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> set echo on
SQL> @part
SQL>
SQL> drop table PARTITIONED_INTERIM;

Table dropped.

SQL>
SQL> drop table partitioned;

Table dropped.

SQL>
SQL> create table partitioned
  2  (
  3  pk number,
  4  blah  varchar(40),
  5  blah2 varchar(40)
  6  )
  7  partition by range (pk)
  8  (partition p1 values less than (1000),
  9   partition p2 values less than (10000),
 10   partition p3 values less than (30000),
 11   partition p4 values less than (MAXVALUE)
 12   );

Table created.

SQL>
SQL> create index pk_idx
  2  on partitioned (pk)
  3  global partition by range (pk)
  4  (partition p1 values less than (1000),
  5   partition p2 values less than (10000),
  6   partition p3 values less than (30000),
  7   partition p4 values less than (MAXVALUE)
  8   )
  9  ;

Index created.

SQL>
SQL> ALTER TABLE partitioned ADD CONSTRAINT ppk PRIMARY KEY (pk) USING INDEX pk_idx;

Table altered.

SQL>
SQL> select index_name, uniqueness , table_name, partitioned
  2  from user_indexes
  3  where table_name = 'PARTITIONED';

INDEX_NAME                     UNIQUENES TABLE_NAME                     PAR
------------------------------ --------- ------------------------------ ---
PK_IDX                         NONUNIQUE PARTITIONED                    YES

SQL>
SQL> EXEC dbms_redefinition.can_redef_table('PHIL', 'PARTITIONED');

PL/SQL procedure successfully completed.

SQL>
SQL> create table PARTITIONED_INTERIM as ( select * from PARTITIONED where 1=0 );

Table created.

SQL>
SQL> exec DBMS_REDEFINITION.start_redef_table(uname =>     'PHIL',orig_table => 'PARTITIONED',int_table => 'PARTITIONED_INTERIM');

PL/SQL procedure successfully completed.

SQL>
SQL> create unique index partitioned_interim_pk on partitioned_interim (pk) global online;

Index created.

SQL>
SQL> exec dbms_redefinition.sync_interim_table(uname => 'PHIL',orig_table => 'PARTITIONED',int_table => 'PARTITIONED_INTERIM');

PL/SQL procedure successfully completed.

SQL>
SQL> exec dbms_redefinition.finish_redef_table(uname => 'PHIL',orig_table => 'PARTITIONED',int_table => 'PARTITIONED_INTERIM');

PL/SQL procedure successfully completed.

SQL>
SQL> select index_name, uniqueness , table_name, partitioned
  2  from user_indexes
  3  where table_name = 'PARTITIONED';

INDEX_NAME                     UNIQUENES TABLE_NAME                     PAR
------------------------------ --------- ------------------------------ ---
PARTITIONED_INTERIM_PK         UNIQUE    PARTITIONED                    NO

SQL>
SQL>
SQL>

Note that you'll have to deal with other indexes/objects etc too, using DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS() and DBMS_REDEFINITION.REGISTER_DEPENDENT_OBJECT().

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.