Hi ,
Could you please have a look at below reference partition scenario Rowcount of order_items is 25 millions:
I need to change the partition technique of child table i.r. order_items to DAILY-RANGE INTERVAL partition.
Table dont have primary key so i am using cons_use_rowid to redfine.
however i annot able to finish redefintion becasue of below error
------------------------
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
------------------------
Could you please assist here and if there is any alternative to do this activity please help.
-- 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;