Định nghĩa lại bảng trực tuyến cho phép bạn cấu trúc lại quá trình sản xuất bảng Oracle® của mình mà không làm cho dữ liệu không có sẵn. Bạn có thể thấy thoải mái khi sử dụng các bảng để di chuyển dữ liệu, nhưng có một giải pháp tốt hơn.
Giới thiệu
Việc sắp xếp 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 khiến bảng và dữ liệu không có sẵn trong một khoảng thời gian nhất định, đây là một tình huống ít thuận lợi hơn cho các doanh nghiệp. Đây là khi DBMS_REDEFINITION gói tiết kiệm trong ngày, 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 Oracletable vì những lý do sau:
- Để nâng cao hiệu suất của các truy vấn hoặc Ngôn ngữ thao tác dữ liệu (DML)
- Để điều chỉnh các thay đổi của ứng dụng
- Để quản lý bộ nhớ
Cơ sở dữ liệu Oracle 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, đó là xác định lại bảng trực tuyến. Việc xác định lại bảng trực tuyến mang lại hiệu suất tăng đáng kể khi so sánh với các phương pháp xác định lại bảng truyền thống.
Khi một bảng được xác định lại trực tuyến, bảng đó 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 chỉ bị khóa ở chế độ độc quyền trong một khoảng thời gian ngắn, không phụ thuộc vào kích thước của bảng và mức độ phổ biến của định nghĩa lại. Quá trình xác định lại hoàn toàn không rõ ràng đối với người dùng.
Việc xác định lại bảng trực tuyến yêu cầu không gian trống gần tương đương với không gian hiện đang được sử dụng bởi bảng đang được xác định lại.
Có nhiều cách để tổ chức lại một bảng. Khi thời gian chết là một thách thức, thì DBMS_REDEFINITION gói là lựa chọn tốt nhất cho nhiệm vụ này.
Xác định lại bảng trực tuyến
Sử dụng các bước sau để xác định lại một bảng trực tuyến.
-
Chọn phương pháp xác định lại,
by keyhoặcby rowids.Bằng khóa :Chọn khóa chính hoặc khóa chính giả để sử dụng cho định nghĩa. Khóa chính giả là khóa duy nhất trong đó tất cả các cột thành phần chứa
NOT NULLhạn chế. Đối 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 phải có cùng một cột chính. Đây là phương pháp xác định lại được ưu tiên và mặc định.Bởi rowid: Sử dụng phương pháp này nếu không có khóa nào. Trong phương thức này, cột ẩn, có tên là
M_ROW$$, được thêm vào phiên bản sau khi xác định lại của bảng. Cột này nên được bỏ hoặc đánh dấu là không sử dụng sau khi định nghĩa xong. NếuCOMPATIBLEđược đặt thành 10.2.0 hoặc cao hơn, giai đoạn cuối của quá trình xác định lại tự động đặt cột này là không sử dụng. Sau đó, bạn không thể sử dụngALTER TABLE ... DROP UNUSED COLUMNSđể loại bỏ nó. Bạn không thể sử dụng phương pháp này trên các bảng được tổ chức theo chỉ mục. -
Xác minh rằng bảng có thể được xác định lại trực tuyến bằng cách gọi
CAN_REDEF_TABLEthủ tục. Nếu bảng không phải là ứng cử viên cho định nghĩa trực tuyến, thì quy trình này gây ra lỗi cho biết lý do tại sao bảng không thể được định nghĩa lại trực tuyến. -
Tạo một bảng tạm thời trống (trong cùng một lược đồ với bảng được xác định) với tất cả các thuộc tính vật lý và logic mong muốn.
-
Không cần thiết phải tạo bảng tạm thời với tất cả các chỉ mục, ràng buộc, trợ cấp và trình kích hoạt của bảng được xác định lại. Điều này có thể tự động hoàn thành bằng cách sử dụng
COPY_TABLE_DEPENDENTSthủ tục. -
Để cải thiện hiệu suất trong các bảng lớn hơn, bạn có thể đặt 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; -
FINISH_REDEF_TABLElệnh hoàn thành việc xác định lại bảng. Trong quá trình này, bảng gốc bị khóa trong chế độ độc quyền trong thời gian ngắn, không phụ thuộc vào 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ý được gửi trước khi hoàn thành việc xác định lại. -
Nếu bạn đã sử dụng
rowidsđể xác định lại vàCOMPATIBLEcủa bạn tham số khởi tạo được đặt thành 10.1.0 hoặc thấp hơn, bạn cần bỏ qua cột ẩnM_ROW$$đã được thêm vào bảng được xác định 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 TABLE <table_name> SET UNUSED (M_ROW$$);Nếu
COMPATIBLElà 10.2.0 trở lên, cột ẩn này tự động được đặt thànhUNUSEDkhi quá trình xác định lại hoàn tất. Sau đó, bạn có thể thả cột bằngALTER TABLE ... DROP UNUSED COLUMNSbản tường trình. Chờ hoàn thành bất kỳ truy vấn chạy dài nào đối với bảng tạm thời, sau đó thả bảnginterim xuống.
Định nghĩa lại bảng mẫu
Phần sau đây trình bày các ví dụ về các lệnh và kết quả đầu ra khác nhau để định nghĩa lại bảng làm ví dụ.
Bắt đầu sqlplus
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 options
Tạo bảng demo
Mẫu sau minh họa việc tạo tên bảng demo TEST1 dưới AMIT lược đồ.
SQL> CREATE TABLE TEST1 ( ID NUMBER(10) ,
ENAME VARCHAR2(10),
SAL NUMBER(10) ) ;
Chèn hàng loạt
Mẫu sau minh họa việc chèn hàng loạt và cài đặt PPA_AGGGREGATE_TARGET trong AMIT giản đồ 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 các đối tượng phụ thuộc để thử nghiệm
Mẫu sau minh họa việc tạo các đối tượng phụ thuộc liên quan đến bảng TEST1 để bạn có thể xem điều gì xảy ra trong quá trình xác định lại trực tuyến.
Xem tác phẩm
SQL> CREATE VIEW TEST1_VW AS SELECT * FROM TEST1 ;
VIEW CREATED.
Tạo trình tự
SQL> CREATE SEQUENCE TEST_SEQ ;
SEQUENCE CREATED.
Tạo thủ tục
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 trình kích hoạt 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.
Tạo khóa chính
SQL> ALTER TABLE TEST1 ADD CONSTRAINT TEST1_ID_PK PRIMARY KEY (ID) ;
TABLE ALTERED.
Nhận trạng thái trước khi xác định 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 để xác định lại
Mẫu sau đây cho thấy việc kiểm tra xem bảng có thể được xác định lại trực tuyến bằng cách sử dụng rowids hoặc primary key :
Sử dụng khóa chính
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 tạm thời
Mẫu sau minh họa việc tạo một bản sao của bảng tạm thời mới mà 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(*)
----------
999999
Kết nối với cơ sở dữ liệu
Ví dụ sau minh họa kết nối bằng cách sử dụng người dùng đặc quyền để thực thi tác vụ xác định 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 options
Bắt đầu xác định lại
Mẫu sau minh họa việc bắt đầu định nghĩa lại bằng cách sử dụng khóa chính:
SQL> EXEC DBMS_REDEFINITION.START_REDEF_TABLE('AMIT','TEST1', 'TEST1_REORG');
PL/SQL PROCEDURE SUCCESSFULLY COMPLETED.
Sao chép các đối tượng phụ thuộc
Mẫu sau minh họa tự động sao chép các đối tượng phụ thuộc nhưmview, khóa chính, chế độ xem, trình tự và trình kích hoạt. IGNORE_ERROR được đặt thành TRUE để tránh vi phạm khóa chính với COPY_TABLE_DEPENDENTS lệnh.
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
Mẫu sau minh họa việc kiểm tra lỗi trong DBA_REDEFINITION_ERRORS xem:
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
Xác thực cả hai bảng
Mẫu sau minh họa việc xác thực số hàng trên cả hai bảng và đồng bộ hóa với bảng tạm thời:
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.
Kết thúc quá trình xác định lại
Mẫu sau đây chứng minh việc hoàn thành việc xác định 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 giản đồ
Mẫu sau cho thấy việc biên dịch lại giản đồ với sự phụ thuộc hoàn chỉnh, điều này là cần thiết vì các trình kích hoạt không hợp lệ trong bước bắt buộ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.
Bỏ bảng tạm thời
Mẫu sau minh họa việc loại bỏ bảng tạm thời:
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à truy cập đồng thời 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 chết nào, do đó tránh được những thay đổi do thời gian ngừng hoạt động cho khách hàng trong môi trường xử lý giao dịch trực tuyến (OLTP).
Sử dụng tab Phản hồi để đưa ra bất kỳ nhận xét hoặc đặt câu hỏi nào.