Bắt đầu từ Oracle® 10g, bạn có thể phân vùng bảng trực tuyến mà không có bất kỳ thời gian ngừng hoạt động ứng dụng nào bằng cách sử dụng gói DBMS_REDEFINITION.
Sử dụng các bước sau để thay đổi một bảng không phân vùng thành bảng phân vùng bằng cách sử dụng DBMS_REDEFINITION. Ví dụ này thay đổi bảng không phân vùng, TABLEA, thành bảng phân vùng phạm vi khoảng (range interval partition).
Bước 1: Sao lưu bảng không phân vùng
Chạy mã sau để tạo bản sao lưu xuất đầy đủ của bảng, 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_onlyBước 2: Kiểm tra các đối tượng cơ sở dữ liệu
Các đối tượng cơ sở dữ liệu phụ thuộc (D) sau có thể bị xóa khi một bảng bị xóa:
CONSTRAINT (Ràng buộc) D
INDEX (Chỉ mục) D
MATERIALIZED_VIEW_LOG (Nhật ký View vật chất hóa) D
OBJECT_GRANT (Cấp quyền đối tượng) D
TRIGGER (Kích hoạt) D
Chạy các lệnh SQL sau và lưu đầu ra trong một file spool, chẳng hạn như 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 RBước 3: Thu thập DDL của TABLEA
Chạy các lệnh sau để thu thập ngôn ngữ định nghĩa dữ liệu (DDL) của TABLEA và lưu script trong file spool DEF_TABLEA.sql trước khi tạo bảng phân vùng:
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 offBước 4: Sao chép script DDL
Chạy lệnh sau để sao chép script DDL mà bạn đã tạo ở bước 3.
cp DEF_TABLEA.sql DEF_TABLEA_PAR.sqlBước 5: Xem lại các ngày trong bảng không phân vùng
Chạy lệnh sau để tìm các ngày trong TABLEA:
SQL> select * from (select DT from TEST.TABLEA where rownum <15 order by DT DESC);Bước 6: Chỉnh sửa file DEF_TABLEA_PAR.sql
Chỉnh sửa DEF_TABLEA_PAR.sql để thực hiện các thay đổi sau:
Thay đổi tất cả các lần xuất hiện của TABLEA thành TABLEA_PAR.
Xóa tất cả các ràng buộc, như NOT NULL hoặc bất kỳ ràng buộc nào khác.
Chèn lệnh sau để bảng được tạo trong tablespace mới:
TABLESPACE "TABLEA_TBL_PAR" LOGGINGChèn các lệnh sau để thêm định nghĩa phân vùng dựa trên các ngày được xác định ở Bước 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')));
File DEF_TABLEA_PAR.sql bây giờ trông như ví dụ sau:
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')));Bước 7: Tạo bảng phân vùng
Tạo bảng phân vùng bằng cách chạy các bước sau để chạy script DEF_TABLEA_PAR.sql:
SQL> spool DEF_TABLEA_PAR.outp.txt
SQL> @DEF_TABLEA_PAR.sql
Table Created.
SQL> spool offBước 8: Xác minh bảng phân vùng
Chạy các lệnh sau để xác minh bảng phân vùng và trả về các phân vùng đã định nghĩa:
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_2005Bước 9: Thu thập thống kê trên bảng không phân vùng
Chạy các lệnh sau để thu thập thống kê trên bảng không phân vùng và lưu chúng vào file spool.
SQL> SPOOL gather_stats.txt
SQL> exec dbms_stats.gather_table_stats ('TEST', 'TABLEA',cascade => TRUE);
SQL> spool offBước 10: Kiểm tra tính khả thi của redefinition
Lưu ý: Bảng nguồn (không phân vùng) không cần khóa chính trước khi bạn sử dụng gói redefinition.
Chạy các lệnh sau để xem liệu redefinition có thể thực hiện được không, và lưu kết quả vào file spool:
SQL> spool check_the_redefinition.txt
SQL> EXEC DBMS_Redefinition.can_redef_table ('TEST', 'TABLEA');
SQL> spool offBước 11: Bắt đầu redefinition
Nếu không có lỗi được liệt kê trong check_the_redefinition.txt, hãy bắt đầu redefinition bằng cách sử dụng lệnh chạy lâu sau:
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 offBước 12: Theo dõi lỗi tablespace trong quá trình redefinition
Hoạt động redefinition ở bước 11 có thể dẫn đến các cảnh báo tablespace như ví dụ sau:
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 2Nếu bạn thấy các lỗi tablespace tương tự như ví dụ trước, thì bạn nên thực hiện các bước sau:
Chạy lệnh sau để dừng quá trình redefinition.
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 offXóa bảng phân vùng và materialized view.
Tăng kích thước tablespace. Trong ví dụ này, bạn nên tăng kích thước tablespace TABLEA_TBL.
Chạy lại bước 11.
Bước 13: Kiểm tra lỗi redefinition
Sau khi quá trình redefinition hoàn thành thành công, hãy chạy các lệnh sau để kiểm tra bất kỳ lỗi nào:
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 offNếu redefinition thành công, bạn sẽ thấy kết quả tương tự như sau trong file copy_table_dependents.txt:
l_num_errors=0
PL/SQL procedure successfully completed.Bước 14: (Tùy chọn) Đồng bộ lại bảng phân vùng
Nếu bạn muốn, hãy chạy các lệnh sau để đồng bộ lại bảng phân vùng với tên trung gian:
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 offBước 15: Thu thập thống kê trên bảng phân vùng
Chạy các lệnh sau để thu thập thống kê trên bảng phân vùng:
SQL> spool gather_statistics_par.txt
SQL> exec dbms_stats.gather_table_stats ('TEST', 'TABLEA_PAR',cascade => TRUE);
SQL> spool offBước 16: Tạo script ràng buộc
Chạy các lệnh sau để chuẩn bị script để kích hoạt ràng buộc validate.
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 offBước 17: Kích hoạt ràng buộc validate
Chạy script và các lệnh được tạo ra bởi bước 16, như được hiển thị trong ví dụ sau:
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 offBước 18: So sánh bảng không phân vùng và bảng phân vùng
So sánh bảng gốc, không phân vùng, với bảng phân vùng mới để xác minh rằng tất cả các thuộc tính đều giống nhau.
Bước 19: Đổi tên các bảng
Chạy các lệnh sau để đặt bảng trung gian làm bảng thực để chuyển đổi tên bảng:
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 offBước 20: So sánh các bảng
Chạy các lệnh sau để so sánh số lượng bản ghi của cả hai bảng và đảm bảo chúng khớp:
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 offBước 21: Xác minh thành công phân vùng
Chạy các lệnh sau để xác minh quá trình phân vùng đã thành công:
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 offBước 22: Kiểm tra lại các đối tượng cơ sở dữ liệu
Chạy các lệnh sau để kiểm tra các đối tượng cơ sở dữ liệu và so sánh kết quả với bước 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 offBước 23: Tái xây dựng chỉ mục
Chạy các lệnh sau để tái xây dựng chỉ mục trên tablespace mới:
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 offBước 24: Xác thực chỉ mục
Chạy các lệnh sau để xác minh rằng trạng thái là valid và tablespace cho tất cả các chỉ mục là 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 offBước 25: Xóa bảng không phân vùng gốc
Sau khi DBA đã xác nhận mọi thứ trông tốt, thực thi lệnh sau để xóa bảng gốc, bây giờ có tên của bảng trung gian, TEST.TABLEA_PAR:
SQL> DROP table TEST.TABLEA_PAR cascade constraints;Kết luận
Các bước trước đó sử dụng bảng trung gian, TEST.TABLEA_PAR, để phân vùng bảng, TEST.TABLEA, thành bảng phạm vi khoảng (range interval table) mà không có bất kỳ thời gian ngừng hoạt động ứng dụng nào.
Sử dụng tab Feedback để đưa ra bất kỳ nhận xét hoặc đặt câu hỏi nào.