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

Top 50 Oracle SQL Blogs 2016

Top 50 Oracle SQL Blogs 2016
Top 50 Oracle SQL Blogs 2016