CREATE SEQUENCE SZR.CUSTOMER_ORDERS_SEQ
  START WITH 1
  INCREMENT BY 1
  NOCACHE
  NOCYCLE;

CREATE TABLE SZR.CUSTOMER_ORDERS (
    ORDER_ID        NUMBER        PRIMARY KEY, 
    ORDER_DATE      DATE          NOT NULL,
    CUSTOMER_ID     NUMBER        NOT NULL,
    CUSTOMER_NAME   VARCHAR2(120),
    PRODUCT_CODE    VARCHAR2(50),
    QUANTITY        NUMBER(8),
    UNIT_PRICE      NUMBER(12,2),
    TOTAL_AMOUNT    NUMBER(14,2)  GENERATED ALWAYS AS (QUANTITY * UNIT_PRICE) VIRTUAL,
    ORDER_STATUS    VARCHAR2(20)  DEFAULT 'PENDING',
    CREATED_BY      VARCHAR2(60)
) TABLESPACE USERS;

-- 創(chuàng)建 Trigger 實(shí)現(xiàn)自增主鍵
CREATE OR REPLACE TRIGGER SZR.CUSTOMER_ORDERS_TRG
BEFORE INSERT ON SZR.CUSTOMER_ORDERS
FOR EACH ROW
BEGIN
    IF :NEW.ORDER_ID IS NULL THEN
        SELECT SZR.CUSTOMER_ORDERS_SEQ.NEXTVAL
          INTO :NEW.ORDER_ID
          FROM DUAL;
    END IF;
END;
/

INSERT INTO SZR.CUSTOMER_ORDERS (ORDER_DATE, CUSTOMER_ID, CUSTOMER_NAME, PRODUCT_CODE, QUANTITY, UNIT_PRICE, ORDER_STATUS, CREATED_BY)
SELECT 
    ADD_MONTHS(TO_DATE('2020-03-10','YYYY-MM-DD'), LEVEL*1) AS ORDER_DATE,
    5000 + MOD(LEVEL, 300) AS CUSTOMER_ID,
    'Customer_' || TO_CHAR(5000 + MOD(LEVEL, 300)) AS CUSTOMER_NAME,
    'PROD_' || LPAD(MOD(LEVEL, 99)+1, 3, '0') AS PRODUCT_CODE,
    MOD(LEVEL, 50) + 1 AS QUANTITY,
    ROUND(DBMS_RANDOM.VALUE(10, 500), 2) AS UNIT_PRICE,
    CASE MOD(LEVEL, 5) 
        WHEN 0 THEN 'COMPLETED' 
        WHEN 1 THEN 'SHIPPED' 
        WHEN 2 THEN 'PENDING' 
        ELSE 'CANCELLED' 
    END AS ORDER_STATUS,
    'User_' || MOD(LEVEL, 10) AS CREATED_BY
FROM DUAL
CONNECT BY LEVEL <= 1200;

COMMIT;

-- 查看原表記錄數(shù)和數(shù)據(jù)分布
SELECT COUNT(*) AS total_rows FROM SZR.CUSTOMER_ORDERS;

SELECT MIN(ORDER_DATE), MAX(ORDER_DATE) FROM SZR.CUSTOMER_ORDERS;

SELECT TRUNC(ORDER_DATE,'MM'), COUNT(*) FROM SZR.CUSTOMER_ORDERS GROUP BY TRUNC(ORDER_DATE,'MM') ORDER BY 1;

set serveroutput on;
BEGIN
  DBMS_REDEFINITION.CAN_REDEF_TABLE(
    uname        => 'SZR',
    tname        => 'CUSTOMER_ORDERS',
    options_flag => DBMS_REDEFINITION.CONS_USE_PK
  );
  DBMS_OUTPUT.PUT_LINE('表支持重定義（CONS_USE_PK）：成功');
END;
/

CREATE TABLE SZR.CUSTOMER_ORDERS_INT
TABLESPACE USERS
PARTITION BY RANGE (ORDER_DATE)
INTERVAL (NUMTOYMINTERVAL(1, 'MONTH'))    
(
  PARTITION P_BEFORE_2022 VALUES LESS THAN (TO_DATE('2022-01-01','YYYY-MM-DD'))
)
AS 
SELECT * FROM SZR.CUSTOMER_ORDERS 
WHERE 1=0;

-- 查看臨時(shí)表當(dāng)前分區(qū)
SELECT PARTITION_NAME, HIGH_VALUE 
FROM USER_TAB_PARTITIONS 
WHERE TABLE_NAME = 'CUSTOMER_ORDERS_INT';

set serveroutput on;
BEGIN
  DBMS_REDEFINITION.START_REDEF_TABLE(
    uname        => 'SZR',
    orig_table   => 'CUSTOMER_ORDERS',
    int_table    => 'CUSTOMER_ORDERS_INT',
    options_flag => DBMS_REDEFINITION.CONS_USE_PK
  );
  DBMS_OUTPUT.PUT_LINE('START_REDEF_TABLE 執(zhí)行完成');
END;
/

BEGIN
  DBMS_REDEFINITION.ABORT_REDEF_TABLE(
    uname      => 'SZR',
    orig_table => 'CUSTOMER_ORDERS',
    int_table  => 'CUSTOMER_ORDERS_INT'
  );
END;
/

set serveroutput on;
DECLARE
  l_num_errors  PLS_INTEGER;
BEGIN
  DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(
    uname            => 'SZR',
    orig_table       => 'CUSTOMER_ORDERS',
    int_table        => 'CUSTOMER_ORDERS_INT',
    copy_indexes     => 0,                    -- 不自動(dòng)復(fù)制索引（主鍵索引已存在）
    copy_triggers    => TRUE,
    copy_constraints => FALSE,                -- 關(guān)鍵：關(guān)閉約束復(fù)制，避免 NOT NULL 沖突
    copy_privileges  => TRUE,
    ignore_errors    => TRUE,
    num_errors       => l_num_errors,
    copy_statistics  => FALSE
  );
  DBMS_OUTPUT.PUT_LINE('COPY_TABLE_DEPENDENTS 執(zhí)行完成，錯(cuò)誤數(shù): ' || l_num_errors);
END;
/

BEGIN
  DBMS_REDEFINITION.SYNC_INTERIM_TABLE(
    uname      => 'SZR',
    orig_table => 'CUSTOMER_ORDERS',
    int_table  => 'CUSTOMER_ORDERS_INT'
  );
  DBMS_OUTPUT.PUT_LINE('SYNC_INTERIM_TABLE 執(zhí)行完成');
END;
/

BEGIN
  DBMS_REDEFINITION.FINISH_REDEF_TABLE(
    uname      => 'SZR',
    orig_table => 'CUSTOMER_ORDERS',
    int_table  => 'CUSTOMER_ORDERS_INT'
  );
  DBMS_OUTPUT.PUT_LINE('FINISH_REDEF_TABLE 執(zhí)行完成，表已成功轉(zhuǎn)換為分區(qū)表');
END;
/

SELECT TABLE_NAME, PARTITIONING_TYPE, INTERVAL 
FROM USER_PART_TABLES 
WHERE TABLE_NAME = 'CUSTOMER_ORDERS';

SELECT PARTITION_NAME, HIGH_VALUE, NUM_ROWS 
FROM USER_TAB_PARTITIONS 
WHERE TABLE_NAME = 'CUSTOMER_ORDERS' 
ORDER BY PARTITION_POSITION;

SELECT COUNT(*) FROM SZR.CUSTOMER_ORDERS;

-- 刪除臨時(shí)表
DROP TABLE SZR.CUSTOMER_ORDERS_INT PURGE;

-- 收集統(tǒng)計(jì)信息
BEGIN
  DBMS_STATS.GATHER_TABLE_STATS('SZR', 'CUSTOMER_ORDERS', cascade => TRUE);
END;
/