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).
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)"
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)
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.
Well... the next thing is to test without deferred_segment_creation. This should succeed.
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 ?
Then, I retry disabling deferred_segment_creation, so as to force a segment to be created, and use CONS_USE_PK :
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.
(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:
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
Post a Comment