Oracle数据库：分区识别与自动化删除方案咨询

一、识别带分区的表 #

使用系统视图dba_part_tables查询所有分区表，指定用户可缩小范围：

SELECT owner, table_name, partition_count
FROM dba_part_tables
-- WHERE owner = 'YOUR_SCHEMA' -- 替换为实际用户名，可选过滤
ORDER BY owner, table_name;

二、查询需清理的分区 #

1. 创建于30天前的分区 #

结合dba_tab_partitions和dba_part_tables筛选：

SELECT 
  p.owner,
  p.table_name,
  p.partition_name,
  TO_CHAR(p.created, 'YYYY-MM-DD HH24:MI:SS') AS create_time,
  p.tablespace_name
FROM dba_tab_partitions p
JOIN dba_part_tables t 
  ON p.owner = t.owner AND p.table_name = t.table_name
WHERE p.created <= SYSDATE - 30
-- AND p.owner = 'YOUR_SCHEMA' -- 可选过滤用户
ORDER BY p.created;

2. 接近满额的分区 #

通过dba_segments计算分区空间使用率，阈值可自行调整（示例为90%）：

SELECT 
  p.owner,
  p.table_name,
  p.partition_name,
  p.tablespace_name,
  ROUND(s.bytes / 1024 / 1024, 2) AS total_mb,
  ROUND((s.bytes - s.free_space) / 1024 / 1024, 2) AS used_mb,
  ROUND(((s.bytes - s.free_space) / s.bytes) * 100, 2) AS usage_pct
FROM dba_tab_partitions p
JOIN (
  SELECT 
    owner, 
    segment_name, 
    partition_name, 
    bytes,
    (SELECT SUM(bytes) FROM dba_free_space f WHERE f.tablespace_name = s.tablespace_name) AS free_space
  FROM dba_segments s
  WHERE segment_type = 'TABLE PARTITION'
) s 
  ON p.owner = s.owner 
  AND p.table_name = s.segment_name 
  AND p.partition_name = s.partition_name
WHERE ROUND(((s.bytes - s.free_space) / s.bytes) * 100, 2) >= 90 -- 调整使用率阈值
-- AND p.owner = 'YOUR_SCHEMA' -- 可选过滤用户
ORDER BY usage_pct DESC;

三、删除分区的操作与依赖处理 #

1. 基础删除语句 #

删除分区时同步维护关联索引，避免索引失效：

-- 删除单个分区
ALTER TABLE YOUR_SCHEMA.YOUR_TABLE DROP PARTITION YOUR_PARTITION UPDATE INDEXES;

2. 依赖问题处理 #

• 索引依赖：使用UPDATE INDEXES参数自动删除对应索引分区，确保索引可用；若需保留索引，需提前重建。
• 外键/视图依赖：先确认关联数据已清理，或临时禁用外键约束，删除后恢复；视图若依赖分区表，需确认视图逻辑不受影响。
• 权限验证：SYSDBA权限默认可执行删除操作，若用普通用户，需授予ALTER TABLE权限。

四、调度作业实现（自动清理） #

1. 创建清理存储过程 #

封装分区查询与删除逻辑，添加异常处理：

CREATE OR REPLACE PROCEDURE clean_target_partitions AS
BEGIN
  -- 清理30天前的分区
  FOR rec IN (
    SELECT 
      p.owner,
      p.table_name,
      p.partition_name
    FROM dba_tab_partitions p
    JOIN dba_part_tables t 
      ON p.owner = t.owner AND p.table_name = t.table_name
    WHERE p.created <= SYSDATE - 30
    AND p.owner = 'YOUR_SCHEMA'
  ) LOOP
    EXECUTE IMMEDIATE 'ALTER TABLE ' || rec.owner || '.' || rec.table_name || ' DROP PARTITION ' || rec.partition_name || ' UPDATE INDEXES';
  END LOOP;

  -- 清理使用率超90%的分区
  FOR rec IN (
    SELECT 
      p.owner,
      p.table_name,
      p.partition_name
    FROM dba_tab_partitions p
    JOIN (
      SELECT 
        owner, 
        segment_name, 
        partition_name, 
        bytes,
        (SELECT SUM(bytes) FROM dba_free_space f WHERE f.tablespace_name = s.tablespace_name) AS free_space
      FROM dba_segments s
      WHERE segment_type = 'TABLE PARTITION'
    ) s 
      ON p.owner = s.owner 
      AND p.table_name = s.segment_name 
      AND p.partition_name = s.partition_name
    WHERE ROUND(((s.bytes - s.free_space) / s.bytes) * 100, 2) >= 90
    AND p.owner = 'YOUR_SCHEMA'
  ) LOOP
    EXECUTE IMMEDIATE 'ALTER TABLE ' || rec.owner || '.' || rec.table_name || ' DROP PARTITION ' || rec.partition_name || ' UPDATE INDEXES';
  END LOOP;
EXCEPTION
  WHEN OTHERS THEN
    -- 可添加日志记录逻辑，比如写入自定义日志表
    DBMS_OUTPUT.PUT_LINE('清理失败: ' || SQLERRM);
END;
/

2. 创建定时调度作业 #

用DBMS_SCHEDULER设置每日自动执行（示例为凌晨2点）：

BEGIN
  DBMS_SCHEDULER.CREATE_JOB (
    job_name        => 'CLEAN_PARTITIONS_DAILY',
    job_type        => 'STORED_PROCEDURE',
    job_action      => 'clean_target_partitions',
    start_date      => SYSDATE,
    repeat_interval => 'FREQ=DAILY; BYHOUR=2; BYMINUTE=0;', -- 调整执行时间
    enabled         => TRUE,
    comments        => '每日自动清理老旧或满额的表分区'
  );
END;
/

3. 作业管理 #

• 查看作业状态：SELECT job_name, enabled, last_start_date FROM dba_scheduler_jobs WHERE job_name = 'CLEAN_PARTITIONS_DAILY';
• 手动执行作业：EXEC DBMS_SCHEDULER.RUN_JOB('CLEAN_PARTITIONS_DAILY');
• 禁用作业：EXEC DBMS_SCHEDULER.DISABLE('CLEAN_PARTITIONS_DAILY');

内容的提问来源于stack exchange，提问作者Raichen