使用ALTER TABLE…DROP PARTITION命令删除表分区及其数据-云原生数据库 PolarDB-阿里云

ALTER TABLE ... DROP PARTITION 用于从一个分区表中删除指定的分区及其包含的全部数据。此操作会永久移除数据且不可恢复，执行前需确认目标分区已备份或不再需要。

语法介绍

ALTER TABLE [schema.]table_name DROP PARTITION partition_name;

参数说明

参数名称	参数要求	参数说明	参数示例
`table_name`	必填	要删除分区的目标分区表的名称。	`sales_records`
`partition_name`	必填	要删除的分区的名称。	`p_2023_q1`

注意事项

执行此命令的用户需是该表的所有者或者是高权限用户。
不能删除最后一个分区，目标分区表至少要保留一个分区。
DROP PARTITION 会获取 AccessExclusiveLock（表级排他锁），阻塞该表的所有 DML 和大部分 DDL 操作。建议在业务低峰期执行，并预留足够的时间窗口。

使用示例

环境准备

-- 创建一个按销售日期范围分区的表
CREATE TABLE sales_records (
    sale_id    INT NOT NULL,
    product_id INT NOT NULL,
    sale_date  DATE NOT NULL
)
PARTITION BY RANGE (sale_date) (
    PARTITION P_2023_Q1 VALUES LESS THAN (TO_DATE('2023-04-01', 'YYYY-MM-DD')),
    PARTITION P_2023_Q2 VALUES LESS THAN (TO_DATE('2023-07-01', 'YYYY-MM-DD'))
);

-- 创建一个全局唯一索引
CREATE UNIQUE INDEX idx_sale_id_global ON sales_records(sale_id) GLOBAL;

-- 插入数据
INSERT INTO sales_records VALUES (1, 101, TO_DATE('2023-01-15', 'YYYY-MM-DD')); -- q1
INSERT INTO sales_records VALUES (2, 102, TO_DATE('2023-04-20', 'YYYY-MM-DD')); -- q2

前置检查

删除任何分区之前，建议完成以下检查和准备工作，以防止数据误删和业务意外中断。

确认分区存在。

-- 检查分区信息
SELECT PARTITION_NAME FROM USER_TAB_PARTITIONS WHERE TABLE_NAME = 'SALES_RECORDS';
-- 确认 p_2023_q1 在列表中

（可选）创建数据备份。
为防止误操作，建议在执行删除前对表或整个数据库进行备份，详情请参考备份操作说明。
评估业务影响。
检查当前是否有正在访问该表的长事务或关键业务。删除操作会请求表级排他锁，阻塞所有读写请求。
```
-- 查看当前是否有活跃的锁
SELECT locktype, relation::regclass, mode, granted, pid
FROM pg_locks
WHERE relation = 'SALES_RECORDS'::regclass;
```

执行操作

完成所有检查和准备后，在业务低峰期执行 ALTER TABLE ... DROP PARTITION 命令，删除指定分区。

ALTER TABLE sales_records DROP PARTITION P_2023_Q1;

结果验证

操作完成后，需验证分区和数据是否已按预期删除。

检查分区是否已被移除

查询元数据视图，确认分区列表中已不存在被删除的分区。

SELECT partition_name
FROM ALL_TAB_PARTITIONS
WHERE table_name = 'SALES_RECORDS' and partition_name = 'P_2023_Q1';
-- 预期结果：返回为空。

确认数据已被删除

尝试查询原分区对应的数据，确认查询结果为空。

SELECT * 
FROM sales_records 
WHERE sale_date < '2023-04-01';
-- 预期结果：返回为空。

更新表统计信息
删除分区后，建议立即更新表的统计信息，以确保查询优化器能够生成准确的执行计划。
```
ANALYZE SALES_RECORDS;
```

常见问题

Q1：执行删除分区后，报partition "..." of relation "..." does not exist异常。

分区名不存在或大小写不匹配。分区名大小写敏感，请先查询 USER_TAB_PARTITIONS确认分区名称，或使用双引号精确引用。

Q2：执行删除分区后，报permission denied for table ... 或 must be owner of table ...异常。

执行操作的用户权限不足，需切换到表所有者或高权限用户执行。

Q3：执行删除分区后，报cannot drop partition due to dependent objects异常。

存在其他数据库对象（如外键约束、视图）依赖于此分区。默认的 RESTRICT 行为会阻止删除。可以手动识别并删除这些依赖对象。

Q4：操作长时间未完成或卡住。

执行 DROP PARTITION 时，数据库会请求 AccessExclusiveLock 锁。若该锁被其他活跃事务阻塞，操作将进入等待状态，可能导致超时或执行延迟。可通过查询锁信息获取会话（blocking_pid），在通过以下语句查询具体会话信息。

-- 根据pid查询阻塞线程的用户信息
SELECT 
    pid,
    usename AS username,           -- 数据库用户名
    application_name,              -- 应用名称（如 JDBC, psql）
    client_addr,                   -- 客户端 IP 地址
    client_hostname,               -- 客户端主机名
    client_port,                   -- 客户端端口
    backend_start,                 -- 连接开始时间
    xact_start,                    -- 事务开始时间
    query_start,                   -- 当前查询开始时间
    state_change,                  -- 状态变更时间
    state,                         -- 状态（active, idle, idle in transaction）
    wait_event_type,               -- 等待类型（如 Lock, IO）
    wait_event,                    -- 具体等待事件
    query                          -- 当前正在执行或最近执行的 SQL
FROM 
    pg_stat_activity
WHERE 
    pid in 'blocking_pid';