{15 その他}複数パーティションでのメンテナンス操作

 

https://docs.oracle.com/cd/F19136_01/vldbg/maintenance-partition-tables-indexes.html#GUID-676087FC-D6DC-4533-95FD-6DC941CE4753
https://docs.oracle.com/cd/F19136_01/vldbg/maintenance-partition-tables-indexes.html#GUID-D413FE9E-7FFA-41E6-BA1B-E2C88095A947
https://docs.oracle.com/cd/F19136_01/vldbg/maintenance-partition-tables-indexes.html#GUID-54080399-6E59-4706-80C8-690159B20790
https://docs.oracle.com/cd/F19136_01/vldbg/maintenance-partition-tables-indexes.html#GUID-01C14320-0D7B-48BE-A5AD-003DDA761277
https://docs.oracle.com/cd/F19136_01/vldbg/maintenance-partition-tables-indexes.html#GUID-2A1C159C-347E-457E-854F-4D67F89B83DB

sqlplus test/test@pdb11

     追加

CREATE TABLE sales
  ( prod_id       NUMBER(6)
  , cust_id       NUMBER
  , time_id       DATE
  , channel_id    CHAR(1)
  , promo_id      NUMBER(6)
  , quantity_sold NUMBER(3)
  , amount_sold   NUMBER(10,2)
  )
 PARTITION BY RANGE (time_id)
 ( PARTITION sales_q1_2006 VALUES LESS THAN (TO_DATE('20060101','yyyymmdd'))
    TABLESPACE users
 , PARTITION sales_q2_2006 VALUES LESS THAN (TO_DATE('20070101','yyyymmdd'))
    TABLESPACE users
 , PARTITION sales_q3_2006 VALUES LESS THAN (TO_DATE('20080101','yyyymmdd'))
    TABLESPACE users
 , PARTITION sales_q4_2006 VALUES LESS THAN (TO_DATE('20090101','yyyymmdd'))
    TABLESPACE users
 );

ALTER TABLE sales ADD 
  PARTITION sales_q1_2007 VALUES LESS THAN (TO_DATE('20100101','yyyymmdd')),
  PARTITION sales_q2_2007 VALUES LESS THAN (TO_DATE('20110101','yyyymmdd')),
  PARTITION sales_q3_2007 VALUES LESS THAN (TO_DATE('20120101','yyyymmdd')),
  PARTITION sales_q4_2007 VALUES LESS THAN (TO_DATE('20130101','yyyymmdd'))
;

select * from user_tab_partitions where table_name = 'SALES';

     削除

ALTER TABLE sales DROP PARTITION sales_q1_2006, sales_q2_2006,
     sales_q3_2006, sales_q3_2007;

     マージ
select * from user_tab_partitions where table_name = 'SALES';

ALTER TABLE sales
  MERGE PARTITIONS SALES_Q1_2007, SALES_Q2_2007
  INTO PARTITION SALES_QA_2007;

     分割
select * from user_tab_partitions where table_name = 'SALES';

ALTER TABLE sales SPLIT PARTITION SALES_Q4_2006 INTO
( PARTITION SALES_Q4_20061 VALUES LESS THAN (TO_DATE('20050101','yyyymmdd')),
  PARTITION SALES_Q4_20062 VALUES LESS THAN (TO_DATE('20060101','yyyymmdd')),
  PARTITION SALES_Q4_20063 VALUES LESS THAN (TO_DATE('20070101','yyyymmdd')),
  PARTITION SALES_Q4_20064 VALUES LESS THAN (TO_DATE('20080101','yyyymmdd')),
  PARTITION SALES_Q4_20065);

 

     切捨て
select * from user_tab_partitions where table_name = 'SALES';

ALTER TABLE sales TRUNCATE PARTITIONS SALES_Q4_20062, SALES_Q4_20063, SALES_Q4_20064;