การกำหนดตารางใหม่แบบออนไลน์ช่วยให้คุณสามารถปรับโครงสร้างการสร้างตาราง Oracle® ใหม่ได้โดยไม่ทำให้ข้อมูลไม่พร้อมใช้งาน คุณอาจสะดวกที่จะใช้ตารางชั่วคราวเพื่อย้ายข้อมูลไปรอบๆ แต่มีวิธีอื่นที่ดีกว่า
แนะนำตัว
การจัดเตรียมข้อมูลและย้ายข้อมูลไปรอบๆ ขณะที่คุณปรับโครงสร้างตารางใหม่ทำให้ทั้งตารางและข้อมูลไม่พร้อมใช้งานในช่วงระยะเวลาหนึ่ง ซึ่งเป็นสถานการณ์ที่ไม่เอื้ออำนวยสำหรับธุรกิจ นี่คือเมื่อ DBMS_REDEFINITION package บันทึกวันดังแสดงในภาพต่อไปนี้:
วัตถุประสงค์
คุณต้องแก้ไขโครงสร้างเชิงตรรกะหรือทางกายภาพของ Oracletable เป็นระยะๆ ด้วยเหตุผลดังต่อไปนี้:
- เพื่อปรับปรุงการสืบค้นหรือประสิทธิภาพของภาษาการจัดการข้อมูล (DML)
- เพื่อรองรับการเปลี่ยนแปลงการสมัคร
- ในการจัดการพื้นที่เก็บข้อมูล
Oracle Database มีกลไกในการปรับเปลี่ยนโครงสร้างตารางโดยไม่ส่งผลกระทบอย่างมีนัยสำคัญต่อความพร้อมใช้งานของตาราง ซึ่งเรียกว่าการกำหนดนิยามใหม่ของตารางออนไลน์ การกำหนดตารางใหม่ทางออนไลน์ให้ประสิทธิภาพที่เพิ่มขึ้นอย่างมากเมื่อเทียบกับวิธีการกำหนดตารางใหม่แบบเดิม
เมื่อมีการกำหนดตารางใหม่ทางออนไลน์ จะสามารถเข้าถึงทั้งแบบสอบถามและ DML ได้ในระหว่างกระบวนการกำหนดนิยามใหม่ส่วนใหญ่ ตารางถูกล็อคในโหมดเอกสิทธิ์เฉพาะในช่วงเวลาสั้น ๆ ซึ่งไม่ขึ้นกับขนาดของตารางและความซับซ้อนของการนิยามใหม่ กระบวนการกำหนดนิยามใหม่มีความโปร่งใสอย่างสมบูรณ์สำหรับผู้ใช้
การกำหนดตารางใหม่แบบออนไลน์ต้องการพื้นที่ว่างที่ใกล้เคียงกับพื้นที่ที่ใช้ในปัจจุบันโดยตารางที่กำลังกำหนดใหม่
มีหลายวิธีในการจัดระเบียบตารางใหม่ เมื่อการหยุดทำงานเป็นสิ่งที่ท้าทาย DBMS_REDEFINITION แพ็คเกจเป็นตัวเลือกที่ดีที่สุดสำหรับงานนี้
กำหนดตารางออนไลน์ใหม่
ใช้ขั้นตอนต่อไปนี้เพื่อกำหนดตารางออนไลน์ใหม่
-
เลือกวิธีการกำหนดนิยามใหม่
by keyหรือby rowids.ตามคีย์ :เลือกคีย์หลักหรือคีย์หลักเทียมเพื่อใช้สำหรับคำจำกัดความ Pseudo-primary keys เป็นคีย์เฉพาะที่ componentcolumns ทั้งหมดมี
NOT NULLข้อจำกัด สำหรับวิธีนี้ เวอร์ชันของตารางก่อนและหลังการกำหนดนิยามใหม่ควรประกอบด้วยคีย์คอลัมน์หลักเดียวกัน นี่เป็นวิธีการกำหนดนิยามใหม่ที่ต้องการและเป็นค่าเริ่มต้นโดยนักเลง: ใช้วิธีนี้หากไม่มีคีย์ ในวิธีนี้ คอลัมน์ ahidden ชื่อ
M_ROW$$ถูกเพิ่มในเวอร์ชันหลังการกำหนดใหม่ของตาราง คอลัมน์นี้ควรทิ้งหรือทำเครื่องหมายว่าไม่ได้ใช้หลังจากคำจำกัดความนั้นเสร็จสิ้น ถ้าCOMPATIBLEถูกตั้งค่าเป็น 10.2.0 หรือสูงกว่า ช่วงสุดท้ายของการกำหนดนิยามใหม่จะตั้งค่าคอลัมน์นี้เป็นไม่ได้ใช้โดยอัตโนมัติ คุณสามารถใช้ALTER TABLE ... DROP UNUSED COLUMNSคำสั่งที่จะปล่อยคุณไม่สามารถใช้วิธีนี้ในตารางที่จัดดัชนีได้ -
ตรวจสอบว่าตารางสามารถกำหนดใหม่ทางออนไลน์ได้โดยการเรียกใช้
CAN_REDEF_TABLEขั้นตอน. หากตารางไม่ใช่ตัวเลือกสำหรับการกำหนดนิยามใหม่ทางออนไลน์ ขั้นตอนนี้จะทำให้เกิดข้อผิดพลาดที่ระบุว่าเหตุใดจึงไม่สามารถกำหนดตารางใหม่ทางออนไลน์ได้ -
สร้างตารางระหว่างกาลที่ว่างเปล่า (ในสคีมาเดียวกับตารางที่จะกำหนด) พร้อมแอตทริบิวต์ทางตรรกะและทางกายภาพที่ต้องการทั้งหมด
-
ไม่จำเป็นต้องสร้างตารางชั่วคราวด้วยดัชนี ข้อจำกัด เงินช่วยเหลือ และทริกเกอร์ของตารางที่ถูกกำหนดใหม่ทั้งหมด นี้สามารถนอนได้โดยอัตโนมัติโดยใช้
COPY_TABLE_DEPENDENTSขั้นตอน -
ในการปรับปรุงประสิทธิภาพในตารางขนาดใหญ่ คุณสามารถตั้งค่าแบบขนานโดยใช้คำสั่งต่อไปนี้:
ALTER SESSION force parallel dml parallel degree-of-parallelism; ALTER SESSION force parallel query parallel degree-of-parallelism; -
FINISH_REDEF_TABLEคำสั่งจะทำให้การกำหนดตารางใหม่เสร็จสมบูรณ์ ในระหว่างขั้นตอนนี้ ตารางเดิมจะถูกล็อกในโหมดพิเศษในช่วงเวลาสั้นๆ โดยไม่ขึ้นกับปริมาณข้อมูลในตารางต้นฉบับ อย่างไรก็ตามFINISH_REDEF_TABLEรอการดำเนินการ DML ที่ค้างอยู่ทั้งหมดก่อนที่จะกำหนดนิยามใหม่ให้เสร็จสิ้น -
หากคุณใช้
rowidsสำหรับการนิยามใหม่และCOMPATIBLE. ของคุณ พารามิเตอร์เริ่มต้นถูกตั้งค่าเป็น 10.1.0 หรือต่ำกว่า คุณต้องวางคอลัมน์ที่ซ่อนอยู่M_ROW$$ที่ถูกเพิ่มลงในตารางที่กำหนดใหม่ คุณยังสามารถตั้งค่าคอลัมน์เป็น “UNUSED” โดยใช้คำสั่งต่อไปนี้:ALTER TABLE <table_name> SET UNUSED (M_ROW$$);ถ้า
COMPATIBLEคือ 10.2.0 หรือสูงกว่า คอลัมน์ที่ซ่อนอยู่นี้จะถูกตั้งค่าโดยอัตโนมัติเป็นUNUSEDเมื่อนิยามใหม่เสร็จสิ้น จากนั้นคุณสามารถวางคอลัมน์ด้วยALTER TABLE ... DROP UNUSED COLUMNSคำแถลง. รอให้การสืบค้นที่รันอยู่ใดๆ กับตารางระหว่างกาลเสร็จสมบูรณ์แล้วจึงปล่อยตารางระหว่างกาล
ตัวอย่างการกำหนดตารางใหม่
ส่วนต่อไปนี้แสดงตัวอย่างคำสั่งและผลลัพธ์ต่างๆ สำหรับการกำหนดนิยามใหม่ของตารางตัวอย่าง
เริ่ม sqlplus
ตัวอย่างต่อไปนี้สาธิตการเริ่มต้น sqlplus :
[oracle@vm215 ~]$ sqlplus amit/amit
SQL*Plus: Release 11.2.0.3.0 Production on Sat Oct 29 05:44:44 2016
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
สร้างตารางสาธิต
ตัวอย่างต่อไปนี้สาธิตการสร้างชื่อตารางสาธิต TEST1 ภายใต้AMIT สคีมา
SQL> CREATE TABLE TEST1 ( ID NUMBER(10) ,
ENAME VARCHAR2(10),
SAL NUMBER(10) ) ;
แทรกแถวจำนวนมาก
ตัวอย่างต่อไปนี้สาธิตการแทรกแถวจำนวนมากและการตั้งค่าPPA_AGGGREGATE_TARGET ใน AMIT สคีมาเพื่อมูลค่าสูงสุด
SQL> INSERT INTO AMIT.TEST1 SELECT ROWNUM, 'T'|| ROWNUM,
DBMS_RANDOM.VALUE(100000, 999999) FROM DUAL CONNECT BY LEVEL < 1000000;
999999 ROWS CREATED.
SQL> COMMIT;
COMMIT COMPLETE.
สร้างวัตถุที่ขึ้นต่อกันสำหรับการทดสอบ
ตัวอย่างต่อไปนี้สาธิตการสร้างอ็อบเจ็กต์ที่ขึ้นอยู่กับตารางTEST1 เพื่อให้คุณเห็นว่าเกิดอะไรขึ้นในระหว่างการกำหนดนิยามใหม่ทางออนไลน์
ดูผลงาน
SQL> CREATE VIEW TEST1_VW AS SELECT * FROM TEST1 ;
VIEW CREATED.
การสร้างลำดับ
SQL> CREATE SEQUENCE TEST_SEQ ;
SEQUENCE CREATED.
ขั้นตอนการสร้าง
CREATE OR REPLACE PROCEDURE PROC1 (P_ID IN NUMBER)
AS V_ID NUMBER ;
BEGIN
SELECT SAL
INTO V_ID
FROM TEST1
WHERE ID = P_ID;
END;
/
PROCEDURE CREATED.
การสร้างทริกเกอร์ DML
SQL> CREATE OR REPLACE TRIGGER AMIT_TRIG
BEFORE INSERT OR UPDATE ON TEST1
FOR EACH ROW
DECLARE
X NUMBER;
BEGIN
SELECT COUNT(*) INTO X
FROM TEST1
WHERE ID = :NEW.ID;
IF X > 0 THEN
RAISE_APPLICATION_ERROR(-20501, 'ID' || :NEW.ID || ' ALREADY EXISTS');
END IF;
END;
/
TRIGGER CREATED.
การสร้างคีย์หลัก
SQL> ALTER TABLE TEST1 ADD CONSTRAINT TEST1_ID_PK PRIMARY KEY (ID) ;
TABLE ALTERED.
รับสถานะก่อนกำหนดใหม่
SQL> COLUMN OBJECT_NAME FORMAT A20
SELECT OBJECT_NAME, OBJECT_TYPE, STATUS FROM USER_OBJECTS ORDER BY OBJECT_NAME;SQL>
OBJECT_NAME OBJECT_TYPE STATUS
-------------------- ------------------- -------
AMIT_TRIG TRIGGER VALID
PROC1 PROCEDURE VALID
TEST1 TABLE VALID
TEST1_ID_PK INDEX VALID
TEST1_VW VIEW VALID
TEST_SEQ SEQUENCE VALID
6 ROWS SELECTED.
ตรวจสอบตารางเพื่อกำหนดนิยามใหม่
ตัวอย่างต่อไปนี้สาธิตการตรวจสอบว่าตารางสามารถกำหนดใหม่ได้ทางออนไลน์โดยใช้ rowids หรือ primary key :
การใช้คีย์หลัก
SQL> EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE ('AMIT','TEST1',DBMS_REDEFINITION.CONS_USE_PK);
PL/SQL PROCEDURE SUCCESSFULLY COMPLETED.
การใช้ Rowid
SQL> EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE ('AMIT','TEST1',DBMS_REDEFINITION.CONS_USE_ROWID);
PL/SQL PROCEDURE SUCCESSFULLY COMPLETED.
สร้างแบบจำลองของตารางชั่วคราว
ตัวอย่างต่อไปนี้สาธิตการสร้างแบบจำลองของตารางชั่วคราวใหม่โดยไม่มีอ็อบเจ็กต์ที่ขึ้นต่อกัน:
SQL> CREATE TABLE TEST1_REORG AS SELECT * FROM TEST1 WHERE ROWNUM=5 ;
TABLE CREATED.
SQL> SELECT COUNT(*) FROM TEST1_REORG ;
COUNT(*)
----------
0
SQL> SELECT COUNT(*) FROM TEST1;
COUNT(*)
----------
999999
เชื่อมต่อกับฐานข้อมูล
ตัวอย่างต่อไปนี้แสดงให้เห็นถึงการเชื่อมต่อโดยใช้ผู้ใช้ที่มีสิทธิ์ในการดำเนินการงานการกำหนดนิยามใหม่ของตาราง:
[oracle@vm215 ~]$ sqlplus / as sysdba
Sql*plus: release 11.2.0.3.0 production on sat oct 29 05:16:48 2016
Copyright (c) 1982, 2011, oracle. All rights reserved.
Connected to:
Oracle database 11g enterprise edition release 11.2.0.3.0 - 64bit production
With the partitioning, olap, data mining and real application testing options
เริ่มนิยามใหม่
ตัวอย่างต่อไปนี้สาธิตการกำหนดนิยามใหม่เริ่มต้นโดยใช้คีย์หลัก:
SQL> EXEC DBMS_REDEFINITION.START_REDEF_TABLE('AMIT','TEST1', 'TEST1_REORG');
PL/SQL PROCEDURE SUCCESSFULLY COMPLETED.
คัดลอกออบเจ็กต์ที่ขึ้นต่อกัน
ตัวอย่างต่อไปนี้สาธิตการคัดลอกอ็อบเจ็กต์ที่ขึ้นต่อกันโดยอัตโนมัติ เช่น likemview, คีย์หลัก, มุมมอง, ลำดับ และทริกเกอร์ IGNORE_ERROR ถูกตั้งค่าเป็นTRUE เพื่อหลีกเลี่ยงการละเมิดคีย์หลักด้วย COPY_TABLE_DEPENDENTS คำสั่ง
SQL> DECLARE
N PLS_INTEGER;
BEGIN
DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS('AMIT', 'TEST1','TEST1_REORG',
DBMS_REDEFINITION.CONS_ORIG_PARAMS, TRUE, TRUE, TRUE, TRUE, N);
END;
/
PL/SQL PROCEDURE SUCCESSFULLY COMPLETED.
ตรวจหาข้อผิดพลาด
ตัวอย่างต่อไปนี้สาธิตการตรวจสอบข้อผิดพลาดในDBA_REDEFINITION_ERRORS มุมมอง:
SQL> COL OBJECT_NAME FOR A25
SET LIN200 PAGES 200
COL DDL_TEXT FOR A60
SELECT OBJECT_NAME, BASE_TABLE_NAME, DDL_TXT
FROM DBA_REDEFINITION_ERRORS;
NO ROWS SELECTED
ตรวจสอบทั้งสองตาราง
ตัวอย่างต่อไปนี้สาธิตการตรวจสอบจำนวนแถวของทั้งสองตารางและการซิงค์กับตารางชั่วคราว:
SQL> SELECT COUNT(*) FROM AMIT.TEST1_REORG ;
COUNT(*)
----------
999999
SQL> SELECT COUNT(*) FROM AMIT.TEST1 ;
COUNT(*)
----------
999999
SQL> EXEC DBMS_REDEFINITION.SYNC_INTERIM_TABLE('AMIT', 'TEST1', 'TEST1_REORG');
PL/SQL PROCEDURE SUCCESSFULLY COMPLETED.
สิ้นสุดการกำหนดนิยามใหม่
ตัวอย่างต่อไปนี้แสดงให้เห็นถึงการสิ้นสุดการกำหนดนิยามใหม่:
SQL> EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE ('AMIT', 'TEST1', 'TEST1_REORG');
PL/SQL PROCEDURE SUCCESSFULLY COMPLETED.
SQL> COLUMN OBJECT_NAME FORMAT A40
SELECT OBJECT_NAME, OBJECT_TYPE, STATUS
FROM DBA_OBJECTS
WHERE OWNER='AMIT';
OBJECT_NAME OBJECT_TYPE STATUS
--------------------- ------------------- -------
TEST1_VW VIEW INVALID
TEST_SEQ SEQUENCE VALID
PROC1 PROCEDURE VALID
TEST1 TABLE VALID
TEST1_REORG TABLE VALID
TEST1_ID_PK INDEX VALID
TMP$$_TEST1_ID_PK0 INDEX VALID
TMP$$_AMIT_TRIG0 TRIGGER INVALID
AMIT_TRIG TRIGGER INVALID
9 ROWS SELECTED.
ตรวจสอบข้อผิดพลาดและคอมไพล์สคีมาใหม่
ตัวอย่างต่อไปนี้สาธิตการคอมไพล์สคีมาใหม่ด้วยการพึ่งพาที่สมบูรณ์ ซึ่งจำเป็นเนื่องจากทริกเกอร์ที่ไม่ถูกต้องในขั้นตอนก่อนหน้า:
SQL> EXEC UTL_RECOMP.RECOMP_SERIAL('AMIT') ;
PL/SQL PROCEDURE SUCCESSFULLY COMPLETED.
SQL> SELECT OBJECT_NAME, OBJECT_TYPE, STATUS FROM DBA_OBJECTS WHERE OWNER='AMIT';
OBJECT_NAME OBJECT_TYPE STATUS
---------------------------------------- ------------------- -------
TEST1_VW VIEW VALID
TEST_SEQ SEQUENCE VALID
PROC1 PROCEDURE VALID
TEST1 TABLE VALID
TEST1_REORG TABLE VALID
TEST1_ID_PK INDEX VALID
TMP$$_TEST1_ID_PK0 INDEX VALID
TMP$$_AMIT_TRIG0 TRIGGER VALID
AMIT_TRIG TRIGGER VALID
9 ROWS SELECTED.
วางตารางชั่วคราว
ตัวอย่างต่อไปนี้แสดงให้เห็นถึงการวางตารางชั่วคราว:
SQL> DROP TABLE AMIT.TEST1_REORG;
TABLE DROPPED.
บทสรุป
หากจำเป็นต้องแก้ไขโครงสร้างตารางและเข้าถึงได้พร้อมกันโดยผู้ใช้ปลายทาง ให้ใช้ DBMS_REFDEFINITION .
คุณลักษณะนี้ช่วยจัดระเบียบข้อมูลใหม่โดยไม่ต้องหยุดทำงาน จึงหลีกเลี่ยงความท้าทายที่เกิดจากการหยุดทำงานสำหรับลูกค้าในสภาพแวดล้อมการประมวลผลธุรกรรมออนไลน์ (OLTP)
ใช้แท็บคำติชมเพื่อแสดงความคิดเห็นหรือถามคำถาม
