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 :
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 ?

.
.
.





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