oracle分区

分区字段是不允许进行update操作的，如果有对分区字段行进update，就会报错——ORA-14402:更新分区关键字列将导致分区的更改。  可以通过打开表的row movement属性来允许对分区字段的update操作。

?

1	alter table test_part enable row movement;

分区里有max

alter table dc_admissions
split partition ADMISSIONS_MAX at (TO_DATE(' 2024-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
into (partition ADMISSIONS_2023, partition ADMISSIONS_MAX)

查询分区内的数据

select count(*) as 记录数 from 表名 partition(分区名) ;

select* from dc_admissions partition(ADMISSIONS_2023) ;

多个分区 （这个没有好的方法查询）

select * from T_FQ_TEST PARTITION (PART_01)
union all
select * from T_FQ_TEST PARTITION (PART_02);

查看当前分区

select t.partition_name,t.num_rows from all_tab_partitions t where table_name='DC_ADMISSIONS'

批量增加表分区

SELECT   'ALTER TABLE '||a.table_name||' ADD PARTITION PART_'||'202201'||' VALUES LESS THAN (TO_DATE(''2022-02-01 00:00:00'',''yyyy-mm-dd hh24:mi:ss''));'
 FROM DBA_PART_TABLES A where owner IN ('NEWODS') and table_name not like '%$%'
 UNION ALL
 SELECT   'ALTER TABLE '||a.table_name||' ADD PARTITION PART_'||'202202'||' VALUES LESS THAN (TO_DATE(''2022-03-01 00:00:00'',''yyyy-mm-dd hh24:mi:ss''));'
 FROM DBA_PART_TABLES A where owner IN ('NEWODS') and table_name not like '%$%'
 UNION ALL
 SELECT   'ALTER TABLE '||a.table_name||' ADD PARTITION PART_'||'202203'||' VALUES LESS THAN (TO_DATE(''2022-04-01 00:00:00'',''yyyy-mm-dd hh24:mi:ss''));'
 FROM DBA_PART_TABLES A where owner IN ('NEWODS') and table_name not like '%$%'
 UNION ALL
 SELECT   'ALTER TABLE '||a.table_name||' ADD PARTITION PART_'||'202204'||' VALUES LESS THAN (TO_DATE(''2022-05-01 00:00:00'',''yyyy-mm-dd hh24:mi:ss''));'
 FROM DBA_PART_TABLES A where owner IN ('NEWODS') and table_name not like '%$%'
 UNION ALL
 SELECT   'ALTER TABLE '||a.table_name||' ADD PARTITION PART_'||'202205'||' VALUES LESS THAN (TO_DATE(''2022-06-01 00:00:00'',''yyyy-mm-dd hh24:mi:ss''));'
 FROM DBA_PART_TABLES A where owner IN ('NEWODS') and table_name not like '%$%'
 UNION ALL
 SELECT   'ALTER TABLE '||a.table_name||' ADD PARTITION PART_'||'202206'||' VALUES LESS THAN (TO_DATE(''2022-07-01 00:00:00'',''yyyy-mm-dd hh24:mi:ss''));'
 FROM DBA_PART_TABLES A where owner IN ('NEWODS') and table_name not like '%$%'
 UNION ALL
 SELECT   'ALTER TABLE '||a.table_name||' ADD PARTITION PART_'||'202207'||' VALUES LESS THAN (TO_DATE(''2022-08-01 00:00:00'',''yyyy-mm-dd hh24:mi:ss''));'
 FROM DBA_PART_TABLES A where owner IN ('NEWODS') and table_name not like '%$%'
 UNION ALL
 SELECT   'ALTER TABLE '||a.table_name||' ADD PARTITION PART_'||'202208'||' VALUES LESS THAN (TO_DATE(''2022-09-01 00:00:00'',''yyyy-mm-dd hh24:mi:ss''));'
 FROM DBA_PART_TABLES A where owner IN ('NEWODS') and table_name not like '%$%'
 UNION ALL
 SELECT   'ALTER TABLE '||a.table_name||' ADD PARTITION PART_'||'202209'||' VALUES LESS THAN (TO_DATE(''2022-10-01 00:00:00'',''yyyy-mm-dd hh24:mi:ss''));'
 FROM DBA_PART_TABLES A where owner IN ('NEWODS') and table_name not like '%$%'
 UNION ALL
 SELECT   'ALTER TABLE '||a.table_name||' ADD PARTITION PART_'||'202210'||' VALUES LESS THAN (TO_DATE(''2022-11-01 00:00:00'',''yyyy-mm-dd hh24:mi:ss''));'
 FROM DBA_PART_TABLES A where owner IN ('NEWODS') and table_name not like '%$%'
 UNION ALL
 SELECT   'ALTER TABLE '||a.table_name||' ADD PARTITION PART_'||'202211'||' VALUES LESS THAN (TO_DATE(''2022-12-01 00:00:00'',''yyyy-mm-dd hh24:mi:ss''));'
 FROM DBA_PART_TABLES A where owner IN ('NEWODS') and table_name not like '%$%'
 UNION ALL
 SELECT   'ALTER TABLE '||a.table_name||' ADD PARTITION PART_'||'202212'||' VALUES LESS THAN (TO_DATE(''2023-01-01 00:00:00'',''yyyy-mm-dd hh24:mi:ss''));'
 FROM DBA_PART_TABLES A where owner IN ('NEWODS') and table_name not like '%$%'