Oracle Interval Partition 自動分割槽表-實驗

fjzcau發表於2015-12-24
Oracle

  1. #自動分割槽表，實驗

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


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

  91. commit;

  93. commit;


  96. --檢視分割槽
  97. select
  98.   table_name ,
  99.   partition_name ,
  100.   high_value
  101. from user_tab_partitions
  102. where table_name = 'FACT_STORAGE_SHEET_PP_2'
  103. order by 2;

  105. -- and partition_name like 'SYS%'


  108. --分割槽重新命名

  110. declare
  111. v_sql varchar(400);
  112. v_table_name user_tab_partitions.table_name%type;
  113. v_partition_name user_tab_partitions.partition_name%type;
  114. v_high_value varchar(200);
  115. v_tmp_partition_name user_tab_partitions.partition_name%type;

  117. cursor cur is
  118. select
  119.   table_name ,
  120.   partition_name ,
  121.   high_value
  122. from user_tab_partitions
  123. where partition_name like 'SYS%' and table_name = 'FACT_STORAGE_SHEET_PP_2' ;

  125. begin
  126.   open cur;
  127.   loop
  128.     fetch cur into v_table_name,v_partition_name,v_high_value;
  129.     exit when cur%notfound;
  130.     v_tmp_partition_name := to_char(to_date(v_high_value - 1, 'yyyymmdd'),'yyyymmdd');
  131.     v_sql := 'alter table '|| v_table_name ||' rename partition '
  132.     ||v_partition_name
  133.     ||' to P' || v_tmp_partition_name;
  134.     dbms_output.put_line( v_sql );
  135.     execute immediate v_sql;
  136.   end loop;
  137.   close cur;
  138. end;
  139. /


  142. --查詢分割槽記錄
  143. select * from FACT_STORAGE_SHEET_PP_2 partition(P20150101);

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/22661144/viewspace-1960904/，如需轉載，請註明出處，否則將追究法律責任。

相關文章