Search My Oracle Blog

Custom Search

17 October, 2011

DBMS_REDEFINITION to redefine a Partition -- and the impact of deferred_segment_creation

During a forums discussion on START_REDEF, I created test cases in 10.2 and 11.2 to test DBMS_REDEFINITION to redefine a Partition (instead of using ALTER TABLE .... EXCHANGE PARTITION).

If there is a Primary Key, normally, I should use options_flag => DBMS_REDEFINITION.CONS_USE_PK However, if I use options_flag => DBMS_REDEFINITION.CONS_USE_ROWID, Oracle attempts to create another Unique Index.

Here's the peculiarity. If I do not have any row present in the partition (so it is a segmentless partition), the FINISH_REDEF fails with an Object deadlock :
(see ANSWER at the end of this post)

SQL> select * from v$version;

BANNER                                                                                                      
--------------------------------------------------------------------------------                            
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production                                      
PL/SQL Release 11.2.0.1.0 - Production                                                                      
CORE    11.2.0.1.0      Production                                                                                  
TNS for Linux: Version 11.2.0.1.0 - Production                                                              
NLSRTL Version 11.2.0.1.0 - Production                                                                      

SQL> show parameter deferred;

NAME                                 TYPE        VALUE                                                      
------------------------------------ ----------- ------------------------------                             
deferred_segment_creation            boolean     TRUE                                                       
SQL>
SQL> drop materialized view int_salestable;
drop materialized view int_salestable
*
ERROR at line 1:
ORA-12003: materialized view "HEMANT"."INT_SALESTABLE" does not exist


SQL>
SQL> drop TABLE salestable;

Table dropped.

SQL>
SQL> CREATE TABLE salestable
2  (s_saleid NUMBER,
3  s_saledate DATE,
4  s_custid NUMBER,
5  s_totalprice NUMBER)
6  logging
7  TABLESPACE users
8  PARTITION BY RANGE(s_saledate)
9  (PARTITION sal03q1 VALUES LESS THAN (TO_DATE('01-APR-2003', 'DD-MON-YYYY')),
10  PARTITION sal03q2 VALUES LESS THAN (TO_DATE('01-JUL-2003', 'DD-MON-YYYY')),
11  PARTITION sal03q3 VALUES LESS THAN (TO_DATE('01-OCT-2003', 'DD-MON-YYYY')),
12  PARTITION sal03q4 VALUES LESS THAN (TO_DATE('01-JAN-2004', 'DD-MON-YYYY')));

Table created.

SQL>
SQL>
SQL> alter table salestable modify s_saleid number primary key;

Table altered.

SQL> CREATE INDEX sales_index ON salestable(s_saledate, s_saleid, s_custid) logging LOCAL;

Index created.

SQL>
SQL> select partition_name, tablespace_name from user_tab_partitions
2  where table_name = 'SALESTABLE' order by partition_name
3  /

PARTITION_NAME                 TABLESPACE_NAME                                                              
------------------------------ ------------------------------                                               
SAL03Q1                        USERS                                                                        
SAL03Q2                        USERS                                                                        
SAL03Q3                        USERS                                                                        
SAL03Q4                        USERS                                                                        

SQL>
SQL> select partition_name, tablespace_name from user_ind_partitions
2  where index_name = 'SALES_INDEX' order by partition_name
3  /

PARTITION_NAME                 TABLESPACE_NAME                                                              
------------------------------ ------------------------------                                               
SAL03Q1                        USERS                                                                        
SAL03Q2                        USERS                                                                        
SAL03Q3                        USERS                                                                        
SAL03Q4                        USERS                                                                        

SQL>
SQL>
SQL> drop TABLE int_salestable ;

Table dropped.

SQL>
SQL> CREATE TABLE int_salestable
2  (s_saleid NUMBER,
3  s_saledate DATE,
4  s_custid NUMBER,
5  s_totalprice NUMBER)
6  logging
7  tablespace example;

Table created.

SQL>
SQL> alter table int_salestable modify s_saleid number primary key;

Table altered.

SQL>
SQL> CREATE INDEX int_sales_index ON int_salestable
2  (s_saledate, s_saleid, s_custid)
3  logging
4  TABLESPACE example;

Index created.

SQL>
SQL>
SQL> BEGIN
2  DBMS_REDEFINITION.CAN_REDEF_TABLE(
3  uname => 'HEMANT',
4  tname => 'SALESTABLE',
5  options_flag => DBMS_REDEFINITION.CONS_USE_ROWID,
6  part_name => 'SAL03Q1');
7  END;
8  /

PL/SQL procedure successfully completed.

SQL>
SQL> BEGIN
2  DBMS_REDEFINITION.START_REDEF_TABLE(
3  uname => 'HEMANT',
4  orig_table => 'SALESTABLE',
5  int_table => 'INT_SALESTABLE',
6  col_mapping => NULL,
7  options_flag => DBMS_REDEFINITION.CONS_USE_ROWID,
8  part_name => 'SAL03Q1');
9  END;
10  /

PL/SQL procedure successfully completed.

SQL>
SQL>
SQL> BEGIN
2  DBMS_REDEFINITION.FINISH_REDEF_TABLE(
3  uname => 'HEMANT',
4  orig_table => 'SALESTABLE',
5  int_table => 'INT_SALESTABLE',
6  part_name => 'SAL03Q1');
7  END;
8  /
BEGIN
*
ERROR at line 1:
ORA-42012: error occurred while completing the redefinition
ORA-04020: deadlock detected while trying to lock object HEMANT.INT_SALESTABLE
ORA-06512: at "SYS.DBMS_REDEFINITION", line 78
ORA-06512: at "SYS.DBMS_REDEFINITION", line 1680
ORA-06512: at line 2


SQL>
SQL>
SQL> select partition_name, tablespace_name from user_tab_partitions
2  where table_name = 'SALESTABLE' order by partition_name
3  /

PARTITION_NAME                 TABLESPACE_NAME                                                              
------------------------------ ------------------------------                                               
SAL03Q1                        USERS                                                                        
SAL03Q2                        USERS                                                                        
SAL03Q3                        USERS                                                                        
SAL03Q4                        USERS                                                                        

SQL>
SQL> select partition_name, tablespace_name from user_ind_partitions
2  where index_name = 'SALES_INDEX' order by partition_name
3  /

PARTITION_NAME                 TABLESPACE_NAME                                                              
------------------------------ ------------------------------                                               
SAL03Q1                        USERS                                                                        
SAL03Q2                        USERS                                                                        
SAL03Q3                        USERS                                                                        
SAL03Q4                        USERS                                                                        

SQL>

Note the usage of CONS_USE_ROWID.

However, if the partition does have a row present, i.e it does have a segment created, then the FINISH_REDEF can succeed.
SQL> select * from v$version;

BANNER                                                                                                      
--------------------------------------------------------------------------------                            
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production                                      
PL/SQL Release 11.2.0.1.0 - Production                                                                      
CORE    11.2.0.1.0      Production                                                                                  
TNS for Linux: Version 11.2.0.1.0 - Production                                                              
NLSRTL Version 11.2.0.1.0 - Production                                                                      

SQL> show parameter deferred;

NAME                                 TYPE        VALUE                                                      
------------------------------------ ----------- ------------------------------                             
deferred_segment_creation            boolean     TRUE                                                       
SQL>
SQL> drop materialized view int_salestable;

Materialized view dropped.

SQL>
SQL> drop TABLE salestable;

Table dropped.

SQL>
SQL> CREATE TABLE salestable
2  (s_saleid NUMBER,
3  s_saledate DATE,
4  s_custid NUMBER,
5  s_totalprice NUMBER)
6  logging
7  TABLESPACE users
8  PARTITION BY RANGE(s_saledate)
9  (PARTITION sal03q1 VALUES LESS THAN (TO_DATE('01-APR-2003', 'DD-MON-YYYY')),
10  PARTITION sal03q2 VALUES LESS THAN (TO_DATE('01-JUL-2003', 'DD-MON-YYYY')),
11  PARTITION sal03q3 VALUES LESS THAN (TO_DATE('01-OCT-2003', 'DD-MON-YYYY')),
12  PARTITION sal03q4 VALUES LESS THAN (TO_DATE('01-JAN-2004', 'DD-MON-YYYY')));

Table created.

SQL>
SQL> insert into salestable partition (sal03q1) values (101,to_date('01-JAN-2003','DD-MON-YYYY'),1,2000);

1 row created.

SQL> commit;

Commit complete.

SQL>
SQL> alter table salestable modify s_saleid number primary key;

Table altered.

SQL> CREATE INDEX sales_index ON salestable(s_saledate, s_saleid, s_custid) logging LOCAL;

Index created.

SQL>
SQL> select partition_name, tablespace_name from user_tab_partitions
2  where table_name = 'SALESTABLE' order by partition_name
3  /

PARTITION_NAME                 TABLESPACE_NAME                                                              
------------------------------ ------------------------------                                               
SAL03Q1                        USERS                                                                        
SAL03Q2                        USERS                                                                        
SAL03Q3                        USERS                                                                        
SAL03Q4                        USERS                                                                        

SQL>
SQL> select partition_name, tablespace_name from user_ind_partitions
2  where index_name = 'SALES_INDEX' order by partition_name
3  /

PARTITION_NAME                 TABLESPACE_NAME                                                              
------------------------------ ------------------------------                                               
SAL03Q1                        USERS                                                                        
SAL03Q2                        USERS                                                                        
SAL03Q3                        USERS                                                                        
SAL03Q4                        USERS                                                                        

SQL>
SQL>
SQL> drop TABLE int_salestable ;

Table dropped.

SQL>
SQL> CREATE TABLE int_salestable
2  (s_saleid NUMBER,
3  s_saledate DATE,
4  s_custid NUMBER,
5  s_totalprice NUMBER)
6  logging
7  tablespace example;

Table created.

SQL>
SQL> alter table int_salestable modify s_saleid number primary key;

Table altered.

SQL>
SQL> CREATE INDEX int_sales_index ON int_salestable
2  (s_saledate, s_saleid, s_custid)
3  logging
4  TABLESPACE example;

Index created.

SQL>
SQL>
SQL> BEGIN
2  DBMS_REDEFINITION.CAN_REDEF_TABLE(
3  uname => 'HEMANT',
4  tname => 'SALESTABLE',
5  options_flag => DBMS_REDEFINITION.CONS_USE_ROWID,
6  part_name => 'SAL03Q1');
7  END;
8  /

PL/SQL procedure successfully completed.

SQL>
SQL> BEGIN
2  DBMS_REDEFINITION.START_REDEF_TABLE(
3  uname => 'HEMANT',
4  orig_table => 'SALESTABLE',
5  int_table => 'INT_SALESTABLE',
6  col_mapping => NULL,
7  options_flag => DBMS_REDEFINITION.CONS_USE_ROWID,
8  part_name => 'SAL03Q1');
9  END;
10  /

PL/SQL procedure successfully completed.

SQL>
SQL>
SQL> BEGIN
2  DBMS_REDEFINITION.FINISH_REDEF_TABLE(
3  uname => 'HEMANT',
4  orig_table => 'SALESTABLE',
5  int_table => 'INT_SALESTABLE',
6  part_name => 'SAL03Q1');
7  END;
8  /

PL/SQL procedure successfully completed.

SQL>
SQL>
SQL> select partition_name, tablespace_name from user_tab_partitions
2  where table_name = 'SALESTABLE' order by partition_name
3  /

PARTITION_NAME                 TABLESPACE_NAME                                                              
------------------------------ ------------------------------                                               
SAL03Q1                        EXAMPLE                                                                      
SAL03Q2                        USERS                                                                        
SAL03Q3                        USERS                                                                        
SAL03Q4                        USERS                                                                        

SQL>
SQL> select partition_name, tablespace_name from user_ind_partitions
2  where index_name = 'SALES_INDEX' order by partition_name
3  /

PARTITION_NAME                 TABLESPACE_NAME                                                              
------------------------------ ------------------------------                                               
SAL03Q1                        EXAMPLE                                                                      
SAL03Q2                        USERS                                                                        
SAL03Q3                        USERS                                                                        
SAL03Q4                        USERS                                                                        

SQL>


Well... the next thing is to test without deferred_segment_creation. This should succeed.
SQL> select * from v$version;

BANNER                                                                                                      
--------------------------------------------------------------------------------                            
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production                                      
PL/SQL Release 11.2.0.1.0 - Production                                                                      
CORE    11.2.0.1.0      Production                                                                                  
TNS for Linux: Version 11.2.0.1.0 - Production                                                              
NLSRTL Version 11.2.0.1.0 - Production                                                                      

SQL> show parameter deferred;

NAME                                 TYPE        VALUE                                                      
------------------------------------ ----------- ------------------------------                             
deferred_segment_creation            boolean     TRUE                                                       
SQL> alter session set deferred_segment_creation=FALSE;

Session altered.

SQL>
SQL> drop materialized view int_salestable;
drop materialized view int_salestable
*
ERROR at line 1:
ORA-12003: materialized view "HEMANT"."INT_SALESTABLE" does not exist


SQL>
SQL> drop TABLE salestable;

Table dropped.

SQL>
SQL> CREATE TABLE salestable
2  (s_saleid NUMBER,
3  s_saledate DATE,
4  s_custid NUMBER,
5  s_totalprice NUMBER)
6  logging
7  TABLESPACE users
8  PARTITION BY RANGE(s_saledate)
9  (PARTITION sal03q1 VALUES LESS THAN (TO_DATE('01-APR-2003', 'DD-MON-YYYY')),
10  PARTITION sal03q2 VALUES LESS THAN (TO_DATE('01-JUL-2003', 'DD-MON-YYYY')),
11  PARTITION sal03q3 VALUES LESS THAN (TO_DATE('01-OCT-2003', 'DD-MON-YYYY')),
12  PARTITION sal03q4 VALUES LESS THAN (TO_DATE('01-JAN-2004', 'DD-MON-YYYY')));

Table created.

SQL>
SQL>
SQL> alter table salestable modify s_saleid number primary key;

Table altered.

SQL> CREATE INDEX sales_index ON salestable(s_saledate, s_saleid, s_custid) logging LOCAL;

Index created.

SQL>
SQL> select partition_name, tablespace_name from user_tab_partitions
2  where table_name = 'SALESTABLE' order by partition_name
3  /

PARTITION_NAME                 TABLESPACE_NAME                                                              
------------------------------ ------------------------------                                               
SAL03Q1                        USERS                                                                        
SAL03Q2                        USERS                                                                        
SAL03Q3                        USERS                                                                        
SAL03Q4                        USERS                                                                        

SQL>
SQL> select partition_name, tablespace_name from user_ind_partitions
2  where index_name = 'SALES_INDEX' order by partition_name
3  /

PARTITION_NAME                 TABLESPACE_NAME                                                              
------------------------------ ------------------------------                                               
SAL03Q1                        USERS                                                                        
SAL03Q2                        USERS                                                                        
SAL03Q3                        USERS                                                                        
SAL03Q4                        USERS                                                                        

SQL>
SQL>
SQL> drop TABLE int_salestable ;

Table dropped.

SQL>
SQL> CREATE TABLE int_salestable
2  (s_saleid NUMBER,
3  s_saledate DATE,
4  s_custid NUMBER,
5  s_totalprice NUMBER)
6  logging
7  tablespace example;

Table created.

SQL>
SQL> alter table int_salestable modify s_saleid number primary key;

Table altered.

SQL>
SQL> CREATE INDEX int_sales_index ON int_salestable
2  (s_saledate, s_saleid, s_custid)
3  logging
4  TABLESPACE example;

Index created.

SQL>
SQL>
SQL> BEGIN
2  DBMS_REDEFINITION.CAN_REDEF_TABLE(
3  uname => 'HEMANT',
4  tname => 'SALESTABLE',
5  options_flag => DBMS_REDEFINITION.CONS_USE_ROWID,
6  part_name => 'SAL03Q1');
7  END;
8  /

PL/SQL procedure successfully completed.

SQL>
SQL> BEGIN
2  DBMS_REDEFINITION.START_REDEF_TABLE(
3  uname => 'HEMANT',
4  orig_table => 'SALESTABLE',
5  int_table => 'INT_SALESTABLE',
6  col_mapping => NULL,
7  options_flag => DBMS_REDEFINITION.CONS_USE_ROWID,
8  part_name => 'SAL03Q1');
9  END;
10  /

PL/SQL procedure successfully completed.

SQL>
SQL>
SQL> BEGIN
2  DBMS_REDEFINITION.FINISH_REDEF_TABLE(
3  uname => 'HEMANT',
4  orig_table => 'SALESTABLE',
5  int_table => 'INT_SALESTABLE',
6  part_name => 'SAL03Q1');
7  END;
8  /

PL/SQL procedure successfully completed.

SQL>
SQL>
SQL> select partition_name, tablespace_name from user_tab_partitions
2  where table_name = 'SALESTABLE' order by partition_name
3  /

PARTITION_NAME                 TABLESPACE_NAME                                                              
------------------------------ ------------------------------                                               
SAL03Q1                        EXAMPLE                                                                      
SAL03Q2                        USERS                                                                        
SAL03Q3                        USERS                                                                        
SAL03Q4                        USERS                                                                        

SQL>
SQL> select partition_name, tablespace_name from user_ind_partitions
2  where index_name = 'SALES_INDEX' order by partition_name
3  /

PARTITION_NAME                 TABLESPACE_NAME                                                              
------------------------------ ------------------------------                                               
SAL03Q1                        EXAMPLE                                                                      
SAL03Q2                        USERS                                                                        
SAL03Q3                        USERS                                                                        
SAL03Q4                        USERS                                                                        

SQL>


So, in 11.2, with deferred segments, CONS_USE_ROWID results in an object deadlock for an empty partition. What if I use CONS_USE_PK ?
SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

SQL> show parameter deferred;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
deferred_segment_creation            boolean     TRUE
SQL>
SQL> drop materialized view int_salestable;

Materialized view dropped.

SQL>
SQL> drop TABLE salestable;

Table dropped.

SQL>
SQL> CREATE TABLE salestable
2  (s_saleid NUMBER,
3  s_saledate DATE,
4  s_custid NUMBER,
5  s_totalprice NUMBER)
6  logging
7  TABLESPACE users
8  PARTITION BY RANGE(s_saledate)
9  (PARTITION sal03q1 VALUES LESS THAN (TO_DATE('01-APR-2003', 'DD-MON-YYYY')),
10  PARTITION sal03q2 VALUES LESS THAN (TO_DATE('01-JUL-2003', 'DD-MON-YYYY')),
11  PARTITION sal03q3 VALUES LESS THAN (TO_DATE('01-OCT-2003', 'DD-MON-YYYY')),
12  PARTITION sal03q4 VALUES LESS THAN (TO_DATE('01-JAN-2004', 'DD-MON-YYYY')));

Table created.

SQL>
SQL>
SQL> alter table salestable modify s_saleid number primary key;

Table altered.

SQL> CREATE INDEX sales_index ON salestable(s_saledate, s_saleid, s_custid) logging LOCAL;

Index created.

SQL>
SQL> select partition_name, tablespace_name from user_tab_partitions
2  where table_name = 'SALESTABLE' order by partition_name
3  /

PARTITION_NAME                 TABLESPACE_NAME
------------------------------ ------------------------------
SAL03Q1                        USERS
SAL03Q2                        USERS
SAL03Q3                        USERS
SAL03Q4                        USERS

SQL>
SQL> select partition_name, tablespace_name from user_ind_partitions
2  where index_name = 'SALES_INDEX' order by partition_name
3  /

PARTITION_NAME                 TABLESPACE_NAME
------------------------------ ------------------------------
SAL03Q1                        USERS
SAL03Q2                        USERS
SAL03Q3                        USERS
SAL03Q4                        USERS

SQL>
SQL>
SQL> drop TABLE int_salestable ;

Table dropped.

SQL>
SQL> CREATE TABLE int_salestable
2  (s_saleid NUMBER,
3  s_saledate DATE,
4  s_custid NUMBER,
5  s_totalprice NUMBER)
6  logging
7  tablespace example;

Table created.

SQL>
SQL> alter table int_salestable modify s_saleid number primary key;

Table altered.

SQL>
SQL> CREATE INDEX int_sales_index ON int_salestable
2  (s_saledate, s_saleid, s_custid)
3  logging
4  TABLESPACE example;

Index created.

SQL>
SQL>
SQL> BEGIN
2  DBMS_REDEFINITION.CAN_REDEF_TABLE(
3  uname => 'HEMANT',
4  tname => 'SALESTABLE',
5  options_flag => DBMS_REDEFINITION.CONS_USE_PK,
6  part_name => 'SAL03Q1');
7  END;
8  /

PL/SQL procedure successfully completed.

SQL>
SQL> BEGIN
2  DBMS_REDEFINITION.START_REDEF_TABLE(
3  uname => 'HEMANT',
4  orig_table => 'SALESTABLE',
5  int_table => 'INT_SALESTABLE',
6  col_mapping => NULL,
7  options_flag => DBMS_REDEFINITION.CONS_USE_PK,
8  part_name => 'SAL03Q1');
9  END;
10  /

PL/SQL procedure successfully completed.

SQL>
SQL>
SQL> BEGIN
2  DBMS_REDEFINITION.FINISH_REDEF_TABLE(
3  uname => 'HEMANT',
4  orig_table => 'SALESTABLE',
5  int_table => 'INT_SALESTABLE',
6  part_name => 'SAL03Q1');
7  END;
8  /
BEGIN
*
ERROR at line 1:
ORA-42012: error occurred while completing the redefinition
ORA-04020: deadlock detected while trying to lock object HEMANT.INT_SALESTABLE
ORA-06512: at "SYS.DBMS_REDEFINITION", line 78
ORA-06512: at "SYS.DBMS_REDEFINITION", line 1680
ORA-06512: at line 2


SQL>
SQL>
SQL> select partition_name, tablespace_name from user_tab_partitions
2  where table_name = 'SALESTABLE' order by partition_name
3  /

PARTITION_NAME                 TABLESPACE_NAME
------------------------------ ------------------------------
SAL03Q1                        USERS
SAL03Q2                        USERS
SAL03Q3                        USERS
SAL03Q4                        USERS

SQL>
SQL> select partition_name, tablespace_name from user_ind_partitions
2  where index_name = 'SALES_INDEX' order by partition_name
3  /

PARTITION_NAME                 TABLESPACE_NAME
------------------------------ ------------------------------
SAL03Q1                        USERS
SAL03Q2                        USERS
SAL03Q3                        USERS
SAL03Q4                        USERS

SQL>


Then, I retry disabling deferred_segment_creation, so as to force a segment to be created, and use CONS_USE_PK :
SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

SQL> show parameter deferred;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
deferred_segment_creation            boolean     TRUE
SQL> alter session set deferred_segment_creation=FALSE;

Session altered.

SQL>
SQL> drop materialized view int_salestable;

Materialized view dropped.

SQL>
SQL> drop TABLE salestable;

Table dropped.

SQL>
SQL> CREATE TABLE salestable
2  (s_saleid NUMBER,
3  s_saledate DATE,
4  s_custid NUMBER,
5  s_totalprice NUMBER)
6  logging
7  TABLESPACE users
8  PARTITION BY RANGE(s_saledate)
9  (PARTITION sal03q1 VALUES LESS THAN (TO_DATE('01-APR-2003', 'DD-MON-YYYY')),
10  PARTITION sal03q2 VALUES LESS THAN (TO_DATE('01-JUL-2003', 'DD-MON-YYYY')),
11  PARTITION sal03q3 VALUES LESS THAN (TO_DATE('01-OCT-2003', 'DD-MON-YYYY')),
12  PARTITION sal03q4 VALUES LESS THAN (TO_DATE('01-JAN-2004', 'DD-MON-YYYY')));

Table created.

SQL>
SQL>
SQL> alter table salestable modify s_saleid number primary key;

Table altered.

SQL> CREATE INDEX sales_index ON salestable(s_saledate, s_saleid, s_custid) logging LOCAL;

Index created.

SQL>
SQL> select partition_name, tablespace_name from user_tab_partitions
2  where table_name = 'SALESTABLE' order by partition_name
3  /

PARTITION_NAME                 TABLESPACE_NAME
------------------------------ ------------------------------
SAL03Q1                        USERS
SAL03Q2                        USERS
SAL03Q3                        USERS
SAL03Q4                        USERS

SQL>
SQL> select partition_name, tablespace_name from user_ind_partitions
2  where index_name = 'SALES_INDEX' order by partition_name
3  /

PARTITION_NAME                 TABLESPACE_NAME
------------------------------ ------------------------------
SAL03Q1                        USERS
SAL03Q2                        USERS
SAL03Q3                        USERS
SAL03Q4                        USERS

SQL>
SQL>
SQL> drop TABLE int_salestable ;

Table dropped.

SQL>
SQL> CREATE TABLE int_salestable
2  (s_saleid NUMBER,
3  s_saledate DATE,
4  s_custid NUMBER,
5  s_totalprice NUMBER)
6  logging
7  tablespace example;

Table created.

SQL>
SQL> alter table int_salestable modify s_saleid number primary key;

Table altered.

SQL>
SQL> CREATE INDEX int_sales_index ON int_salestable
2  (s_saledate, s_saleid, s_custid)
3  logging
4  TABLESPACE example;

Index created.

SQL>
SQL>
SQL> BEGIN
2  DBMS_REDEFINITION.CAN_REDEF_TABLE(
3  uname => 'HEMANT',
4  tname => 'SALESTABLE',
5  options_flag => DBMS_REDEFINITION.CONS_USE_PK,
6  part_name => 'SAL03Q1');
7  END;
8  /

PL/SQL procedure successfully completed.

SQL>
SQL> BEGIN
2  DBMS_REDEFINITION.START_REDEF_TABLE(
3  uname => 'HEMANT',
4  orig_table => 'SALESTABLE',
5  int_table => 'INT_SALESTABLE',
6  col_mapping => NULL,
7  options_flag => DBMS_REDEFINITION.CONS_USE_PK,
8  part_name => 'SAL03Q1');
9  END;
10  /

PL/SQL procedure successfully completed.

SQL>
SQL>
SQL> BEGIN
2  DBMS_REDEFINITION.FINISH_REDEF_TABLE(
3  uname => 'HEMANT',
4  orig_table => 'SALESTABLE',
5  int_table => 'INT_SALESTABLE',
6  part_name => 'SAL03Q1');
7  END;
8  /

PL/SQL procedure successfully completed.

SQL>
SQL>
SQL> select partition_name, tablespace_name from user_tab_partitions
2  where table_name = 'SALESTABLE' order by partition_name
3  /

PARTITION_NAME                 TABLESPACE_NAME
------------------------------ ------------------------------
SAL03Q1                        EXAMPLE
SAL03Q2                        USERS
SAL03Q3                        USERS
SAL03Q4                        USERS

SQL>
SQL> select partition_name, tablespace_name from user_ind_partitions
2  where index_name = 'SALES_INDEX' order by partition_name
3  /

PARTITION_NAME                 TABLESPACE_NAME
------------------------------ ------------------------------
SAL03Q1                        EXAMPLE
SAL03Q2                        USERS
SAL03Q3                        USERS
SAL03Q4                        USERS

SQL>


This succeeds as expected.

Conclusion : If a partition that is to be "exchanged" via DBMS_REDEFINITION is empty and deferred_segment_creation is enabled in 11.2, an ORA-4020 Object Deadlock error occurs when attempting to FINISH_REDEF.

Question : Is this a valid conclusion ?


ANSWER :  See MyOracleSupport Documents :
"Bug 8891929 - ORA-42012 / ORA-4020 during DBMS_REDEFINITION (Doc ID 8891929.8)"
and
"Bug 11775474 - DBMS_REDEFINITION does not use deferred segment creation in 11.2.0.2 (Doc ID 11775474.8)"
.
.
.





1 comment:

Anonymous said...

Hi,
I have hit deadlock on 1 from 10th redefinition and my SR is open with Oracle dev for 10th months. Maybe I should point them to your findings. :)
Marcin

Aggregated by orafaq.com

Aggregated by orafaq.com
This blog is being aggregated by orafaq.com