CREATE TABLE hr.sales
(
  customer_id NUMBER,
  sell_id     NUMBER,
  product_id  NUMBER,
  sold_date   DATE,
  price       NUMBER,
  quantity    NUMBER,
  state       VARCHAR(5)
);

CREATE UNIQUE INDEX hr.customer_id_u ON hr.sales(customer_id);

INSERT INTO hr.sales 
  VALUES(1, 10, 15, TO_DATE('20110110', 'YYYYMMDD'), 100, 5, 'CA');

INSERT INTO hr.sales 
  VALUES(2, 15, 10, TO_DATE('20110116', 'YYYYMMDD'), 25, 2, 'OR');

INSERT INTO hr.sales 
  VALUES(3, 20, 5, TO_DATE('20110210', 'YYYYMMDD'), 10, 3, 'WA');

ALTER TABLE hr.sales READ ONLY;

CREATE TABLE hr.sales_list_partition 
  PARTITION BY LIST (state)
    (PARTITION west VALUES ('CA','OR','WA'),
     PARTITION default_partition VALUES (DEFAULT)) 
AS
  SELECT * FROM hr.sales;

ALTER TABLE hr.sales
  RENAME TO sales_old;

ALTER TABLE hr.sales_list_partition
  RENAME TO sales;

ALTER TABLE hr.sales_old READ WRITE;

CREATE UNIQUE INDEX new_customer_id_u ON hr.sales(customer_id);

CREATE TABLE ht.sales_interim
(
  customer_id NUMBER,
  sell_id     NUMBER,
  product_id  NUMBER,
  sold_date   DATE,
  price       NUMBER,
  quantity    NUMBER,
  state       VARCHAR(5)
)
PARTITION BY RANGE (sold_date)
  (PARTITION P_JANUARY VALUES LESS THAN (TO_DATE('20110131', 'YYYYMMDD')),
    PARTITION P_FEBRUARY VALUES LESS THAN (TO_DATE('20110228', 'YYYYMMDD')),
    PARTITION max_value VALUES LESS THAN (MAXVALUE)
  );

BEGIN
  DBMS_REDEFINITION.START_REDEF_TABLE
   (uname          => 'HR',
    orig_table     => 'SALES_OLD',
    int_table      => 'SALES_INTERIM',
    options_flag   => dbms_redefinition.cons_use_rowid);
END;
/

VARIABLE num_errors NUMBER;
BEGIN
  DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS
   (uname        => 'HR',
    orig_table   => 'SALES_OLD',
    int_table    => 'SALES_INTERIM',
    num_errors   => :num_errors);
END;
/

PRINT num_errors;

BEGIN
  DBMS_REDEFINITION.SYNC_INTERIM_TABLE
   (uname       => 'HR',
    orig_table  => 'SALES_OLD',
    int_table   => 'SALES_INTERIM');
END;
/

BEGIN
  DBMS_REDEFINITION.FINISH_REDEF_TABLE
   (uname       => 'HR',
    orig_table  => 'SALES_OLD',
    int_table   => 'SALES_INTERIM');
END;
/

DROP TABLE hr.sales_old PURGE;
DROP TABLE hr.sales_interim PURGE;
DROP TABLE hr.sales PURGE;