為了清理數據，往往需要刪除一些分區
簡單查看當前分區

附件

--創建測試表
--  drop table test_part purge;CREATE TABLE test_part (sales_id NUMBER,sale_date DATE,amount NUMBER
)
PARTITION BY RANGE (sale_date)
INTERVAL (INTERVAL '1' MONTH)  -- 每個月創建一個分區
(PARTITION p_initial VALUES LESS THAN (TO_DATE('2025-01-01', 'YYYY-MM-DD'))  -- 初始分區，包含2025年1月之前的數據
);--插入測試數據
insert into test_part values(1,sysdate-500,10);
insert into test_part values(2,sysdate-30,20);
insert into test_part values(3,sysdate+20,30);
commit;--顯示表結構
desc TEST_PART--顯示表定義
set pages 100
SET LONG 999 LONGCHU 10000
SELECT DBMS_METADATA.GET_DDL('TABLE', 'TEST_PART', 'SYS') FROM DUAL;--查看當前分區
set line 200
col table_name for a12
col TABLESPACE_NAME for a12
col PARTITION_NAME for a12
col HIGH_VALUE for a88
select TABLE_NAME,PARTITION_NAME,HIGH_VALUE,PARTITION_POSITION,TABLESPACE_NAME,NUM_ROWS from DBA_tab_partitions where TABLE_NAME='TEST_PART';--查看分區high_value
col PARTITION for a20
with xml as (
select dbms_xmlgen.getxmltype('select table_name, partition_name, high_value from user_tab_partitions where table_name = ''TEST_PART''') as x
from dual)
select extractValue(rws.object_value, '/ROW/TABLE_NAME') table_name,
extractValue(rws.object_value, '/ROW/PARTITION_NAME') partition,
extractValue(rws.object_value, '/ROW/HIGH_VALUE') high_value
from xml x,table(xmlsequence(extract(x.x, '/ROWSET/ROW'))) rws ORDER BY extractValue(rws.object_value, '/ROW/HIGH_VALUE');--不是很嚴謹的過濾（extractValue的結果該用什么轉換為日期呢？）
select table_name,partition,   high_value    from (with xml as (select dbms_xmlgen.getxmltype('select table_name, partition_name, high_value from user_tab_partitions where table_name = ''TEST_PART''') as xfrom dual)select extractValue(rws.object_value, '/ROW/TABLE_NAME') table_name,extractValue(rws.object_value, '/ROW/PARTITION_NAME') partition,extractValue(rws.object_value, '/ROW/HIGH_VALUE') high_valuefrom xml x,table(xmlsequence(extract(x.x, '/ROWSET/ROW'))) rws ORDER BY extractValue(rws.object_value, '/ROW/HIGH_VALUE')) where high_value>'TO_DATE('' 2025-04-01' ;

過濾掉想要的分區