oracke数据库分区新增

1、发现一些过程执行报错，报错原因是‘ora-14400插入的分区关键字未映射到任何分区’，原来是2020年库表的时间子分区未扩展；

2、扩展子分区的语句是

ALTER TABLE 表名 MODIFY PARTITION 分区名 ADD SUBPARTITION 子分区名 VALUES (分区字段值) tablespace 表空间名;

3、批量扩展子分区，执行以下语句可自动生成扩展脚本：

SELECT DISTINCT 'ALTER TABLE ' || A.table_name || ' MODIFY PARTITION ' ||
A.partition_name || ' ADD SUBPARTITION ' ||
REPLACE(A.subpartition_name, '2019', '2020') || ' values (' ||
replace(REPLACE(A.subpartition_name,
A.partition_name || '_M',
''),
'2019',
'2020') || ') tablespace ' || A.tablespace_name || ';' sql
FROM USER_TAB_SUBPARTITIONS A
WHERE A.subpartition_name LIKE '%2019%'
AND A.table_name NOT LIKE 'BIN$%'
AND EXISTS (SELECT 1
FROM USER_PART_TABLES B
WHERE B.table_name = A.table_name
AND B.subpartitioning_type = 'LIST')
order by sql;SELECT DISTINCT 'ALTER TABLE ' || A.table_name || ' MODIFY PARTITION ' ||
A.partition_name || ' ADD SUBPARTITION ' ||
REPLACE(A.subpartition_name, '2019', '2020') ||
' values less than (to_date(''' ||
to_char(add_months(to_date(replace(REPLACE(A.subpartition_name,
A.partition_name || '_M',
''),
'2019',
'2020'),
'yyyymm'),
1),
'yyyy-mm-dd') || ''',''yyyy-mm-dd'')) tablespace ' ||
A.tablespace_name || ';' sql
FROM USER_TAB_SUBPARTITIONS A
WHERE A.subpartition_name LIKE '%2019%'
AND A.table_name NOT LIKE 'BIN$%'
AND EXISTS (SELECT 1
FROM USER_PART_TABLES B
WHERE B.table_name = A.table_name
AND B.subpartitioning_type = 'RANGE')
order by sql;