Định nghĩa lại bảng trực tuyến (Online table redefinition) cho phép bạn cấu trúc lại bảng Oracle® của mình trong môi trường sản xuất mà không làm cho dữ liệu không khả dụng. Bạn có thể thoải mái sử dụng bảng tạm để di chuyển dữ liệu, nhưng có giải pháp tốt hơn.
Giới thiệu
Việc dàn dựng (staging) dữ liệu và di chuyển nó xung quanh trong khi bạn cấu trúc lại bảng của mình làm cho cả bảng và dữ liệu không khả dụng trong một khoảng thời gian nhất định, đây là tình huống không thuận lợi cho doanh nghiệp. Đây là lúc gói DBMS_REDEFINITION cứu vãn tình hình, như được hiển thị trong hình ảnh sau:
Mục đích
Định kỳ, bạn cần sửa đổi cấu trúc logic hoặc vật lý của một bảng Oracle vì các lý do sau:
- Để nâng cao hiệu suất truy vấn hoặc Ngôn ngữ Thao tác Dữ liệu (DML)
- Để đáp ứng các thay đổi ứng dụng
- Để quản lý lưu trữ
Oracle Database cung cấp một cơ chế để thực hiện các sửa đổi cấu trúc bảng mà không ảnh hưởng đáng kể đến tính khả dụng của bảng, được gọi là định nghĩa lại bảng trực tuyến (online table redefinition). Định nghĩa lại bảng trực tuyến cung cấp sự tăng cường hiệu suất đáng kể khi so sánh với các phương pháp truyền thống của định nghĩa lại bảng.
Khi một bảng được định nghĩa lại trực tuyến, nó có thể truy cập được cho cả truy vấn và DML trong hầu hết quá trình định nghĩa lại. Bảng bị khóa ở chế độ độc quyền (exclusive mode) chỉ trong một khoảng thời gian ngắn, độc lập với kích thước của bảng và độ phức tạp của việc định nghĩa lại. Quy trình định nghĩa lại hoàn toàn trong suốt đối với người dùng.
Định nghĩa lại bảng trực tuyến yêu cầu không gian trống xấp xỉ tương đương với không gian hiện đang được sử dụng bởi bảng đang được định nghĩa lại.
Có nhiều cách để tổ chức lại một bảng. Khi thời gian ngừng hoạt động là một thách thức, thì gói DBMS_REDEFINITION là tùy chọn tốt nhất cho nhiệm vụ này.
Định nghĩa lại bảng trực tuyến
Sử dụng các bước sau để định nghĩa lại một bảng trực tuyến.
Chọn phương pháp định nghĩa lại, hoặc
by keyhoặcby rowids.Theo khóa (By key): Chọn khóa chính (primary key) hoặc khóa chính giả (pseudo-primary key) để sử dụng cho định nghĩa lại. Khóa chính giả là các khóa duy nhất nơi tất cả các cột thành phần chứa ràng buộc
NOT NULL. Với phương pháp này, các phiên bản của bảng trước và sau khi định nghĩa lại nên bao gồm cùng các cột khóa chính. Đây là phương pháp ưu tiên và mặc định của định nghĩa lại.Theo rowid: Sử dụng phương pháp này nếu không có khóa nào có sẵn. Trong phương pháp này, một cột ẩn, tên là
M_ROW$$, được thêm vào phiên bản sau định nghĩa lại của bảng. Cột này nên bị xóa hoặc đánh dấu là không sử dụng sau khi định nghĩa lại hoàn tất. NếuCOMPATIBLEđược đặt thành 10.2.0 hoặc cao hơn, giai đoạn cuối cùng của định nghĩa lại tự động đặt cột này là không sử dụng. Sau đó bạn có thể sử dụng câu lệnhALTER TABLE ... DROP UNUSED COLUMNSđể xóa nó. Bạn không thể sử dụng phương pháp này trên các bảng tổ chức chỉ mục (index-organized tables).Xác minh rằng bảng có thể được định nghĩa lại trực tuyến bằng cách gọi thủ tục
CAN_REDEF_TABLE. Nếu bảng không phải là ứng cử viên cho định nghĩa lại trực tuyến, thủ tục này sẽ ném ra lỗi chỉ ra lý do bảng không thể được định nghĩa lại trực tuyến.Tạo một bảng trung gian trống (trong cùng schema với bảng cần định nghĩa lại) với tất cả các thuộc tính logic và vật lý mong muốn.
Không cần thiết để tạo bảng trung gian với tất cả các chỉ mục, ràng buộc, cấp quyền và trigger của bảng đang được định nghĩa lại. Điều này có thể được thực hiện tự động bằng cách sử dụng thủ tục
COPY_TABLE_DEPENDENTS.Để cải thiện hiệu suất trong các bảng lớn, bạn có thể đặt nó song song bằng cách sử dụng các lệnh sau:
ALTER SESSION force parallel dml parallel degree-of-parallelism; ALTER SESSION force parallel query parallel degree-of-parallelism;Lệnh
FINISH_REDEF_TABLEhoàn tất định nghĩa lại của bảng. Trong thủ tục này, bảng gốc bị khóa ở chế độ độc quyền trong một thời gian ngắn, độc lập với lượng dữ liệu trong bảng gốc. Tuy nhiên,FINISH_REDEF_TABLEđợi tất cả các hoạt động DML đang chờ xử lý để cam kết trước khi hoàn tất định nghĩa lại.Nếu bạn đã sử dụng
rowidscho định nghĩa lại và tham số khởi tạoCOMPATIBLEcủa bạn được đặt thành 10.1.0 hoặc thấp hơn, bạn cần xóa cột ẩnM_ROW$$đã được thêm vào bảng đã định nghĩa lại. Bạn cũng có thể đặt cột thành "UNUSED" bằng cách sử dụng lệnh sau:ALTER TABLESET UNUSED (M_ROW$$); Nếu
COMPATIBLElà 10.2.0 hoặc cao hơn, cột ẩn này tự động được đặt thànhUNUSEDkhi định nghĩa lại hoàn tất. Sau đó bạn có thể xóa cột với câu lệnhALTER TABLE ... DROP UNUSED COLUMNS. Đợi bất kỳ truy vấn chạy dài nào đối với bảng trung gian hoàn tất và sau đó xóa bảng trung gian.
Ví dụ định nghĩa lại bảng mẫu
Phần sau hiển thị các ví dụ của các lệnh và đầu ra khác nhau cho một ví dụ định nghĩa lại bảng mẫu.
Khởi động sqlplus
Ví dụ mẫu sau minh họa việc khởi động 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 optionsTạo bảng demo
Ví dụ mẫu sau minh họa việc tạo bảng demo tên TEST1 dưới schema AMIT.
SQL> CREATE TABLE TEST1 (
ID NUMBER(10),
ENAME VARCHAR2(10),
SAL NUMBER(10)
);Chèn hàng loạt dòng
Ví dụ mẫu sau minh họa việc chèn hàng loạt dòng và đặt PPA_AGGGREGATE_TARGET trong schema AMIT thành giá trị tối đa.
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.Tạo đối tượng phụ thuộc cho kiểm tra
Ví dụ mẫu sau minh họa việc tạo đối tượng phụ thuộc liên quan đến hàng của bảng TEST1, để bạn có thể thấy những gì xảy ra trong quá trình định nghĩa lại trực tuyến.
Tạo View
SQL> CREATE VIEW TEST1_VW AS SELECT * FROM TEST1;
VIEW CREATED.Tạo Sequence
SQL> CREATE SEQUENCE TEST_SEQ;
SEQUENCE CREATED.Tạo Procedure
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.Tạo DML Trigger
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.Tạo Primary Key
SQL> ALTER TABLE TEST1 ADD CONSTRAINT TEST1_ID_PK PRIMARY KEY (ID);
TABLE ALTERED.Lấy trạng thái trước định nghĩa lại
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.Kiểm tra bảng cho định nghĩa lại
Ví dụ mẫu sau minh họa việc kiểm tra bảng có thể được định nghĩa lại trực tuyến bằng cách sử dụng hoặc rowids hoặc primary key:
Sử dụng primary key
SQL> EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE ('AMIT','TEST1',DBMS_REDEFINITION.CONS_USE_PK);
PL/SQL PROCEDURE SUCCESSFULLY COMPLETED.Sử dụng rowid
SQL> EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE ('AMIT','TEST1',DBMS_REDEFINITION.CONS_USE_ROWID);
PL/SQL PROCEDURE SUCCESSFULLY COMPLETED.Tạo bản sao của bảng trung gian
Ví dụ mẫu sau minh họa việc tạo bản sao của bảng trung gian mới không có bất kỳ đối tượng phụ thuộc nào:
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(*)
----------
999999Kết nối với cơ sở dữ liệu
Ví dụ mẫu sau minh họa việc kết nối bằng cách sử dụng user đặc quyền để thực hiện tác vụ định nghĩa lại bảng:
[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 optionsBắt đầu định nghĩa lại
Ví dụ mẫu sau minh họa việc bắt đầu định nghĩa lại bằng cách sử dụng primary key:
SQL> EXEC DBMS_REDEFINITION.START_REDEF_TABLE('AMIT','TEST1', 'TEST1_REORG');
PL/SQL PROCEDURE SUCCESSFULLY COMPLETED.Sao chép đối tượng phụ thuộc
Ví dụ mẫu sau minh họa việc tự động sao chép đối tượng phụ thuộc như mview, primary key, view, sequence và trigger. IGNORE_ERROR được đặt thành TRUE để tránh vi phạm primary key với lệnh 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.Kiểm tra lỗi
Ví dụ mẫu sau minh họa việc kiểm tra lỗi trong view 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 SELECTEDXác thực cả hai bảng
Ví dụ mẫu sau minh họa việc xác thực số lượng hàng trên cả hai bảng và đồng bộ với bảng trung gian:
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.Hoàn tất định nghĩa lại
Ví dụ mẫu sau minh họa việc hoàn tất định nghĩa lại:
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.Kiểm tra lỗi và biên dịch lại schema
Ví dụ mẫu sau minh họa việc biên dịch lại schema với sự phụ thuộc hoàn chỉnh, cần thiết do các trigger không hợp lệ ở bước trước:
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.Xóa bảng trung gian
Ví dụ mẫu sau minh họa việc xóa bảng trung gian:
SQL> DROP TABLE AMIT.TEST1_REORG;
TABLE DROPPED.Kết luận
Nếu cấu trúc bảng cần được sửa đổi và đồng thời được truy cập bởi người dùng cuối, hãy sử dụng DBMS_REFDEFINITION.
Tính năng này giúp tổ chức lại dữ liệu mà không có bất kỳ thời gian ngừng hoạt động nào, từ đó tránh các thách thức do downtime gây ra cho khách hàng trong môi trường xử lý giao dịch trực tuyến (OLTP).
Sử dụng tab Feedback để đưa ra bất kỳ nhận xét hoặc đặt câu hỏi nào.