Hướng dẫn thao tác với Partition trong Oracle DB
Drop partition
Drop một partition sẽ xóa toàn bộ dữ liệu trên partition đó
–drop a partition
alter table thetest drop partition p201510;
–>drop a partition + its data + drop the corresponding partitions in each local index
Insert dữ liệu vào một bảng đã có partition
Nếu dữ liệu không thuộc partition nào có sẵn thì sẽ không them được trừ khi cho thêm partition vào bảng. Câu lệnh INSERT không khác gì với câu INSERT thông thường.
Thêm partition vào một bảng đã có sẵn partition (kể cả composite partition)
Bài này dựa trên kiến thức cơ bản về partition như các loại partition, cách đánh partition, … nên sẽ không nói lại các phần căn bản này.
Sử dụng câu lệnh sau:
ALTER TABLE thetest2
ADD PARTITION P201511 VALUES LESS THAN (to_date(‘20151201′,’YYYYMMDD’));
Thêm subpartition vào một bảng có sẵn partition (*-list partition)
ALTER TABLE THETESt2 MODIFY PARTITION P201509
add subpartition P201509_PC values (‘C’, ‘c’);
Nhận thấy, ta cần phải chỉ định rõ rang partition trước khi thêm một subpartition và cấu trúc của việc insert này phải là ALTER TABLE … MODIFY PARTITION … chứ không giống như thêm partition.
Tuy vậy ta có thể thêm subpartition vào cùng với partition trong một câu lệnh như sau:
ALTER TABLE thetest2
ADD PARTITION P201512 VALUES LESS THAN (to_date(‘20160101′,’YYYYMMDD’))
(
SUBPARTITION P201512_PA values (‘A’, ‘a’),
SUBPARTITION P201512_PB values (‘B’, ‘b’),
SUBPARTITION P201512_PC values (‘C’, ‘c’)
)
Chi tiết hơn có thể tham khảo tại:https://docs.oracle.com/cd/E18283_01/server.112/e16541/part_admin002.htm#i1007318
Tạo mới partition vào một table có sẵn dữ liệu nhưng chưa có partition
Để tạo mới partition vào một table có sẵn dữ liệu khi ở khai báo mặc định của table lại không có partition thì cần sử dụngDBMS_REDEFINITION hoặc ALTER TABLE … EXCHANGE PARTITION.
Cả hai phương pháp này để dựa trên cách thức là sẽ tạo một bảng mới với cấu trúc giống hệt nhưng đã khai báo partition khi tạo mới và swap giữa hai bảng này. Cuối cùng sau khi đã swap xong thì sẽ thực hiện việc drop bảng tạm đi. Cụ thể như sau:
Bước 1: Khởi tạo dữ liệu sample
Giả sử ta có một bảng với dữ liệu đã có sẵn như sau:
create table thetest(
logid number,
transdate date,
status varchar2(50),
pid varchar2(50),
constraint pk_logid PRIMARY KEY(logid)
);
insert into thetest values(1,to_date(‘20151101’, ‘YYYYMMDD’), ‘1’, ‘A’);
insert into thetest values(2,to_date(‘20151001’, ‘YYYYMMDD’), ‘1’, ‘A’);
insert into thetest values(3,to_date(‘20151201’, ‘YYYYMMDD’), ‘1’, ‘A’);
Bước 2: Tạo một bảng tương tự để swap
Tạo mới một bảng sạch sẽ với cấu trúc giống hệt nhưng đã khai báo partition. Ở đây tôi dung composite partition:
create table thetest2(
logid number,
transdate date,
status varchar2(50),
pid varchar2(50),
constraint pk_logid2 PRIMARY KEY(logid))
partition by range(transdate) subpartition by list(pid)
subpartition template(
subpartition pa values(‘A’, ‘a’),
subpartition pb values(‘B’, ‘b’)
)
(
partition p201509 values less than (to_date(‘20151001′,’YYYYMMDD’)),
partition p201510 values less than (to_date(‘20151101′,’YYYYMMDD’))
)
Bước 3: Kiểm tra xem có redefinition được bảng ban đầu không
Đăng nhập bằng user SYS hoặc một user nào được gán quyền EXEC DBMS_REDEFINITION
EXEC DBMS_REDEFINITION.can_redef_table(‘SCHEMA’, ‘THETEST’);
Nếu câu lệnh trên không gây lỗi tức là có thể redefine lại bảng được với user đã khai báo
Bước 4: Ghi nhớ câu lệnh stop/abort
Là một DBA, bạn nên ghi nhớ câu stop trước khi học câu start.!
EXEC DBMS_REDEFINITION.abort_redef_table(‘SCHEMA’, ‘THETEST’, ‘THETEST2’);
Nếu quá trình organize table có vấn đề chúng ta sẽ phải thực hiện việc restart lại quá trình này nhưng bởi vì redefinition sẽ yêu cầu tạo snapshot nên phải chạy câu lênh abort_redef_table() ở trên để release snapshot trước khi bắt đầu lại.
Có thể thử bằng cách tạo ít partition ở bảng tạm hơn so với các giá trị của bảng chính. Khi này sau khi bắt đầu tiến trình thì Oracle sẽ báo lỗi do một số giá trị sẽ không biết cho vào partition nào. Lúc này giả sử rằng ta đã tạo thêm partition ở bảng tạm cho chuẩn với dữ liệu (cách thêm partition vào bảng có sẵn dữ liệu đã nêu ở trên) chạy lại câu lệnhstart_redef_table() sẽ bị lỗi:
ORA-23539: table “SCHEMA”.”THETEST” currently being redefined
Như vậy Oracle vẫn ghi nhận bảng THETSET đang được redefined nên tất nhiên sẽ ngăn cấm không cho thực hiện câu lệnh.
Bước 5: Thực hiện tiến trình
EXEC DBMS_REDEFINITION.start_redef_table(‘SCHEMA’, ‘THETEST’, ‘THETEST2’);
—
Lưu ý: sau câu lệnh này, dữ liệu đã được chuyển sang bảng THETEST2 nhưng dữ liệu trên bảng THETEST vẫn còn. Thời gian thực hiện tiến trình này không nhanh và phụ thuộc vào dữ liệu trong DB.
exec DBMS_REDEFINITION.sync_interim_table(‘SCHEMA’, ‘THETEST’, ‘THETEST2’);
Cuối cùng là kết thúc quá trình redefinition
exec DBMS_REDEFINITION.finish_redef_table(‘SCHEMA’, ‘THETEST’, ‘THETEST2’);
Sau câu lệnh này, phần bảng THETEST đã có partition đồng thời có dữ liệu đầy đủ. Bây giờ có thể drop bảng tạm THETEST2 đi do bảng này vẫn còn dữ liệu nhưng không còn cần thiết nữa.
Thời gian thực hiện tiến trình finish này khá dài nên đòi hỏi tính toán hợp lý và có sự kiên nhẫn. Thêm một vấn đề khi làm DBA là tình kiên trì quyết không bấm Cancel. Thay vì đó hãy giành thời gian chuẩn bị kỹ càng và học cách stop tiến trình.
Bước 6: Dọn dẹp tiến trình
drop table thetest2;
alter table thetest rename constraint pk_logid2 to pk_logid;
alter index pk_logid2 rename to pk_logid;
Đoạn trên đã thực hiện xóa bảng tạm, đổi tên lại các constraint và các index cho đúng với bảng cũ. Nghiên cứu và kiểm tra chính xác bảng cũ là rất quan trọng.