การแบ่งตารางออนไลน์ใน Oracle 10g ด้วย DBMS

ตั้งแต่ Oracle 10g เป็นต้นมา สามารถแบ่งตาราง (partition table) แบบออนไลน์ได้โดยไม่มี downtime ของแอปพลิเคชัน โดยใช้แพ็กเกจ DBMS_REDEFINITION

ขั้นตอนที่ 1: สำรองข้อมูลตารางที่ยังไม่ partition

รันโค้ดต่อไปนี้เพื่อสร้าง full export backup ของตาราง TABLEA:

expdp "/ as sysdba" directory=EXPDP_DIR dumpfile=tableA_UNPAR.dmp logfile=tableA_UNPAR.log TABLES=TEST.TABLEA
expdp "/ as sysdba" directory=EXPDP_DIR dumpfile=tableA_metaunpar.dmp logfile=tableA_metaunpar.log TABLES=TEST.TABLEA content=metadata_only

ขั้นตอนที่ 2: ตรวจสอบออบเจ็กต์ฐานข้อมูล

ออบเจ็กต์ที่ขึ้นอยู่กับตาราง (Dependent objects) ที่จะถูกลบเมื่อตารางถูกลบ:

CONSTRAINT (Constraints) D
INDEX (Indexes) D
MATERIALIZED_VIEW_LOG D
OBJECT_GRANT D
TRIGGER D

รันคำสั่ง SQL และบันทึกผลใน spool file เช่น cons_trig_indx.txt:

set LINESIZE 500
set PAGESIZE 1000
SQL> spool cons_trig_indx.txt
SQL> select name, type, owner from all_dependencies where referenced_owner = 'TEST' and referenced_name = 'TABLEA';
SQL> select OWNER, INDEX_NAME, TABLE_OWNER, TABLE_NAME, STATUS, TABLESPACE_NAME from dba_indexes where TABLE_OWNER='TEST' and TABLE_NAME='TABLEA';
SQL> select STATUS, OBJECT_TYPE, OBJECT_NAME from dba_objects where OWNER='TEST' and OBJECT_TYPE = 'TRIGGER' and STATUS='INVALID';
SQL> select CONSTRAINT_NAME, CONSTRAINT_TYPE from dba_constraints where TABLE_NAME='TABLEA' and owner='TEST';
SQL> spool off

ขั้นตอนที่ 3: จับ DDL ของ TABLEA

set echo off
set feedback off
set linesize 160
set long 2000000
set pagesize 0
set trims on
column txt format a150 word_wrapped
SQL> spool DEF_TABLEA.sql
SQL> select DBMS_METADATA.GET_DDL('TABLE','TABLEA','TEST') txt FROM dual;
SQL> spool off

ขั้นตอนที่ 4: คัดลอกสคริปต์ DDL

cp DEF_TABLEA.sql DEF_TABLEA_PAR.sql

ขั้นตอนที่ 5: ตรวจสอบวันที่ในตาราง Non-partition

SQL> select * from (select DT from TEST.TABLEA where rownum <15 order by DT DESC);

ขั้นตอนที่ 6: แก้ไขไฟล์ DEF_TABLEA_PAR.sql

เปลี่ยน TABLEA ทุกที่เป็น TABLEA_PAR
ลบ constraints ทั้งหมด เช่น NOT NULL
เพิ่มคำสั่งสร้างตารางใน tablespace ใหม่: TABLESPACE "TABLEA_TBL_PAR" LOGGING

เพิ่ม partition definition ตามวันที่จากขั้นตอนที่ 5:

PARTITION BY RANGE(DT) interval (numtoyminterval(1,'MONTH'))
(partition TABLEA_2004 values less than (to_date('01/01/2005','DD/MM/YYYY')),
 partition TABLEA_2005 values less than (to_date('01/01/2006','DD/MM/YYYY')));

ขั้นตอนที่ 7: สร้างตาราง Partition

SQL> spool DEF_TABLEA_PAR.outp.txt
SQL> @DEF_TABLEA_PAR.sql
Table Created.
SQL> spool off

ขั้นตอนที่ 8: ตรวจสอบตาราง Partition

SQL> spool verify_partition.txt
SQL> select partition_name from DBA_tab_partitions where table_name ='TABLEA_PAR' and table_owner = 'TEST';
SQL> spool off
PARTITION_NAME
-----------------
TABLEA_2004
TABLEA_2005

ขั้นตอนที่ 9: รวบรวมสถิติตาราง Non-partition

SQL> SPOOL gather_stats.txt
SQL> exec dbms_stats.gather_table_stats ('TEST', 'TABLEA',cascade => TRUE);
SQL> spool off

ขั้นตอนที่ 10: ตรวจสอบความเป็นไปได้ของ Redefinition

หมายเหตุ: ตารางต้นทาง (non-partitioned) ไม่จำเป็นต้องมี primary key ก่อนใช้ redefinition package

SQL> spool check_the_redefinition.txt
SQL> EXEC DBMS_Redefinition.can_redef_table ('TEST', 'TABLEA');
SQL> spool off

ขั้นตอนที่ 11: เริ่ม Redefinition

หากไม่มีข้อผิดพลาดใน check_the_redefinition.txt ให้เริ่ม redefinition:

SQL> spool start_redef_table.txt
SQL> begin dbms_redefinition.start_redef_table (
    uname => 'TEST',
    orig_table => 'TABLEA',
    int_table => 'TABLEA_PAR');
end;
/
SQL> spool off

ขั้นตอนที่ 12: ตรวจสอบ Tablespace Errors ระหว่าง Redefinition

อาจเกิดข้อผิดพลาด tablespace เช่น ORA-01688 หรือ ORA-14400 หากเกิดขึ้น:

หยุด redefinition: dbms_redefinition.abort_redef_table
Drop partition table และ materialized view
เพิ่มขนาด tablespace (เช่น TABLEA_TBL)
รันขั้นตอนที่ 11 ใหม่

ขั้นตอนที่ 13: ตรวจสอบข้อผิดพลาด Redefinition

SQL> spool copy_table_dependents.txt
SQL> SET SERVEROUTPUT ON
DECLARE l_num_errors PLS_INTEGER;
BEGIN
    DBMS_REDEFINITION.copy_table_dependents(
        uname => 'TEST',
        orig_table => 'TABLEA',
        int_table => 'TABLEA_PAR',
        copy_indexes => DBMS_REDEFINITION.cons_orig_params,
        num_errors => l_num_errors);
    DBMS_OUTPUT.put_line('l_num_errors=' || l_num_errors);
END;
/
SQL> spool off

หากสำเร็จ จะเห็น l_num_errors=0

ขั้นตอนที่ 14: (ตัวเลือก) Resynchronize ตาราง Partition

SQL> spool sync_interim_table.txt
SQL> BEGIN DBMS_REDEFINITION.sync_interim_table (
    uname => 'TEST',
    orig_table => 'TABLEA',
    int_table => 'TABLEA_PAR');
END;
/
SQL> spool off

ขั้นตอนที่ 15: รวบรวมสถิติตาราง Partition

SQL> spool gather_statistics_par.txt
SQL> exec dbms_stats.gather_table_stats ('TEST', 'TABLEA_PAR',cascade => TRUE);
SQL> spool off

ขั้นตอนที่ 16: สร้างสคริปต์ Constraint

SQL> spool constraint_enable_validate.txt
SET LINESIZE 500 SET PAGESIZE 1000
SQL> select 'alter table' ||' '||OWNER||'.'||TABLE_NAME||' enable validate constraint'||' '||CONSTRAINT_NAME||';'
from dba_constraints where TABLE_NAME = 'TABLEA_PAR' and OWNER='TEST';
SQL> spool off

ขั้นตอนที่ 17: Enable Validate Constraint

SQL> spool constraint_enable_execute.outp.txt
SQL> @constraint_enable.sql
alter table TEST.TABLEA_PAR enable validate constraint TMP$$_SYS_C002004601;
alter table TEST.TABLEA_PAR enable validate constraint TMP$$_SYS_C002004602;
alter table TEST.TABLEA_PAR enable validate constraint TMP$$_SYS_C002004603;
alter table TEST.TABLEA_PAR enable validate constraint TMP$$_IDX_PK;
alter table TEST.TABLEA_PAR enable validate constraint TMP$$_FK01;
SQL> spool off

ขั้นตอนที่ 18-25: เปรียบเทียบ, Rename, Verify, Rebuild Indexes, Drop ตารางเก่า

ดำเนินการเปรียบเทียบตารางเก่า-ใหม่, rename tables ด้วย FINISH_REDEF_TABLE, ตรวจสอบจำนวนแถว, verify partition success, ตรวจสอบออบเจ็กต์ใหม่, rebuild indexes บน tablespace ใหม่, validate indexes, และสุดท้าย drop ตาราง non-partition เดิม (TEST.TABLEA_PAR)

สรุป

ขั้นตอนข้างต้นใช้ interim table (TEST.TABLEA_PAR) เพื่อ partition ตาราง TEST.TABLEA ให้เป็น range interval table โดยไม่มี application downtime

การแบ่งตารางออนไลน์ใน Oracle 10g ด้วย DBMS_REDEFINITION: ไม่มี Downtime