[Oracle DB] Vấn đề về lưu trữ: Thủ tục di chuyển bảng partition sang tablespace khác
## Với các tình huống chuyển đổi lớn, ta có thể áp dụng phương pháp move tablespace - chuyển các bảng sang tablespace mới, tương ứng với các datafile mới không bị phân mảnh, không high water mark -> Trong quá trình này, ta thường gặp khó khăn khi di chuyển các bảng partitioned vì:
-- Partition thường được hiểu là "phân vùng", điều này không sai nhưng bản chất mỗi partition có thể coi là một bảng con, tầng instance của Oracle DB sẽ thu gom các bảng con này và biểu diễn cho chúng ta thấy một bảng duy nhất. Như vậy, ta có thể hiểu hiệu suất cao của bảng partitioned so với non-partition đến từ việc câu lệnh SQL sẽ làm việc với hữu hạn các bảng con thay vì làm việc với toàn bộ bảng.
-- Tương tự, việc move tablespace đối với bảng partitioned chính là di chuyển hàng loạt các bảng từ tablespace cũ đến tablespace mới.
-- Ngoài ra nếu bảng có tồn tại index mà bản chất là nhiều index trên nhiều bảng con, chúng ta cần rebuild lại index sau khi chuyển bảng sang tablespace mới.
## Thủ tục sử dụng vòng lặp di chuyển các partition của bảng:
BEGIN
DBMS_SCHEDULER.CREATE_JOB(
job_name => 'MOVE_TABLESPACE_03',
job_type => 'PLSQL_BLOCK',
job_action => q'[
DECLARE
v_new_tablespace VARCHAR2(50) := 'DATA1'; -- Tên của tablespace mới
BEGIN
FOR partition_rec IN (
SELECT table_owner, table_name, partition_name
FROM all_tab_partitions
WHERE table_owner = 'OAZ21' -- Tên chủ sở hữu của bảng
AND table_name = 'TABLE2' -- Tên của bảng
AND tablespace_name <> v_new_tablespace -- Loại trừ các partition đã ở trong tablespace mới
) LOOP
EXECUTE IMMEDIATE 'ALTER TABLE ' || partition_rec.table_owner || '.' || partition_rec.table_name ||
' MOVE PARTITION ' || partition_rec.partition_name ||
' TABLESPACE ' || v_new_tablespace;
END LOOP;
END;
]',
start_date => SYSTIMESTAMP,
repeat_interval => NULL,
end_date => NULL,
enabled => TRUE,
auto_drop => TRUE,
comments => 'no comment'
);
END;
/## Sau khi chạy thủ tục, ta có thể kiểm tra các partition của bảng TABLE1 đã nằm trên tablespace mới (DATA1). Trong thực tế, thủ thuật này thường được sử dụng khi tablespace cũ đang có High water mark cao, không có cách nào để hạ -> Tạo tablespace mới để chứa các bảng cần thiết và xóa tablespace + datafile cũ để lấy lại dung lượng.
## Khi này, ta kiểm tra lại các index của bảng thì các index đã UNUSABLE. Trong hình có 1 index vẫn USABLE là do trong phân vùng đó không chứa bản ghi nào:
SELECT *FROM USER_IND_PARTITIONS;
## Ta cũng có thể quan sát hiện tượng này thông qua alert_log:
## Thủ tục rebuild hàng loạt các index của bảng này:
BEGIN
DBMS_SCHEDULER.CREATE_JOB(
job_name => 'REBUILD_INDEX_PAR_02',
job_type => 'PLSQL_BLOCK',
job_action => q'[
BEGIN
FOR index_rec IN (
SELECT INDEX_NAME, PARTITION_NAME
FROM USER_IND_PARTITIONS
WHERE INDEX_NAME = 'IDX_TABLE2'
AND STATUS = 'UNUSABLE'
)
LOOP
BEGIN
EXECUTE IMMEDIATE 'ALTER INDEX ' || index_rec.INDEX_NAME || ' REBUILD PARTITION ' || index_rec.PARTITION_NAME || ' NOLOGGING ONLINE';
DBMS_OUTPUT.PUT_LINE('Chỉ mục ' || index_rec.INDEX_NAME || ' trên phân vùng ' || index_rec.PARTITION_NAME || ' đã được rebuild thành công.');
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Đã xảy ra lỗi khi rebuild chỉ mục ' || index_rec.INDEX_NAME || ' trên phân vùng ' || index_rec.PARTITION_NAME || ': ' || SQLERRM);
END;
END LOOP;
END;
]',
start_date => SYSTIMESTAMP,
repeat_interval => NULL,
end_date => NULL,
enabled => TRUE,
auto_drop => TRUE,
comments => ''
);
END;
/## Sau đó kiểm tra lại các index của bảng này, trạng thái đã trở về USABLE:
## Tóm lại, để di chuyển một bảng partitioned sang tablespace khác, ta cần:
- Thủ tục di chuyển hàng loạt partititon.
- Thủ tục rebuild hàng loạt index
## Toàn bộ code:
CREATE TABLESPACE DATA1 DATAFILE '/u01/app/oracle/oradata/CDB1/pdb1/data1.dbf' SIZE 256M AUTOEXTEND ON NEXT 64M MAXSIZE UNLIMITED;
DROP TABLESPACE DATA1 INCLUDING CONTENTS AND DATAFILES;
--Di chuyen bang parrtition sang tablespace moi
DECLARE
v_new_tablespace VARCHAR2(50) := 'DATA1'; -- Tên c?a tablespace m?i
BEGIN
FOR partition_rec IN (
SELECT table_owner, table_name, partition_name
FROM all_tab_partitions
WHERE table_owner = 'OAZ21' -- Tên ch? s? h?u c?a b?ng
AND table_name = 'TABLE2' -- Tên c?a b?ng
AND tablespace_name <> v_new_tablespace -- Lo?i tr? các partition dã ? trong tablespace m?i
) LOOP
EXECUTE IMMEDIATE 'ALTER TABLE ' || partition_rec.table_owner || '.' || partition_rec.table_name ||
' MOVE PARTITION ' || partition_rec.partition_name ||
' TABLESPACE ' || v_new_tablespace;
END LOOP;
END;
/
BEGIN
DBMS_SCHEDULER.CREATE_JOB(
job_name => 'MOVE_TABLESPACE_05',
job_type => 'PLSQL_BLOCK',
job_action => q'[
DECLARE
v_new_tablespace VARCHAR2(50) := 'DATA1'; -- Tên c?a tablespace m?i
BEGIN
FOR partition_rec IN (
SELECT table_owner, table_name, partition_name
FROM all_tab_partitions
WHERE table_owner = 'OAZ21' -- Tên ch? s? h?u c?a b?ng
AND table_name = 'TABLE2' -- Tên c?a b?ng
AND tablespace_name <> v_new_tablespace -- Lo?i tr? các partition dã ? trong tablespace m?i
) LOOP
EXECUTE IMMEDIATE 'ALTER TABLE ' || partition_rec.table_owner || '.' || partition_rec.table_name ||
' MOVE PARTITION ' || partition_rec.partition_name ||
' TABLESPACE ' || v_new_tablespace;
END LOOP;
END;
]',
start_date => SYSTIMESTAMP,
repeat_interval => NULL,
end_date => NULL,
enabled => TRUE,
auto_drop => TRUE,
comments => 'no comment'
);
END;
/
--Index
SELECT * FROM USER_IND_PARTITIONS;
BEGIN
DBMS_SCHEDULER.CREATE_JOB(
job_name => 'INDEX_PARALLEL_02',
job_type => 'PLSQL_BLOCK',
job_action => q'[
BEGIN
EXECUTE IMMEDIATE '
create index oaz21.idx_table2 on oaz21.table2(phone,create_date) local parallel 8 nologging online tablespace INDX2024
';
END;
]',
start_date => SYSTIMESTAMP,
repeat_interval => NULL,
end_date => NULL,
enabled => TRUE,
auto_drop => TRUE,
comments => 'no comment'
);
END;
/
--set no parallel
BEGIN
DBMS_SCHEDULER.CREATE_JOB(
job_name => 'INDEX_NOPARALLEL_05',
job_type => 'PLSQL_BLOCK',
job_action => q'[
DECLARE
CURSOR c1 IS
SELECT DISTINCT index_name
FROM USER_IND_PARTITIONS
WHERE index_name = 'IDX_TABLE2'
AND status = 'USABLE';
BEGIN
FOR i1 IN c1 LOOP
EXECUTE IMMEDIATE 'ALTER INDEX oaz21.' || i1.index_name || ' NOLOGGING NOPARALLEL';
END LOOP;
END;
]',
start_date => SYSTIMESTAMP,
repeat_interval => NULL,
end_date => NULL,
enabled => TRUE,
auto_drop => TRUE,
comments => 'no comment'
);
END;
/
--Rebuild index hang loat cho partition
BEGIN
FOR index_rec IN (
SELECT INDEX_NAME, PARTITION_NAME
FROM USER_IND_PARTITIONS
WHERE INDEX_NAME = 'IDX_TABLE2'
AND STATUS = 'UNUSABLE'
)
LOOP
BEGIN
EXECUTE IMMEDIATE 'ALTER INDEX ' || index_rec.INDEX_NAME || ' REBUILD PARTITION ' || index_rec.PARTITION_NAME || ' PARALLEL 8 NOLOGGING ONLINE';
DBMS_OUTPUT.PUT_LINE('Chỉ mục ' || index_rec.INDEX_NAME || ' trên phân vùng ' || index_rec.PARTITION_NAME || ' đã được rebuild thành công.');
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Đã xảy ra lỗi khi rebuild chỉ mục ' || index_rec.INDEX_NAME || ' trên phân vùng ' || index_rec.PARTITION_NAME || ': ' || SQLERRM);
END;
END LOOP;
END;
/
BEGIN
DBMS_SCHEDULER.CREATE_JOB(
job_name => 'REBUILD_INDEX_PAR_02',
job_type => 'PLSQL_BLOCK',
job_action => q'[
BEGIN
FOR index_rec IN (
SELECT INDEX_NAME, PARTITION_NAME
FROM USER_IND_PARTITIONS
WHERE INDEX_NAME = 'IDX_TABLE2'
AND STATUS = 'UNUSABLE'
)
LOOP
BEGIN
EXECUTE IMMEDIATE 'ALTER INDEX ' || index_rec.INDEX_NAME || ' REBUILD PARTITION ' || index_rec.PARTITION_NAME || ' PARALLEL 8 NOLOGGING ONLINE';
DBMS_OUTPUT.PUT_LINE('Chỉ mục ' || index_rec.INDEX_NAME || ' trên phân vùng ' || index_rec.PARTITION_NAME || ' đã được rebuild thành công.');
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Đã xảy ra lỗi khi rebuild chỉ mục ' || index_rec.INDEX_NAME || ' trên phân vùng ' || index_rec.PARTITION_NAME || ': ' || SQLERRM);
END;
END LOOP;
END;
]',
start_date => SYSTIMESTAMP,
repeat_interval => NULL,
end_date => NULL,
enabled => TRUE,
auto_drop => TRUE,
comments => ''
);
END;
/
--Gather
BEGIN
DBMS_SCHEDULER.CREATE_JOB(
job_name => 'GATHER_11',
job_type => 'PLSQL_BLOCK',
job_action => q'[
DECLARE
l_table_name VARCHAR2 (30) := 'TABLE2';
BEGIN
DBMS_STATS.GATHER_TABLE_STATS (
ownname => 'OAZ21',
tabname => l_table_name,
method_opt => 'FOR ALL COLUMNS SIZE AUTO',
estimate_percent => NULL,
granularity => 'ALL',
cascade => TRUE
);
END;
]',
start_date => SYSTIMESTAMP,
repeat_interval => NULL,
end_date => NULL,
enabled => TRUE,
auto_drop => TRUE,
comments => ''
);
END;
/===
Nhận xét
Đăng nhận xét