Oracle 无法在引用分区表上完成finish_redef_table

嗨，

您能否看一下下面的参考分区方案order_items的Rowcount为2500万:
我需要更改子表I.r.的分区技术。order_items到每日范围间隔分区。
表没有主键，所以我使用cons_use_rowid进行redfine。
然而，由于以下错误，我无法完成重新定义
------------------------

ORA-02448: constraint does not exist
ORA-06512: at "SYS.DBMS_REDEFINITION", line 105
ORA-06512: at "SYS.DBMS_REDEFINITION", line 3520
ORA-06512: at line 51
02448. 00000 -  "constraint does not exist"
*Cause:    The named constraint does not exist
*Action:   Stop trying to do something with a nonexistant constraint

------------------------

您能否在这里提供帮助，如果有其他方法可以进行此活动，请提供帮助。

-- sample table and data
drop table order_items_tmp purge;
drop table order_items purge;
drop table orders purge;

CREATE TABLE orders (
  order_id    NUMBER PRIMARY KEY,
  order_date  DATE NOT NULL,
  customer_id NUMBER NOT NULL,
  shipper_id  NUMBER)
PARTITION BY RANGE (order_date) (
  PARTITION y1 VALUES LESS THAN (TO_DATE('01-JAN-2006', 'DD-MON-YYYY')),
  PARTITION y2 VALUES LESS THAN (TO_DATE('01-JAN-2007', 'DD-MON-YYYY')),
  PARTITION y3 VALUES LESS THAN (TO_DATE('01-JAN-2008', 'DD-MON-YYYY')));

CREATE TABLE order_items (
  order_id    NUMBER NOT NULL,
  order_date  date,
  price       NUMBER,
  quantity    NUMBER,
  CONSTRAINT order_items_fk FOREIGN KEY (order_id) REFERENCES orders (order_id))
PARTITION BY REFERENCE (order_items_fk);

insert into orders select level, TO_DATE('01-JAN-2007', 'DD-MON-YYYY'), level, level from dual connect by level <=100;
insert into order_items select order_id, TO_DATE('01-JAN-2020', 'DD-MON-YYYY'), order_id, order_id from orders;
update order_items set order_date = order_date+rownum; -- update for daily dates
commit;


-- Start Redefinition
WHENEVER SQLERROR EXIT SQL.SQLCODE


exec dbms_metadata.set_transform_param (dbms_metadata.session_transform,'STORAGE', false);
exec dbms_metadata.set_transform_param (dbms_metadata.session_transform,'TABLESPACE', false);
exec dbms_metadata.set_transform_param (dbms_metadata.session_transform,'SEGMENT_ATTRIBUTES', false);
exec dbms_metadata.set_transform_param (dbms_metadata.session_transform,'CONSTRAINTS', false);
exec dbms_metadata.set_transform_param (dbms_metadata.session_transform,'REF_CONSTRAINTS', false);
exec dbms_metadata.set_transform_param (dbms_metadata.session_transform,'PARTITIONING', false);
exec dbms_metadata.set_transform_param (dbms_metadata.session_transform,'CONSTRAINTS_AS_ALTER', false);
exec dbms_metadata.set_transform_param (dbms_metadata.session_transform,'SQLTERMINATOR', false);
exec dbms_metadata.set_transform_param (dbms_metadata.session_transform,'PRETTY', true);

SET SERVEROUTPUT ON SIZE UNLIMITED;

DECLARE
  l_tab_sql      varchar2(32767);
  l_part_sql     varchar2(32767) := q'[PARTITION BY RANGE (order_date) INTERVAL (NUMTODSINTERVAL(1,'DAY')) (PARTITION p_default values less than (TO_DATE('01-JAN-1990', 'DD-MON-YYYY')))]';
  l_errors  NUMBER;
  l_schema_owner varchar2(30) := USER;
  l_orig_table   varchar2(30) := 'ORDER_ITEMS';
  l_int_table    varchar2(30) := 'ORDER_ITEMS_TMP';
BEGIN
  l_tab_sql := dbms_metadata.get_ddl('TABLE',l_orig_table,l_schema_owner);
  l_tab_sql := regexp_replace(l_tab_sql,'(\s+)(CONSTRAINT "?.?")(\w+)?"','\1'||'CONSTRAINT "'||'\3_BKP'||'"',1,1,'im');
  l_tab_sql := replace(l_tab_sql,l_orig_table,l_int_table);
  l_tab_sql := l_tab_sql||l_part_sql;
  
  DBMS_OUTPUT.put_line('Create Interm table => ' || l_tab_sql);
  
  EXECUTE IMMEDIATE l_tab_sql;
  DBMS_OUTPUT.put_line('Interm table created..');
  
  -- drop constraint from int table before starting redefinition..
  begin
    for r in (select table_name, constraint_name from user_constraints where table_name = l_int_table)
    loop
       execute immediate 'alter table '|| r.table_name||' drop constraint '|| r.constraint_name;
    DBMS_OUTPUT.put_line('Constraint ' ||r.constraint_name||' dropped..');
    end loop;
  end;  
  
  DBMS_REDEFINITION.can_redef_table(uname => l_schema_owner, tname => l_orig_table, options_flag => dbms_redefinition.cons_use_rowid);
  
  DBMS_REDEFINITION.start_redef_table(uname => l_schema_owner , orig_table => l_orig_table, int_table  => l_int_table, options_flag => dbms_redefinition.cons_use_rowid);
  
  DBMS_REDEFINITION.sync_interim_table(uname => l_schema_owner , orig_table => l_orig_table, int_table  => l_int_table);
  
  DBMS_REDEFINITION.copy_table_dependents(
    uname            => l_schema_owner,
    orig_table       => l_orig_table,
    int_table        => l_int_table,
    copy_indexes     => DBMS_REDEFINITION.cons_orig_params,
    copy_triggers    => TRUE,
    copy_constraints => TRUE,
    copy_privileges  => TRUE,
    ignore_errors    => FALSE,
    num_errors       => l_errors,
    copy_statistics  => FALSE,
    copy_mvlog       => FALSE);
    
  DBMS_OUTPUT.put_line('Errors=' || l_errors);

  DBMS_STATS.gather_table_stats(l_schema_owner, l_int_table, cascade => TRUE);

  DBMS_REDEFINITION.finish_redef_table(uname => l_schema_owner , orig_table => l_orig_table, int_table  => l_int_table);
  EXECUTE IMMEDIATE 'DROP TABLE '||l_int_table||' CASCADE CONSTRAINTS';
  DBMS_OUTPUT.put_line('Interm table dropped..');
  
END;

我不知道为什么你得到的约束不存在错误。当我运行这个我得到:

ORA-23549: table "CHRIS"."ORDER_ITEMS" involved in reference partitioning

正如错误所暗示的那样，这意味着您不能在引用分区表上使用DBMS_redefinition!

我相信您将必须手动进行此转换。