พาร์ติชันตารางออนไลน์ของ Oracle โดยใช้แพ็คเกจ DBMS_REDEFINITION

เริ่มต้นด้วย Oracle® 10g คุณสามารถแบ่งพาร์ติชันตารางออนไลน์โดยไม่ต้องหยุดทำงานของแอปพลิเคชันโดยใช้แพ็คเกจ DBMS_REDEFINITION

ใช้ขั้นตอนต่อไปนี้เพื่อเปลี่ยนตารางที่ไม่ใช่พาร์ติชันเป็นตารางพาร์ติชันโดยใช้ DBMS_REDEFINITION ตัวอย่างนี้เปลี่ยนตารางที่ไม่ใช่พาร์ติชั่น TABLEA เป็นตารางพาร์ติชั่นช่วงช่วง

ขั้นตอนที่ 1:สำรองข้อมูลตารางที่ไม่แบ่งพาร์ติชัน

เรียกใช้รหัสต่อไปนี้เพื่อสร้างข้อมูลสำรองการส่งออกทั้งหมดของตาราง 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:ตรวจสอบวัตถุฐานข้อมูล

ออบเจ็กต์ฐานข้อมูลที่ขึ้นต่อกัน (D) ต่อไปนี้สามารถลบได้เมื่อตารางถูกดร็อป:

• CONSTRAINT (ข้อจำกัด) D

• ดัชนี (ดัชนี) D

• MATERIALIZED_VIEW_LOG (บันทึกการดูวัสดุ) D

• OBJECT_GRANT (การมอบวัตถุ) D

• ทริกเกอร์ (ทริกเกอร์) D

เรียกใช้คำสั่ง SQL ต่อไปนี้และบันทึกเอาต์พุตในสปูลไฟล์ เช่น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';

NAME                TYPE              OWNER
--------------      --------------    -------
PROC_TABLEA         PROCEDURE         TEST
TABLEA_TRIGG        TRIGGER           TEST
PKG_TABLEA          PACKAGE BODY      TEST


SQL> select OWNER, INDEX_NAME, TABLE_OWNER, TABLE_NAME, STATUS, TABLESPACE_NAME
from dba_indexes where TABLE_OWNER='TEST' and TABLE_NAME='TABLEA';

OWNER   INDEX_NAME       TABLE_OWNER  TABLE_NAME   STATUS   TABLESPACE_NAME
---------------------------------------------------------------------------
TEST    TABLEA_IDX_ID01    TEST        TABLEA      VALID    TABLEA_TBL
TEST    TABLEA_IDX_ID04    TEST        TABLEA      VALID    TABLEA_TBL
TEST    TABLEA_IDX_PK      TEST        TABLEA      VALID    TABLEA_TBL


SQL> select STATUS, OBJECT_TYPE, OBJECT_NAME  from dba_objects
where OWNER='TEST' and OBJECT_TYPE = 'TRIGGER' and STATUS='INVALID';

no rows selected

SQL> select CONSTRAINT_NAME, CONSTRAINT_TYPE from dba_constraints
where TABLE_NAME='TABLEA' and owner='TEST';
SQL> spool off

CONSTRAINT_NAME     C
------------------  -----
SYS_C002004601      C
SYS_C002004602      C
SYS_C002004603      C
IDX_PK              P
FK01                R

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

รันคำสั่งต่อไปนี้เพื่อดักจับ data definition language (DDL) ของTABLEA และบันทึกสคริปต์ในสพูลไฟล์ DEF_TABLEA.sql ก่อนที่คุณจะสร้างตารางพาร์ติชั่น:

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

เรียกใช้คำสั่งต่อไปนี้เพื่อคัดลอกสคริปต์ DDL ที่คุณสร้างในขั้นตอนที่ 3

cp DEF_TABLEA.sql DEF_TABLEA_PAR.sql

ขั้นตอนที่ 5:ตรวจสอบวันที่ในตารางที่ไม่ใช่พาร์ติชัน

เรียกใช้คำสั่งต่อไปนี้เพื่อค้นหาวันที่ใน TABLEA:

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

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

แก้ไข DEF_TABLEA_PAR.sql เพื่อทำการเปลี่ยนแปลงดังต่อไปนี้:

• เปลี่ยนการเกิดขึ้นทั้งหมดของ TABLEA ถึง TABLEA_PAR .

• ลบข้อจำกัดทั้งหมด เช่น NOT NULL หรือข้อจำกัดอื่นๆ

• แทรกคำสั่งต่อไปนี้เพื่อสร้างตารางในพื้นที่ตารางใหม่:

    TABLESPACE "TABLEA_TBL_PAR" LOGGING
  

• แทรกคำสั่งต่อไปนี้เพื่อเพิ่มคำจำกัดความของพาร์ติชันตามวันที่ที่ระบุในขั้นตอนที่ 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')));
  

DEF_TABLEA_PAR.sql ไฟล์ควรมีลักษณะดังนี้:

CREATE TABLE "TEST"."TABLEA_PAR"
(    "ID" NUMBER(6,0),
     "CEID" NUMBER(6,0),
     "DT" DATE,
     "AMT" NUMBER(14,4),
     "RET" NUMBER(14,4),
     "CNT" NUMBER(4,0),
     "VCNT" NUMBER(4,0),
     "EXEDT" DATE,
     "LASTUPDBY" VARCHAR2(15),
     "VENUM" NUMBER(6,0),
     "LASTUPDDT" TIMESTAMP (6))
TABLESPACE "TABLEA_TBL_PAR" LOGGING
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:สร้างตารางพาร์ทิชัน

สร้างตารางพาร์ติชั่นโดยทำตามขั้นตอนต่อไปนี้เพื่อรัน DEF_TABLEA_PAR.sql สคริปต์:

SQL> spool DEF_TABLEA_PAR.outp.txt
SQL> @DEF_TABLEA_PAR.sql

Table Created.

SQL> spool off

ขั้นตอนที่ 8:ตรวจสอบตารางพาร์ทิชัน

รันคำสั่งต่อไปนี้เพื่อตรวจสอบตารางพาร์ติชั่นและส่งคืนพาร์ติชั่นที่กำหนด:

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:รวบรวมสถิติจากตารางที่ไม่ใช่พาร์ติชัน

รันคำสั่งต่อไปนี้เพื่อรวบรวมสถิติบนตารางที่ไม่ใช่พาร์ติชันและบันทึกลงในสปูลไฟล์

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

ขั้นตอนที่ 10:ตรวจสอบความเป็นไปได้ในการนิยามใหม่

หมายเหตุ :ตารางต้นทาง (ไม่ได้แบ่งพาร์ติชัน) ไม่ต้องการคีย์หลักก่อนที่คุณจะใช้แพ็คเกจการกำหนดนิยามใหม่

รันคำสั่งต่อไปนี้เพื่อดูว่าสามารถกำหนดนิยามใหม่ได้หรือไม่ และบันทึกผลลัพธ์ลงในสพูลไฟล์:

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

ขั้นตอนที่ 11:เริ่มนิยามใหม่

หากไม่มีข้อผิดพลาดอยู่ใน check_the_redefinition.txt ให้เริ่มนิยามใหม่โดยใช้คำสั่งที่ใช้เวลานานต่อไปนี้:

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:ดูข้อผิดพลาดของพื้นที่ตารางในระหว่างการกำหนดใหม่

การดำเนินการกำหนดนิยามใหม่ในขั้นตอนที่ 11 อาจส่งผลให้มีการแจ้งเตือนพื้นที่ตารางดังตัวอย่างต่อไปนี้:

ERROR at line 1:
ORA-12008: error in materialized view refresh path
ORA-01688: unable to extend table TEST.TABLEA_PAR
partition SYS_P42 by 1024 in tablespace TABLEA_TBL
ORA-06512: at "SYS.DBMS_REDEFINITION", line 52
ORA-06512: at "SYS.DBMS_REDEFINITION", line 1646
ORA-06512: at line 2

ERROR at line 1:
ORA-12008: error in materialized view refresh path
ORA-14400: inserted partition key does not map to any partition
ORA-06512: at "SYS.DBMS_REDEFINITION", line 52
ORA-06512: at "SYS.DBMS_REDEFINITION", line 1646
ORA-06512: at line 2

หากคุณเห็นข้อผิดพลาดของพื้นที่ตารางคล้ายกับตัวอย่างก่อนหน้านี้ คุณควรทำตามขั้นตอนต่อไปนี้:

1. เรียกใช้คำสั่งต่อไปนี้เพื่อหยุดกระบวนการกำหนดนิยามใหม่

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

2. วางตารางพาร์ทิชันและมุมมองที่เป็นรูปธรรม

3. เพิ่มขนาดของพื้นที่ตาราง ในตัวอย่างนี้ คุณควรเพิ่มขนาดของพื้นที่ตาราง TABLEA_TBL

4. เรียกใช้ขั้นตอนที่ 11 อีกครั้ง

ขั้นตอนที่ 13:ตรวจสอบข้อผิดพลาดในการกำหนดนิยามใหม่

หลังจากกระบวนการกำหนดนิยามใหม่เสร็จสมบูรณ์แล้ว ให้รันคำสั่งต่อไปนี้เพื่อตรวจสอบข้อผิดพลาด:

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, -- Non-Default
           num_errors        => l_num_errors);
           DBMS_OUTPUT.put_line('l_num_errors=' || l_num_errors);
     END;
/
SQL> spool off

หากนิยามใหม่สำเร็จ คุณควรเห็นผลลัพธ์ที่คล้ายกับต่อไปนี้ใน copy_table_dependents.txt ไฟล์:

l_num_errors=0
PL/SQL procedure successfully completed.

ขั้นตอนที่ 14:(ไม่บังคับ) ซิงโครไนซ์ตารางพาร์ติชันอีกครั้ง

หากต้องการ ให้เรียกใช้คำสั่งต่อไปนี้เพื่อซิงโครไนซ์ตารางพาร์ติชั่นใหม่โดยใช้ชื่อชั่วคราว:

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:รวบรวมสถิติในตารางพาร์ทิชัน

รันคำสั่งต่อไปนี้เพื่อรวบรวมสถิติบนตารางพาร์ติชั่น:

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

ขั้นตอนที่ 16:สร้างสคริปต์ข้อจำกัด

เรียกใช้คำสั่งต่อไปนี้เพื่อเตรียมสคริปต์เพื่อเปิดใช้งานข้อจำกัดการตรวจสอบ

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';

'ALTERTABLE'||''||OWNER||'.'||TABLE_NAME||'ENABLEVALIDATECONSTRAINT'||''||CONSTR
--------------------------------------------------------------------------------
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

ขั้นตอนที่ 17:เปิดใช้งานการตรวจสอบข้อจำกัด

รันสคริปต์และคำสั่งที่สร้างโดยขั้นตอนที่ 16 ดังแสดงในตัวอย่างต่อไปนี้:

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:เปรียบเทียบตารางที่ไม่ใช่พาร์ติชันและพาร์ติชัน

เปรียบเทียบตารางเดิมที่ไม่ใช่พาร์ติชันกับตารางพาร์ติชันใหม่เพื่อยืนยันว่าแอตทริบิวต์ทั้งหมดเหมือนกัน

ขั้นตอนที่ 19:เปลี่ยนชื่อตาราง

เรียกใช้คำสั่งต่อไปนี้เพื่อตั้งค่าตารางชั่วคราวเป็นตารางจริงเพื่อเปลี่ยนชื่อตาราง:

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

--------------------------------------------
@?/rdbms/admin/utlrp.sql
--------------------------------------------

SQL>spool off

ขั้นตอนที่ 20:เปรียบเทียบตาราง

เรียกใช้คำสั่งต่อไปนี้เพื่อเปรียบเทียบจำนวนระเบียนของทั้งสองตารางและตรวจสอบว่าตรงกัน:

SQL> spool table_count.outp.txt
SQL> select count(*) from TEST.TABLEA;

 COUNT(*)
----------
  890540

SQL> select count (*) from TEST.TABLEA_PAR;

 COUNT(*)
----------
  890540

SQL> spool off

ขั้นตอนที่ 21:ตรวจสอบความสำเร็จของพาร์ติชัน

รันคำสั่งต่อไปนี้เพื่อตรวจสอบว่ากระบวนการพาร์ติชั่นสำเร็จหรือไม่:

SQL> spool check_partition.txt
SQL> select partitioned from dba_tables where table_name = 'TABLEA' and owner='TEST';

PAR
------
YES

SQL> select partition_name , SUBPARTITION_COUNT, TABLESPACE_NAME from dba_tab_partitions where table_name='TABLEA' and table_owner='TEST';
SQL> select table_name, partition_name, high_value, partition_position from DBA_tab_partitions where table_name='TABLEA' and table_owner='TEST';
SQL> spool off

ขั้นตอนที่ 22:ตรวจสอบวัตถุฐานข้อมูลอีกครั้ง

เรียกใช้คำสั่งต่อไปนี้เพื่อตรวจสอบวัตถุฐานข้อมูลและเปรียบเทียบผลลัพธ์กับขั้นตอนที่ 2:

SET LINESIZE 500
SET PAGESIZE 1000
SQL> spool cons_indx_trigg.txt
SQL> select name, type, owner from all_dependencies where referenced_owner = 'TEST' and referenced_name = 'TABLEA';

NAME                TYPE              OWNER
----------------    ---------------   ------------
PROC_TABLEA         PROCEDURE         TEST
TABLEA_TRIGG        TRIGGER           TEST
PKG_TABLEA          PACKAGE BODY      TEST

SQL> select OWNER, INDEX_NAME, TABLE_OWNER, TABLE_NAME, STATUS, TABLESPACE_NAME from dba_indexes where TABLE_OWNER='TEST' and TABLE_NAME='TABLEA';

OWNER  INDEX_NAME       TABLE_OWNER TABLE_NAME  STATUS   TABLESPACE_NAME
------------------------------------------------------------------------
TEST   TABLEA_IDX_ID01  TEST        TABLEA      VALID    TABLEA_TBL
TEST   TABLEA_IDX_ID04  TEST        TABLEA      VALID    TABLEA_TBL
TEST   TABLEA_IDX_PK    TEST        TABLEA      VALID    TABLEA_TBL

SQL> select STATUS, OBJECT_TYPE, OBJECT_NAME  from dba_objects where OWNER='TEST' and OBJECT_TYPE = 'TRIGGER' and STATUS='INVALID';

no rows selected

SQL> select CONSTRAINT_NAME, CONSTRAINT_TYPE from dba_constraints where TABLE_NAME='TABLEA' and owner='TEST';

CONSTRAINT_NAME        C
-------------------		----------
SYS_C002004601         C
SYS_C002004602         C
SYS_C002004603         C
IDX_PK                 P
FK01                   R

12 rows selected.

SQL> spool off

ขั้นตอนที่ 23:สร้างดัชนีใหม่

รันคำสั่งต่อไปนี้เพื่อสร้างดัชนีใหม่บน tablespace ใหม่:

SQL> spool rebuild_indx.txt
SQL>@rebuild_index.sql

ALTER INDEX TEST.TABLEA_IDX_ID01 REBUILD TABLESPACE TABLEA_TBL_PAR ONLINE;
ALTER INDEX TEST.ITABLEA_IDX_ID04 REBUILD TABLESPACE TABLEA_TBL_PAR ONLINE;
ALTER INDEX TEST.TABLEA_IDX_PK REBUILD TABLESPACE TABLEA_TBL_PAR ONLINE;

SQL> spool off

ขั้นตอนที่ 24:ตรวจสอบความถูกต้องของดัชนี

เรียกใช้คำสั่งต่อไปนี้เพื่อตรวจสอบว่าสถานะ valid และ thetablespace สำหรับดัชนีทั้งหมดคือ TABLEA_TBL_PAR:

SQL> spool verify_indx.outp.txt
SQL> select OWNER, INDEX_NAME, TABLE_OWNER, TABLE_NAME, STATUS, TABLESPACE_NAME from dba_indexes where TABLE_OWNER='TEST' and TABLE_NAME='TABLEA';

OWNER  INDEX_NAME       TABLE_OWNER  TABLE_NAME   STATUS   TABLESPACE_NAME
---------------------------------------------------------------------------
TEST   TABLEA_IDX_ID01  TEST         TABLEA       VALID   	 TABLEA_TBL_PAR
TEST   TABLEA_IDX_ID04  TEST         TABLEA       VALID   	 TABLEA_TBL_PAR
TEST   TABLEA_IDX_PK    TEST         TABLEA       VALID     TABLEA_TBL_PAR

SQL>spool off

ขั้นตอนที่ 25:วางตารางที่ไม่ใช่พาร์ติชันดั้งเดิม

หลังจากที่ DBA ยืนยันว่าทุกอย่างดูดีแล้ว ให้รันคำสั่ง follow เพื่อลบตารางเดิม ซึ่งตอนนี้มีชื่อของ TEST.TABLEA_PAR ชั่วคราว:

SQL> DROP table TEST.TABLEA_PAR cascade constraints;

บทสรุป

ขั้นตอนก่อนหน้านี้ใช้ตารางระหว่างกาล TEST.TABLEA_PAR เพื่อแบ่งพาร์ติชันตาราง TEST.TABLEA ลงในตารางช่วงช่วงโดยไม่มีการหยุดทำงานของแอปพลิเคชัน

ใช้แท็บคำติชมเพื่อแสดงความคิดเห็นหรือถามคำถาม