ITPub博客

首页 > 数据库 > Oracle > Oracle Interval Partition 自动分区表-实验

Oracle Interval Partition 自动分区表-实验

原创 Oracle 作者:fjzcau 时间:2015-12-24 20:23:27 0 删除 编辑

  1. #自动分区表,实验

  2. CREATE TABLE FACT_STORAGE_SHEET_PP_2
  3. ( id number,
  4.   "DATE_KEY" NUMBER(11,0)
  5.  )
  6. PARTITION BY RANGE (DATE_KEY)
  7. INTERVAL(1)
  8. (
  9.   PARTITION p0 values LESS THAN (20141211)
  10. )
  11. ;


  12. insert into fact_storage_sheet_pp_2 values (1 ,to_char( to_date('2014/11/21','yyyy/mm/dd'),'yyyymmdd') );
  13. insert into fact_storage_sheet_pp_2 values (2 ,to_char( to_date('2014/11/22','yyyy/mm/dd'),'yyyymmdd') );
  14. insert into fact_storage_sheet_pp_2 values (3 ,to_char( to_date('2014/11/23','yyyy/mm/dd'),'yyyymmdd') );
  15. insert into fact_storage_sheet_pp_2 values (4 ,to_char( to_date('2014/11/24','yyyy/mm/dd'),'yyyymmdd') );
  16. insert into fact_storage_sheet_pp_2 values (5 ,to_char( to_date('2014/11/25','yyyy/mm/dd'),'yyyymmdd') );
  17. insert into fact_storage_sheet_pp_2 values (6 ,to_char( to_date('2014/11/26','yyyy/mm/dd'),'yyyymmdd') );
  18. insert into fact_storage_sheet_pp_2 values (7 ,to_char( to_date('2014/11/27','yyyy/mm/dd'),'yyyymmdd') );
  19. insert into fact_storage_sheet_pp_2 values (8 ,to_char( to_date('2014/11/28','yyyy/mm/dd'),'yyyymmdd') );
  20. insert into fact_storage_sheet_pp_2 values (9 ,to_char( to_date('2014/11/29','yyyy/mm/dd'),'yyyymmdd') );
  21. insert into fact_storage_sheet_pp_2 values (10,to_char( to_date('2014/11/30','yyyy/mm/dd'),'yyyymmdd') );
  22. insert into fact_storage_sheet_pp_2 values (11,to_char( to_date('2014/12/01','yyyy/mm/dd'),'yyyymmdd') );
  23. insert into fact_storage_sheet_pp_2 values (12,to_char( to_date('2014/12/02','yyyy/mm/dd'),'yyyymmdd') );
  24. insert into fact_storage_sheet_pp_2 values (13,to_char( to_date('2014/12/03','yyyy/mm/dd'),'yyyymmdd') );
  25. insert into fact_storage_sheet_pp_2 values (14,to_char( to_date('2014/12/04','yyyy/mm/dd'),'yyyymmdd') );
  26. insert into fact_storage_sheet_pp_2 values (15,to_char( to_date('2014/12/05','yyyy/mm/dd'),'yyyymmdd') );
  27. insert into fact_storage_sheet_pp_2 values (16,to_char( to_date('2014/12/06','yyyy/mm/dd'),'yyyymmdd') );
  28. insert into fact_storage_sheet_pp_2 values (17,to_char( to_date('2014/12/07','yyyy/mm/dd'),'yyyymmdd') );
  29. insert into fact_storage_sheet_pp_2 values (18,to_char( to_date('2014/12/08','yyyy/mm/dd'),'yyyymmdd') );
  30. insert into fact_storage_sheet_pp_2 values (19,to_char( to_date('2014/12/09','yyyy/mm/dd'),'yyyymmdd') );
  31. insert into fact_storage_sheet_pp_2 values (20,to_char( to_date('2014/12/10','yyyy/mm/dd'),'yyyymmdd') );
  32. insert into fact_storage_sheet_pp_2 values (21,to_char( to_date('2014/12/11','yyyy/mm/dd'),'yyyymmdd') );
  33. insert into fact_storage_sheet_pp_2 values (22,to_char( to_date('2014/12/12','yyyy/mm/dd'),'yyyymmdd') );
  34. insert into fact_storage_sheet_pp_2 values (23,to_char( to_date('2014/12/13','yyyy/mm/dd'),'yyyymmdd') );
  35. insert into fact_storage_sheet_pp_2 values (24,to_char( to_date('2014/12/14','yyyy/mm/dd'),'yyyymmdd') );
  36. insert into fact_storage_sheet_pp_2 values (25,to_char( to_date('2014/12/15','yyyy/mm/dd'),'yyyymmdd') );
  37. insert into fact_storage_sheet_pp_2 values (26,to_char( to_date('2014/12/16','yyyy/mm/dd'),'yyyymmdd') );
  38. insert into fact_storage_sheet_pp_2 values (27,to_char( to_date('2014/12/17','yyyy/mm/dd'),'yyyymmdd') );
  39. insert into fact_storage_sheet_pp_2 values (28,to_char( to_date('2014/12/18','yyyy/mm/dd'),'yyyymmdd') );
  40. insert into fact_storage_sheet_pp_2 values (29,to_char( to_date('2014/12/19','yyyy/mm/dd'),'yyyymmdd') );
  41. insert into fact_storage_sheet_pp_2 values (30,to_char( to_date('2014/12/20','yyyy/mm/dd'),'yyyymmdd') );
  42. insert into fact_storage_sheet_pp_2 values (31,to_char( to_date('2014/12/21','yyyy/mm/dd'),'yyyymmdd') );
  43. insert into fact_storage_sheet_pp_2 values (32,to_char( to_date('2014/12/22','yyyy/mm/dd'),'yyyymmdd') );
  44. insert into fact_storage_sheet_pp_2 values (33,to_char( to_date('2014/12/23','yyyy/mm/dd'),'yyyymmdd') );
  45. insert into fact_storage_sheet_pp_2 values (34,to_char( to_date('2014/12/24','yyyy/mm/dd'),'yyyymmdd') );
  46. insert into fact_storage_sheet_pp_2 values (35,to_char( to_date('2014/12/25','yyyy/mm/dd'),'yyyymmdd') );
  47. insert into fact_storage_sheet_pp_2 values (36,to_char( to_date('2014/12/26','yyyy/mm/dd'),'yyyymmdd') );
  48. insert into fact_storage_sheet_pp_2 values (37,to_char( to_date('2014/12/27','yyyy/mm/dd'),'yyyymmdd') );
  49. insert into fact_storage_sheet_pp_2 values (38,to_char( to_date('2014/12/28','yyyy/mm/dd'),'yyyymmdd') );
  50. insert into fact_storage_sheet_pp_2 values (39,to_char( to_date('2014/12/29','yyyy/mm/dd'),'yyyymmdd') );
  51. insert into fact_storage_sheet_pp_2 values (40,to_char( to_date('2014/12/30','yyyy/mm/dd'),'yyyymmdd') );
  52. insert into fact_storage_sheet_pp_2 values (41,to_char( to_date('2014/12/31','yyyy/mm/dd'),'yyyymmdd') );
  53. insert into fact_storage_sheet_pp_2 values (42,to_char( to_date('2015/01/01','yyyy/mm/dd'),'yyyymmdd') );
  54. insert into fact_storage_sheet_pp_2 values (43,to_char( to_date('2015/01/02','yyyy/mm/dd'),'yyyymmdd') );
  55. insert into fact_storage_sheet_pp_2 values (44,to_char( to_date('2015/01/03','yyyy/mm/dd'),'yyyymmdd') );
  56. insert into fact_storage_sheet_pp_2 values (45,to_char( to_date('2015/01/04','yyyy/mm/dd'),'yyyymmdd') );
  57. insert into fact_storage_sheet_pp_2 values (46,to_char( to_date('2015/01/05','yyyy/mm/dd'),'yyyymmdd') );
  58. insert into fact_storage_sheet_pp_2 values (47,to_char( to_date('2015/01/06','yyyy/mm/dd'),'yyyymmdd') );
  59. insert into fact_storage_sheet_pp_2 values (48,to_char( to_date('2015/01/07','yyyy/mm/dd'),'yyyymmdd') );
  60. insert into fact_storage_sheet_pp_2 values (49,to_char( to_date('2015/01/08','yyyy/mm/dd'),'yyyymmdd') );
  61. insert into fact_storage_sheet_pp_2 values (50,to_char( to_date('2015/01/09','yyyy/mm/dd'),'yyyymmdd') );
  62. insert into fact_storage_sheet_pp_2 values (51,to_char( to_date('2015/01/10','yyyy/mm/dd'),'yyyymmdd') );
  63. insert into fact_storage_sheet_pp_2 values (52,to_char( to_date('2015/01/11','yyyy/mm/dd'),'yyyymmdd') );
  64. insert into fact_storage_sheet_pp_2 values (53,to_char( to_date('2015/01/12','yyyy/mm/dd'),'yyyymmdd') );
  65. insert into fact_storage_sheet_pp_2 values (54,to_char( to_date('2015/01/13','yyyy/mm/dd'),'yyyymmdd') );
  66. insert into fact_storage_sheet_pp_2 values (55,to_char( to_date('2015/01/14','yyyy/mm/dd'),'yyyymmdd') );
  67. insert into fact_storage_sheet_pp_2 values (56,to_char( to_date('2015/01/15','yyyy/mm/dd'),'yyyymmdd') );
  68. insert into fact_storage_sheet_pp_2 values (57,to_char( to_date('2015/01/16','yyyy/mm/dd'),'yyyymmdd') );
  69. insert into fact_storage_sheet_pp_2 values (58,to_char( to_date('2015/01/17','yyyy/mm/dd'),'yyyymmdd') );
  70. insert into fact_storage_sheet_pp_2 values (59,to_char( to_date('2015/01/18','yyyy/mm/dd'),'yyyymmdd') );
  71. insert into fact_storage_sheet_pp_2 values (60,to_char( to_date('2015/01/19','yyyy/mm/dd'),'yyyymmdd') );
  72. insert into fact_storage_sheet_pp_2 values (61,to_char( to_date('2015/01/20','yyyy/mm/dd'),'yyyymmdd') );
  73. insert into fact_storage_sheet_pp_2 values (62,to_char( to_date('2015/01/21','yyyy/mm/dd'),'yyyymmdd') );
  74. insert into fact_storage_sheet_pp_2 values (63,to_char( to_date('2015/01/22','yyyy/mm/dd'),'yyyymmdd') );
  75. insert into fact_storage_sheet_pp_2 values (64,to_char( to_date('2015/01/23','yyyy/mm/dd'),'yyyymmdd') );
  76. insert into fact_storage_sheet_pp_2 values (65,to_char( to_date('2015/01/24','yyyy/mm/dd'),'yyyymmdd') );
  77. insert into fact_storage_sheet_pp_2 values (66,to_char( to_date('2015/01/25','yyyy/mm/dd'),'yyyymmdd') );
  78. insert into fact_storage_sheet_pp_2 values (67,to_char( to_date('2015/01/26','yyyy/mm/dd'),'yyyymmdd') );
  79. insert into fact_storage_sheet_pp_2 values (68,to_char( to_date('2015/01/27','yyyy/mm/dd'),'yyyymmdd') );
  80. insert into fact_storage_sheet_pp_2 values (69,to_char( to_date('2015/01/28','yyyy/mm/dd'),'yyyymmdd') );
  81. insert into fact_storage_sheet_pp_2 values (70,to_char( to_date('2015/01/29','yyyy/mm/dd'),'yyyymmdd') );
  82. insert into fact_storage_sheet_pp_2 values (71,to_char( to_date('2015/01/30','yyyy/mm/dd'),'yyyymmdd') );
  83. insert into fact_storage_sheet_pp_2 values (72,to_char( to_date('2015/01/31','yyyy/mm/dd'),'yyyymmdd') );
  84. insert into fact_storage_sheet_pp_2 values (73,to_char( to_date('2015/02/01','yyyy/mm/dd'),'yyyymmdd') );
  85. insert into fact_storage_sheet_pp_2 values (74,to_char( to_date('2015/02/02','yyyy/mm/dd'),'yyyymmdd') );
  86. insert into fact_storage_sheet_pp_2 values (75,to_char( to_date('2015/02/03','yyyy/mm/dd'),'yyyymmdd') );

  87. commit;

  88. commit;


  89. --查看分区
  90. select
  91.   table_name ,
  92.   partition_name ,
  93.   high_value
  94. from user_tab_partitions
  95. where table_name = 'FACT_STORAGE_SHEET_PP_2'
  96. order by 2;

  97. -- and partition_name like 'SYS%'


  98. --分区重命名

  99. declare
  100. v_sql varchar(400);
  101. v_table_name user_tab_partitions.table_name%type;
  102. v_partition_name user_tab_partitions.partition_name%type;
  103. v_high_value varchar(200);
  104. v_tmp_partition_name user_tab_partitions.partition_name%type;

  105. cursor cur is
  106. select
  107.   table_name ,
  108.   partition_name ,
  109.   high_value
  110. from user_tab_partitions
  111. where partition_name like 'SYS%' and table_name = 'FACT_STORAGE_SHEET_PP_2' ;

  112. begin
  113.   open cur;
  114.   loop
  115.     fetch cur into v_table_name,v_partition_name,v_high_value;
  116.     exit when cur%notfound;
  117.     v_tmp_partition_name := to_char(to_date(v_high_value - 1, 'yyyymmdd'),'yyyymmdd');
  118.     v_sql := 'alter table '|| v_table_name ||' rename partition '
  119.     ||v_partition_name
  120.     ||' to P' || v_tmp_partition_name;
  121.     dbms_output.put_line( v_sql );
  122.     execute immediate v_sql;
  123.   end loop;
  124.   close cur;
  125. end;
  126. /


  127. --查询分区记录
  128. select * from FACT_STORAGE_SHEET_PP_2 partition(P20150101);

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/22661144/viewspace-1960904/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2011-04-03

  • 博文量
    209
  • 访问量
    490396