— 1. 监控分区表状态
— 查看分区表的分区信息
SELECT table_name, partition_name, high_value, num_rows
FROM dba_tab_partitions
WHERE table_name = ‘SALES_PARTITIONED_TABLE’;– 查看分区表的大小
SELECT segment_name, partition_name, bytes/1024/1024 AS size_mb
FROM dba_segments
WHERE segment_name = ‘SALES_PARTITIONED_TABLE’;– 2. 监控归档过程
— 查看归档表的大小
SELECT segment_name, bytes/1024/1024 AS size_mb
FROM dba_segments
WHERE segment_name = ‘SALES_ARCHIVE’;– 查看归档表的行数
SELECT COUNT(*) FROM sales_archive;– 3. 管理归档数据
— 分区表添加新分区
ALTER TABLE sales_partitioned_table ADD PARTITION p2026 VALUES LESS THAN (TO_DATE(‘2027-01-01’, ‘YYYY-MM-DD’));– 归档表分区
CREATE TABLE sales_archive_partitioned (
sale_id NUMBER,
sale_date DATE,
product_id NUMBER,
quantity NUMBER,
amount NUMBER
)
PARTITION BY RANGE (sale_date) (
PARTITION p2023 VALUES LESS THAN (TO_DATE(‘2024-01-01’, ‘YYYY-MM-DD’)),
PARTITION p2024 VALUES LESS THAN (TO_DATE(‘2025-01-01’, ‘YYYY-MM-DD’)),
PARTITION p2025 VALUES LESS THAN (TO_DATE(‘2026-01-01’, ‘YYYY-MM-DD’))
) COMPRESS FOR ARCHIVE;– 4. 自动化归档脚本
— 创建归档存储过程
CREATE OR REPLACE PROCEDURE archive_sales_data (p_year IN NUMBER) AS
v_partition_name VARCHAR2(30);v_swap_table_name VARCHAR2(30);BEGIN
v_partition_name := ‘P’ || p_year;v_swap_table_name := ‘SWAP_TABLE_’ || p_year;– 创建交换表
EXECUTE IMMEDIATE ‘CREATE TABLE ‘ || v_swap_table_name || ‘ AS SELECT * FROM sales_partitioned_table WHERE 1=0’;– 交换分区
EXECUTE IMMEDIATE ‘ALTER TABLE sales_partitioned_table EXCHANGE PARTITION ‘ || v_partition_name || ‘ WITH TABLE ‘ || v_swap_table_name;– 插入到归档表
EXECUTE IMMEDIATE ‘INSERT INTO sales_archive SELECT * FROM ‘ || v_swap_table_name;COMMIT;– 清理交换表
EXECUTE IMMEDIATE ‘DROP TABLE ‘ || v_swap_table_name;DBMS_OUTPUT.PUT_LINE(‘Archived partition ‘ || v_partition_name || ‘ successfully’);EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(‘Error: ‘ || SQLERRM);ROLLBACK;EXECUTE IMMEDIATE ‘DROP TABLE ‘ || v_swap_table_name;END;/– 执行归档
EXEC archive_sales_data(2023);