Oracle Interval Partition 自动分区表-实验

最新推荐文章于 2023-04-26 19:49:24 发布

cuiyong8723

最新推荐文章于 2023-04-26 19:49:24 发布

阅读量167

点赞数

文章标签：数据库

#自动分区表，实验
CREATE TABLE FACT_STORAGE_SHEET_PP_2
( id number,
"DATE_KEY" NUMBER(11,0)
)
PARTITION BY RANGE (DATE_KEY)
INTERVAL(1)
(
PARTITION p0 values LESS THAN (20141211)
)
;
insert into fact_storage_sheet_pp_2 values (1 ,to_char( to_date('2014/11/21','yyyy/mm/dd'),'yyyymmdd') );
insert into fact_storage_sheet_pp_2 values (2 ,to_char( to_date('2014/11/22','yyyy/mm/dd'),'yyyymmdd') );
insert into fact_storage_sheet_pp_2 values (3 ,to_char( to_date('2014/11/23','yyyy/mm/dd'),'yyyymmdd') );
insert into fact_storage_sheet_pp_2 values (4 ,to_char( to_date('2014/11/24','yyyy/mm/dd'),'yyyymmdd') );
insert into fact_storage_sheet_pp_2 values (5 ,to_char( to_date('2014/11/25','yyyy/mm/dd'),'yyyymmdd') );
insert into fact_storage_sheet_pp_2 values (6 ,to_char( to_date('2014/11/26','yyyy/mm/dd'),'yyyymmdd') );
insert into fact_storage_sheet_pp_2 values (7 ,to_char( to_date('2014/11/27','yyyy/mm/dd'),'yyyymmdd') );
insert into fact_storage_sheet_pp_2 values (8 ,to_char( to_date('2014/11/28','yyyy/mm/dd'),'yyyymmdd') );
insert into fact_storage_sheet_pp_2 values (9 ,to_char( to_date('2014/11/29','yyyy/mm/dd'),'yyyymmdd') );
insert into fact_storage_sheet_pp_2 values (10,to_char( to_date('2014/11/30','yyyy/mm/dd'),'yyyymmdd') );
insert into fact_storage_sheet_pp_2 values (11,to_char( to_date('2014/12/01','yyyy/mm/dd'),'yyyymmdd') );
insert into fact_storage_sheet_pp_2 values (12,to_char( to_date('2014/12/02','yyyy/mm/dd'),'yyyymmdd') );
insert into fact_storage_sheet_pp_2 values (13,to_char( to_date('2014/12/03','yyyy/mm/dd'),'yyyymmdd') );
insert into fact_storage_sheet_pp_2 values (14,to_char( to_date('2014/12/04','yyyy/mm/dd'),'yyyymmdd') );
insert into fact_storage_sheet_pp_2 values (15,to_char( to_date('2014/12/05','yyyy/mm/dd'),'yyyymmdd') );
insert into fact_storage_sheet_pp_2 values (16,to_char( to_date('2014/12/06','yyyy/mm/dd'),'yyyymmdd') );
insert into fact_storage_sheet_pp_2 values (17,to_char( to_date('2014/12/07','yyyy/mm/dd'),'yyyymmdd') );
insert into fact_storage_sheet_pp_2 values (18,to_char( to_date('2014/12/08','yyyy/mm/dd'),'yyyymmdd') );
insert into fact_storage_sheet_pp_2 values (19,to_char( to_date('2014/12/09','yyyy/mm/dd'),'yyyymmdd') );
insert into fact_storage_sheet_pp_2 values (20,to_char( to_date('2014/12/10','yyyy/mm/dd'),'yyyymmdd') );
insert into fact_storage_sheet_pp_2 values (21,to_char( to_date('2014/12/11','yyyy/mm/dd'),'yyyymmdd') );
insert into fact_storage_sheet_pp_2 values (22,to_char( to_date('2014/12/12','yyyy/mm/dd'),'yyyymmdd') );
insert into fact_storage_sheet_pp_2 values (23,to_char( to_date('2014/12/13','yyyy/mm/dd'),'yyyymmdd') );
insert into fact_storage_sheet_pp_2 values (24,to_char( to_date('2014/12/14','yyyy/mm/dd'),'yyyymmdd') );
insert into fact_storage_sheet_pp_2 values (25,to_char( to_date('2014/12/15','yyyy/mm/dd'),'yyyymmdd') );
insert into fact_storage_sheet_pp_2 values (26,to_char( to_date('2014/12/16','yyyy/mm/dd'),'yyyymmdd') );
insert into fact_storage_sheet_pp_2 values (27,to_char( to_date('2014/12/17','yyyy/mm/dd'),'yyyymmdd') );
insert into fact_storage_sheet_pp_2 values (28,to_char( to_date('2014/12/18','yyyy/mm/dd'),'yyyymmdd') );
insert into fact_storage_sheet_pp_2 values (29,to_char( to_date('2014/12/19','yyyy/mm/dd'),'yyyymmdd') );
insert into fact_storage_sheet_pp_2 values (30,to_char( to_date('2014/12/20','yyyy/mm/dd'),'yyyymmdd') );
insert into fact_storage_sheet_pp_2 values (31,to_char( to_date('2014/12/21','yyyy/mm/dd'),'yyyymmdd') );
insert into fact_storage_sheet_pp_2 values (32,to_char( to_date('2014/12/22','yyyy/mm/dd'),'yyyymmdd') );
insert into fact_storage_sheet_pp_2 values (33,to_char( to_date('2014/12/23','yyyy/mm/dd'),'yyyymmdd') );
insert into fact_storage_sheet_pp_2 values (34,to_char( to_date('2014/12/24','yyyy/mm/dd'),'yyyymmdd') );
insert into fact_storage_sheet_pp_2 values (35,to_char( to_date('2014/12/25','yyyy/mm/dd'),'yyyymmdd') );
insert into fact_storage_sheet_pp_2 values (36,to_char( to_date('2014/12/26','yyyy/mm/dd'),'yyyymmdd') );
insert into fact_storage_sheet_pp_2 values (37,to_char( to_date('2014/12/27','yyyy/mm/dd'),'yyyymmdd') );
insert into fact_storage_sheet_pp_2 values (38,to_char( to_date('2014/12/28','yyyy/mm/dd'),'yyyymmdd') );
insert into fact_storage_sheet_pp_2 values (39,to_char( to_date('2014/12/29','yyyy/mm/dd'),'yyyymmdd') );
insert into fact_storage_sheet_pp_2 values (40,to_char( to_date('2014/12/30','yyyy/mm/dd'),'yyyymmdd') );
insert into fact_storage_sheet_pp_2 values (41,to_char( to_date('2014/12/31','yyyy/mm/dd'),'yyyymmdd') );
insert into fact_storage_sheet_pp_2 values (42,to_char( to_date('2015/01/01','yyyy/mm/dd'),'yyyymmdd') );
insert into fact_storage_sheet_pp_2 values (43,to_char( to_date('2015/01/02','yyyy/mm/dd'),'yyyymmdd') );
insert into fact_storage_sheet_pp_2 values (44,to_char( to_date('2015/01/03','yyyy/mm/dd'),'yyyymmdd') );
insert into fact_storage_sheet_pp_2 values (45,to_char( to_date('2015/01/04','yyyy/mm/dd'),'yyyymmdd') );
insert into fact_storage_sheet_pp_2 values (46,to_char( to_date('2015/01/05','yyyy/mm/dd'),'yyyymmdd') );
insert into fact_storage_sheet_pp_2 values (47,to_char( to_date('2015/01/06','yyyy/mm/dd'),'yyyymmdd') );
insert into fact_storage_sheet_pp_2 values (48,to_char( to_date('2015/01/07','yyyy/mm/dd'),'yyyymmdd') );
insert into fact_storage_sheet_pp_2 values (49,to_char( to_date('2015/01/08','yyyy/mm/dd'),'yyyymmdd') );
insert into fact_storage_sheet_pp_2 values (50,to_char( to_date('2015/01/09','yyyy/mm/dd'),'yyyymmdd') );
insert into fact_storage_sheet_pp_2 values (51,to_char( to_date('2015/01/10','yyyy/mm/dd'),'yyyymmdd') );
insert into fact_storage_sheet_pp_2 values (52,to_char( to_date('2015/01/11','yyyy/mm/dd'),'yyyymmdd') );
insert into fact_storage_sheet_pp_2 values (53,to_char( to_date('2015/01/12','yyyy/mm/dd'),'yyyymmdd') );
insert into fact_storage_sheet_pp_2 values (54,to_char( to_date('2015/01/13','yyyy/mm/dd'),'yyyymmdd') );
insert into fact_storage_sheet_pp_2 values (55,to_char( to_date('2015/01/14','yyyy/mm/dd'),'yyyymmdd') );
insert into fact_storage_sheet_pp_2 values (56,to_char( to_date('2015/01/15','yyyy/mm/dd'),'yyyymmdd') );
insert into fact_storage_sheet_pp_2 values (57,to_char( to_date('2015/01/16','yyyy/mm/dd'),'yyyymmdd') );
insert into fact_storage_sheet_pp_2 values (58,to_char( to_date('2015/01/17','yyyy/mm/dd'),'yyyymmdd') );
insert into fact_storage_sheet_pp_2 values (59,to_char( to_date('2015/01/18','yyyy/mm/dd'),'yyyymmdd') );
insert into fact_storage_sheet_pp_2 values (60,to_char( to_date('2015/01/19','yyyy/mm/dd'),'yyyymmdd') );
insert into fact_storage_sheet_pp_2 values (61,to_char( to_date('2015/01/20','yyyy/mm/dd'),'yyyymmdd') );
insert into fact_storage_sheet_pp_2 values (62,to_char( to_date('2015/01/21','yyyy/mm/dd'),'yyyymmdd') );
insert into fact_storage_sheet_pp_2 values (63,to_char( to_date('2015/01/22','yyyy/mm/dd'),'yyyymmdd') );
insert into fact_storage_sheet_pp_2 values (64,to_char( to_date('2015/01/23','yyyy/mm/dd'),'yyyymmdd') );
insert into fact_storage_sheet_pp_2 values (65,to_char( to_date('2015/01/24','yyyy/mm/dd'),'yyyymmdd') );
insert into fact_storage_sheet_pp_2 values (66,to_char( to_date('2015/01/25','yyyy/mm/dd'),'yyyymmdd') );
insert into fact_storage_sheet_pp_2 values (67,to_char( to_date('2015/01/26','yyyy/mm/dd'),'yyyymmdd') );
insert into fact_storage_sheet_pp_2 values (68,to_char( to_date('2015/01/27','yyyy/mm/dd'),'yyyymmdd') );
insert into fact_storage_sheet_pp_2 values (69,to_char( to_date('2015/01/28','yyyy/mm/dd'),'yyyymmdd') );
insert into fact_storage_sheet_pp_2 values (70,to_char( to_date('2015/01/29','yyyy/mm/dd'),'yyyymmdd') );
insert into fact_storage_sheet_pp_2 values (71,to_char( to_date('2015/01/30','yyyy/mm/dd'),'yyyymmdd') );
insert into fact_storage_sheet_pp_2 values (72,to_char( to_date('2015/01/31','yyyy/mm/dd'),'yyyymmdd') );
insert into fact_storage_sheet_pp_2 values (73,to_char( to_date('2015/02/01','yyyy/mm/dd'),'yyyymmdd') );
insert into fact_storage_sheet_pp_2 values (74,to_char( to_date('2015/02/02','yyyy/mm/dd'),'yyyymmdd') );
insert into fact_storage_sheet_pp_2 values (75,to_char( to_date('2015/02/03','yyyy/mm/dd'),'yyyymmdd') );
commit;
commit;
--查看分区
select
table_name ,
partition_name ,
high_value
from user_tab_partitions
where table_name = 'FACT_STORAGE_SHEET_PP_2'
order by 2;
-- and partition_name like 'SYS%'
--分区重命名
declare
v_sql varchar(400);
v_table_name user_tab_partitions.table_name%type;
v_partition_name user_tab_partitions.partition_name%type;
v_high_value varchar(200);
v_tmp_partition_name user_tab_partitions.partition_name%type;
cursor cur is
select
table_name ,
partition_name ,
high_value
from user_tab_partitions
where partition_name like 'SYS%' and table_name = 'FACT_STORAGE_SHEET_PP_2' ;
begin
open cur;
loop
fetch cur into v_table_name,v_partition_name,v_high_value;
exit when cur%notfound;
v_tmp_partition_name := to_char(to_date(v_high_value - 1, 'yyyymmdd'),'yyyymmdd');
v_sql := 'alter table '|| v_table_name ||' rename partition '
||v_partition_name
||' to P' || v_tmp_partition_name;
dbms_output.put_line( v_sql );
execute immediate v_sql;
end loop;
close cur;
end;
/
--查询分区记录
select * from FACT_STORAGE_SHEET_PP_2 partition(P20150101);