-- #############################################################################################
--
-- %Purpose: Extensive Partitioning Examples for Oracle8 Partition Option
--
-- Example 1: - The Partition Key is part of the Primary Key
--            - Partition Key: [date_cdr]
--            - Primary Key:   [bkg_id,date_cdr]
--
-- Example 2: - The Partition Key is NOT part of the Primary Key
--            - Partition Key: [date_req]
--            - Primary Key:   [bkg_id,req_id]
--
-- #############################################################################################
--
DROP TABLE cdr CASCADE CONSTRAINTS;
--
CREATE TABLE cdr (
 bkg_id                          NUMBER(15)                 NOT NULL,
 date_cdr                        DATE                       NOT NULL,
 calltype                        NUMBER(2)                  NOT NULL)
PARTITION BY RANGE (date_cdr)
 (PARTITION cdr_01_1999 VALUES LESS THAN
 (TO_DATE('01.02.1999','DD.MM.YYYY'))
  TABLESPACE cdr
  STORAGE (INITIAL 1K NEXT 100M MINEXTENTS 1 MAXEXTENTS UNLIMITED) PCTFREE 0,
  PARTITION cdr_02_1999 VALUES LESS THAN
 (TO_DATE('01.03.1999','DD.MM.YYYY'))
  TABLESPACE cdr
  STORAGE (INITIAL 1K NEXT 100M MINEXTENTS 1 MAXEXTENTS UNLIMITED) PCTFREE 0,
  PARTITION cdr_03_1999 VALUES LESS THAN
 (TO_DATE('01.04.1999','DD.MM.YYYY'))
  TABLESPACE cdr
  STORAGE (INITIAL 1K NEXT 100M MINEXTENTS 1 MAXEXTENTS UNLIMITED) PCTFREE 0,
  PARTITION cdr_04_1999 VALUES LESS THAN
 (TO_DATE('01.05.1999','DD.MM.YYYY'))
  TABLESPACE cdr
  STORAGE (INITIAL 1K NEXT 100M MINEXTENTS 1 MAXEXTENTS UNLIMITED) PCTFREE 0,
  PARTITION cdr_05_1999 VALUES LESS THAN (MAXVALUE)
  TABLESPACE cdr
  STORAGE (INITIAL 1K NEXT 100M MINEXTENTS 1 MAXEXTENTS UNLIMITED) PCTFREE 0)
/
-------------------------------------------------------------------
-- Local NON prefixed Primary Key (UNIQUE)
--
-- Der linke Teil des Index stimmt nicht
-- mit dem Partition-Key [date_cdr] überein
-------------------------------------------------------------------
ALTER TABLE cdr ADD (
  CONSTRAINT pk_cdr
  PRIMARY KEY (bkg_id,date_cdr)
USING INDEX
LOCAL
(PARTITION cdr_01_1999
  TABLESPACE idx_cdr
  STORAGE (INITIAL 1K NEXT 100M MINEXTENTS 1 MAXEXTENTS UNLIMITED) PCTFREE 0,
 PARTITION cdr_02_1999
  TABLESPACE idx_cdr
  STORAGE (INITIAL 1K NEXT 100M MINEXTENTS 1 MAXEXTENTS UNLIMITED) PCTFREE 0,
 PARTITION cdr_03_1999
  TABLESPACE idx_cdr
  STORAGE (INITIAL 1K NEXT 100M MINEXTENTS 1 MAXEXTENTS UNLIMITED) PCTFREE 0,
 PARTITION cdr_04_1999
  TABLESPACE idx_cdr
  STORAGE (INITIAL 1K NEXT 100M MINEXTENTS 1 MAXEXTENTS UNLIMITED) PCTFREE 0,
 PARTITION cdr_05_1999
  TABLESPACE idx_cdr
  STORAGE (INITIAL 1K NEXT 100M MINEXTENTS 1 MAXEXTENTS UNLIMITED) PCTFREE 0))
/
--
-------------------------------------------------------------------
-- Local NON prefixed Key (non UNIQUE)
--
-- Der linke Teil des Index stimmt nicht
-- mit dem Partition-Key [date_cdr] überein
-- Der Index kann NICHT UNIQUE sein, da calltype nicht Teil des
-- Primary Keys ist.
-------------------------------------------------------------------
CREATE INDEX cdr_idx_1 ON cdr (calltype)
LOCAL
(PARTITION cdr_01_1999
  TABLESPACE idx_cdr
  STORAGE (INITIAL 1K NEXT 100M MINEXTENTS 1 MAXEXTENTS UNLIMITED) PCTFREE 0,
 PARTITION cdr_02_1999
  TABLESPACE idx_cdr
  STORAGE (INITIAL 1K NEXT 100M MINEXTENTS 1 MAXEXTENTS UNLIMITED) PCTFREE 0,
 PARTITION cdr_03_1999
  TABLESPACE idx_cdr
  STORAGE (INITIAL 1K NEXT 100M MINEXTENTS 1 MAXEXTENTS UNLIMITED) PCTFREE 0,
 PARTITION cdr_04_1999
  TABLESPACE idx_cdr
  STORAGE (INITIAL 1K NEXT 100M MINEXTENTS 1 MAXEXTENTS UNLIMITED) PCTFREE 0,
 PARTITION cdr_05_1999
  TABLESPACE idx_cdr
  STORAGE (INITIAL 1K NEXT 100M MINEXTENTS 1 MAXEXTENTS UNLIMITED) PCTFREE 0)
/
--
-------------------------------------------------------------------
-- Local prefixed Index (UNIQUE)
--
-- Der linke Teil des Index stimmt
-- mit dem Partition-Key [date_cdr] überein, deshalb kann der
-- Index UNIQUE sein.
-------------------------------------------------------------------
--
CREATE UNIQUE INDEX cdr_idx_2 ON cdr (date_cdr,bkg_id)
LOCAL
(PARTITION cdr_01_1999
  TABLESPACE idx_cdr
  STORAGE (INITIAL 1K NEXT 100M MINEXTENTS 1 MAXEXTENTS UNLIMITED) PCTFREE 0,
 PARTITION cdr_02_1999
  TABLESPACE idx_cdr
  STORAGE (INITIAL 1K NEXT 100M MINEXTENTS 1 MAXEXTENTS UNLIMITED) PCTFREE 0,
 PARTITION cdr_03_1999
  TABLESPACE idx_cdr
  STORAGE (INITIAL 1K NEXT 100M MINEXTENTS 1 MAXEXTENTS UNLIMITED) PCTFREE 0,
 PARTITION cdr_04_1999
  TABLESPACE idx_cdr
  STORAGE (INITIAL 1K NEXT 100M MINEXTENTS 1 MAXEXTENTS UNLIMITED) PCTFREE 0,
 PARTITION cdr_05_1999
  TABLESPACE idx_cdr
  STORAGE (INITIAL 1K NEXT 100M MINEXTENTS 1 MAXEXTENTS UNLIMITED) PCTFREE 0)
/
ALTER TABLE cdr ADD (
      CONSTRAINT cdr_idx_2
      UNIQUE (date_cdr,bkg_id))
/
--
-------------------------------------------------------------------
-- Local prefixed Index (UNIQUE)
--
-- Der linke Teil des Index entspricht dem Partition-Key [date_cdr].
-- Deshalb kann der Index UNIQUE sein.
-------------------------------------------------------------------
--
CREATE UNIQUE INDEX cdr_idx_3 ON cdr (date_cdr)
LOCAL
(PARTITION cdr_01_1999
  TABLESPACE idx_cdr
  STORAGE (INITIAL 1K NEXT 100M MINEXTENTS 1 MAXEXTENTS UNLIMITED) PCTFREE 0,
 PARTITION cdr_02_1999
  TABLESPACE idx_cdr
  STORAGE (INITIAL 1K NEXT 100M MINEXTENTS 1 MAXEXTENTS UNLIMITED) PCTFREE 0,
 PARTITION cdr_03_1999
  TABLESPACE idx_cdr
  STORAGE (INITIAL 1K NEXT 100M MINEXTENTS 1 MAXEXTENTS UNLIMITED) PCTFREE 0,
 PARTITION cdr_04_1999
  TABLESPACE idx_cdr
  STORAGE (INITIAL 1K NEXT 100M MINEXTENTS 1 MAXEXTENTS UNLIMITED) PCTFREE 0,
 PARTITION cdr_05_1999
  TABLESPACE idx_cdr
  STORAGE (INITIAL 1K NEXT 100M MINEXTENTS 1 MAXEXTENTS UNLIMITED) PCTFREE 0)
/
-------------------------------------------------------------------
-- Example 2: - Der Partition Key ist NICHT Teil des Primary Keys
--          - Partition Key: [date_req]
--          - Primary Key:   [bkg_id,req_id]
-------------------------------------------------------------------
--
DROP TABLE req CASCADE CONSTRAINTS;
--
CREATE TABLE req (
 bkg_id                          NUMBER(15)                 NOT NULL,
 req_id                          NUMBER(15)                 NOT NULL,
 date_req                        DATE                       NOT NULL,
 status                          NUMBER(2)                  NOT NULL)
PARTITION BY RANGE (date_req)
 (PARTITION req_01_1999 VALUES LESS THAN
 (TO_DATE('01.02.1999','DD.MM.YYYY'))
  TABLESPACE req
  STORAGE (INITIAL 1K NEXT 100M MINEXTENTS 1 MAXEXTENTS UNLIMITED) PCTFREE 0,
  PARTITION req_02_1999 VALUES LESS THAN
 (TO_DATE('01.03.1999','DD.MM.YYYY'))
  TABLESPACE req
  STORAGE (INITIAL 1K NEXT 100M MINEXTENTS 1 MAXEXTENTS UNLIMITED) PCTFREE 0,
  PARTITION req_03_1999 VALUES LESS THAN
 (TO_DATE('01.04.1999','DD.MM.YYYY'))
  TABLESPACE req
  STORAGE (INITIAL 1K NEXT 100M MINEXTENTS 1 MAXEXTENTS UNLIMITED) PCTFREE 0,
  PARTITION req_04_1999 VALUES LESS THAN
 (TO_DATE('01.05.1999','DD.MM.YYYY'))
  TABLESPACE req
  STORAGE (INITIAL 1K NEXT 100M MINEXTENTS 1 MAXEXTENTS UNLIMITED) PCTFREE 0,
  PARTITION req_05_1999 VALUES LESS THAN (MAXVALUE)
  TABLESPACE req
  STORAGE (INITIAL 1K NEXT 100M MINEXTENTS 1 MAXEXTENTS UNLIMITED) PCTFREE 0)
/
-------------------------------------------------------------------
-- Normaler Primary Key, unpartitioniert, nicht zu empfehlen
-- da der Index wieder sehr gross wird. Besser nach einem anderen
-- Kriterium partionieren, zB [bkg_id,req_id]
-------------------------------------------------------------------
--
ALTER TABLE req ADD (
      CONSTRAINT pk_req
      PRIMARY KEY (bkg_id,req_id)
    USING INDEX
    TABLESPACE idx_req
    STORAGE
     (INITIAL       500K
    NEXT        500K
    MINEXTENTS    1
    MAXEXTENTS    UNLIMITED
    PCTINCREASE   0
    FREELISTS     2)
)
/
ALTER TABLE req DROP PRIMARY KEY
/
-------------------------------------------------------------------
-- Globaler Primary Key, ein "MUST" für Primary Keys welche ohne
-- den Partition-Key auskommen müssen.
-------------------------------------------------------------------
--
CREATE UNIQUE INDEX pk_req ON req (bkg_id,req_id)
GLOBAL
PARTITION BY RANGE (bkg_id,req_id)
(PARTITION pk_req_01
  VALUES LESS THAN (100000,100000)
  TABLESPACE idx_bkg
  STORAGE (INITIAL 1K NEXT 100M MINEXTENTS 1 MAXEXTENTS UNLIMITED) PCTFREE 0,
 PARTITION pk_req_02
  VALUES LESS THAN (200000,200000)
  TABLESPACE idx_bkg
  STORAGE (INITIAL 1K NEXT 100M MINEXTENTS 1 MAXEXTENTS UNLIMITED) PCTFREE 0,
 PARTITION pk_req_03
  VALUES LESS THAN (300000,300000)
  TABLESPACE idx_bkg
  STORAGE (INITIAL 1K NEXT 100M MINEXTENTS 1 MAXEXTENTS UNLIMITED) PCTFREE 0,
 PARTITION pk_req_04
  VALUES LESS THAN (MAXVALUE,MAXVALUE)
  TABLESPACE idx_bkg
  STORAGE (INITIAL 1K NEXT 100M MINEXTENTS 1 MAXEXTENTS UNLIMITED) PCTFREE 0)
/
ALTER TABLE req ADD (
      CONSTRAINT pk_req
      PRIMARY KEY (bkg_id,req_id))
/
-------------------------------------------------------------------
-- Local prefixed Index (UNIQUE wäre möglich)
--
-- Der linke Teil des Index stimmt mit dem Partition-Key
-- [date_req] überein.
-------------------------------------------------------------------
--
CREATE INDEX idx_req_1 ON req (date_req,req_id)
LOCAL
(PARTITION req_01_1999
  TABLESPACE idx_req
  STORAGE (INITIAL 1K NEXT 100M MINEXTENTS 1 MAXEXTENTS UNLIMITED) PCTFREE 0,
 PARTITION req_02_1999
  TABLESPACE idx_req
  STORAGE (INITIAL 1K NEXT 100M MINEXTENTS 1 MAXEXTENTS UNLIMITED) PCTFREE 0,
 PARTITION req_03_1999
  TABLESPACE idx_req
  STORAGE (INITIAL 1K NEXT 100M MINEXTENTS 1 MAXEXTENTS UNLIMITED) PCTFREE 0,
 PARTITION req_04_1999
  TABLESPACE idx_req
  STORAGE (INITIAL 1K NEXT 100M MINEXTENTS 1 MAXEXTENTS UNLIMITED) PCTFREE 0,
 PARTITION req_05_1999
  TABLESPACE idx_req
  STORAGE (INITIAL 1K NEXT 100M MINEXTENTS 1 MAXEXTENTS UNLIMITED) PCTFREE 0)
/
-------------------------------------------------------------------
-- Local prefixed Index nur auf Partition-Key
--
-- Der Index entspricht mit dem Partition-Key [date_req]
-------------------------------------------------------------------
--
CREATE INDEX idx_req_2 ON req (date_req)
LOCAL
(PARTITION req_01_1999
  TABLESPACE idx_req
  STORAGE (INITIAL 1K NEXT 100M MINEXTENTS 1 MAXEXTENTS UNLIMITED) PCTFREE 0,
 PARTITION req_02_1999
  TABLESPACE idx_req
  STORAGE (INITIAL 1K NEXT 100M MINEXTENTS 1 MAXEXTENTS UNLIMITED) PCTFREE 0,
 PARTITION req_03_1999
  TABLESPACE idx_req
  STORAGE (INITIAL 1K NEXT 100M MINEXTENTS 1 MAXEXTENTS UNLIMITED) PCTFREE 0,
 PARTITION req_04_1999
  TABLESPACE idx_req
  STORAGE (INITIAL 1K NEXT 100M MINEXTENTS 1 MAXEXTENTS UNLIMITED) PCTFREE 0,
 PARTITION req_05_1999
  TABLESPACE idx_req
  STORAGE (INITIAL 1K NEXT 100M MINEXTENTS 1 MAXEXTENTS UNLIMITED) PCTFREE 0)
/
-------------------------------------------------------------------
-- Local non prefixed Index
--
-- Der linke Teil des Index stimmt NICHT mit dem
-- Partition-Key date_req überein
-------------------------------------------------------------------
--
CREATE INDEX idx_req_3 ON req (req_id,date_req)
LOCAL
(PARTITION req_01_1999
  TABLESPACE idx_req
  STORAGE (INITIAL 1K NEXT 100M MINEXTENTS 1 MAXEXTENTS UNLIMITED) PCTFREE 0,
 PARTITION req_02_1999
  TABLESPACE idx_req
  STORAGE (INITIAL 1K NEXT 100M MINEXTENTS 1 MAXEXTENTS UNLIMITED) PCTFREE 0,
 PARTITION req_03_1999
  TABLESPACE idx_req
  STORAGE (INITIAL 1K NEXT 100M MINEXTENTS 1 MAXEXTENTS UNLIMITED) PCTFREE 0,
 PARTITION req_04_1999
  TABLESPACE idx_req
  STORAGE (INITIAL 1K NEXT 100M MINEXTENTS 1 MAXEXTENTS UNLIMITED) PCTFREE 0,
 PARTITION req_05_1999
  TABLESPACE idx_req
  STORAGE (INITIAL 1K NEXT 100M MINEXTENTS 1 MAXEXTENTS UNLIMITED) PCTFREE 0)
/
-------------------------------------------------------------------
-- Local non prefixed Index
--
-- Der Index ist ein beliebiges Attribut
-------------------------------------------------------------------
--
CREATE INDEX idx_req_4 ON req (req_id)
LOCAL
(PARTITION req_01_1999
  TABLESPACE idx_req
  STORAGE (INITIAL 1K NEXT 100M MINEXTENTS 1 MAXEXTENTS UNLIMITED) PCTFREE 0,
 PARTITION req_02_1999
  TABLESPACE idx_req
  STORAGE (INITIAL 1K NEXT 100M MINEXTENTS 1 MAXEXTENTS UNLIMITED) PCTFREE 0,
 PARTITION req_03_1999
  TABLESPACE idx_req
  STORAGE (INITIAL 1K NEXT 100M MINEXTENTS 1 MAXEXTENTS UNLIMITED) PCTFREE 0,
 PARTITION req_04_1999
  TABLESPACE idx_req
  STORAGE (INITIAL 1K NEXT 100M MINEXTENTS 1 MAXEXTENTS UNLIMITED) PCTFREE 0,
 PARTITION req_05_1999
  TABLESPACE idx_req
  STORAGE (INITIAL 1K NEXT 100M MINEXTENTS 1 MAXEXTENTS UNLIMITED) PCTFREE 0)
/
-------------------------------------------------------------------
-- Bitmapped Indexe
--
-- Bitmapped Indexe sind immer local, global nicht möglich
-------------------------------------------------------------------
--
CREATE BITMAP INDEX idx_bm_req_1 ON req (status)
LOCAL
(PARTITION req_01_1999
  TABLESPACE idx_req
  STORAGE (INITIAL 1K NEXT 100M MINEXTENTS 1 MAXEXTENTS UNLIMITED) PCTFREE 0,
 PARTITION req_02_1999
  TABLESPACE idx_req
  STORAGE (INITIAL 1K NEXT 100M MINEXTENTS 1 MAXEXTENTS UNLIMITED) PCTFREE 0,
 PARTITION req_03_1999
  TABLESPACE idx_req
  STORAGE (INITIAL 1K NEXT 100M MINEXTENTS 1 MAXEXTENTS UNLIMITED) PCTFREE 0,
 PARTITION req_04_1999
  TABLESPACE idx_req
  STORAGE (INITIAL 1K NEXT 100M MINEXTENTS 1 MAXEXTENTS UNLIMITED) PCTFREE 0,
 PARTITION req_05_1999
  TABLESPACE idx_req
  STORAGE (INITIAL 1K NEXT 100M MINEXTENTS 1 MAXEXTENTS UNLIMITED) PCTFREE 0)
/
-------------------------------------------------------------------
-- Anfügen von zwei neuen Partitionen
-------------------------------------------------------------------
--
ALTER TABLE req
  SPLIT PARTITION req_05_1999 AT (TO_DATE('31.05.1999','DD.MM.YYYY'))
  INTO
  (PARTITION req_05_1999_1
     TABLESPACE req
     STORAGE (INITIAL 1K NEXT 100M MINEXTENTS 1 MAXEXTENTS UNLIMITED) PCTFREE 0,
   PARTITION req_05_1999_2
     TABLESPACE req
     STORAGE (INITIAL 1K NEXT 100M MINEXTENTS 1 MAXEXTENTS UNLIMITED) PCTFREE 0)
/
ALTER TABLE req
  RENAME PARTITION req_05_1999_1 TO req_05_1999
/
ALTER TABLE req
  RENAME PARTITION req_05_1999_2 TO req_06_1999
/
--
ALTER TABLE req
  SPLIT PARTITION req_06_1999 AT (TO_DATE('30.06.1999','DD.MM.YYYY'))
  INTO
  (PARTITION req_06_1999_1
     TABLESPACE req
     STORAGE (INITIAL 1K NEXT 100M MINEXTENTS 1 MAXEXTENTS UNLIMITED) PCTFREE 0,
   PARTITION req_06_1999_2
     TABLESPACE req
     STORAGE (INITIAL 1K NEXT 100M MINEXTENTS 1 MAXEXTENTS UNLIMITED) PCTFREE 0)
/
--
-- Die local Indexes wurden auch aufgeteilt und müssen
-- nun wieder auf die gleichen Partionsnamen geändert werden
--
ALTER TABLE req
  RENAME PARTITION req_06_1999_1 TO req_06_1999
/
ALTER TABLE req
  RENAME PARTITION req_06_1999_2 TO req_07_1999
/
--
ALTER INDEX idx_req_1
  RENAME PARTITION req_05_1999_1 TO req_05_1999
/
ALTER INDEX idx_req_1
  RENAME PARTITION req_06_1999_1 TO req_06_1999
/
ALTER INDEX idx_req_1
  RENAME PARTITION req_06_1999_2 TO req_07_1999
/
--
ALTER INDEX idx_req_2
  RENAME PARTITION req_05_1999_1 TO req_05_1999
/
ALTER INDEX idx_req_2
  RENAME PARTITION req_06_1999_1 TO req_06_1999
/
ALTER INDEX idx_req_2
  RENAME PARTITION req_06_1999_2 TO req_07_1999
/
--
ALTER INDEX idx_req_3
  RENAME PARTITION req_05_1999_1 TO req_05_1999
/
ALTER INDEX idx_req_3
  RENAME PARTITION req_06_1999_1 TO req_06_1999
/
ALTER INDEX idx_req_3
  RENAME PARTITION req_06_1999_2 TO req_07_1999
/
--
ALTER INDEX idx_req_4
  RENAME PARTITION req_05_1999_1 TO req_05_1999
/
ALTER INDEX idx_req_4
  RENAME PARTITION req_06_1999_1 TO req_06_1999
/
ALTER INDEX idx_req_4
  RENAME PARTITION req_06_1999_2 TO req_07_1999
/
--
ALTER INDEX idx_bm_req_1
  RENAME PARTITION req_05_1999_1 TO req_05_1999
/
ALTER INDEX idx_bm_req_1
  RENAME PARTITION req_06_1999_1 TO req_06_1999
/
ALTER INDEX idx_bm_req_1
  RENAME PARTITION req_06_1999_2 TO req_07_1999
/
--
-- Rebuild aller local Indexes
--
ALTER TABLE req MODIFY PARTITION req_01_1999
  REBUILD UNUSABLE LOCAL INDEXES
/
ALTER TABLE req MODIFY PARTITION req_02_1999
  REBUILD UNUSABLE LOCAL INDEXES
/
ALTER TABLE req MODIFY PARTITION req_03_1999
  REBUILD UNUSABLE LOCAL INDEXES
/
ALTER TABLE req MODIFY PARTITION req_04_1999
  REBUILD UNUSABLE LOCAL INDEXES
/
ALTER TABLE req MODIFY PARTITION req_05_1999
  REBUILD UNUSABLE LOCAL INDEXES
/
ALTER TABLE req MODIFY PARTITION req_06_1999
  REBUILD UNUSABLE LOCAL INDEXES
/
ALTER TABLE req MODIFY PARTITION req_07_1999
  REBUILD UNUSABLE LOCAL INDEXES
/
-------------------------------------------------------------------
-- Anfügen von einer neuen Partition für den Primary Key
-------------------------------------------------------------------
--
ALTER INDEX pk_req
  SPLIT PARTITION pk_req_04 AT (400000,400000)
  INTO
  (PARTITION pk_req_04_1
     TABLESPACE req
     STORAGE (INITIAL 1K NEXT 100M MINEXTENTS 1 MAXEXTENTS UNLIMITED) PCTFREE 0,
   PARTITION pk_req_04_2
     TABLESPACE req
     STORAGE (INITIAL 1K NEXT 100M MINEXTENTS 1 MAXEXTENTS UNLIMITED) PCTFREE 0)
/
ALTER INDEX pk_req
  RENAME PARTITION pk_req_04_1 TO pk_req_04
/
ALTER INDEX pk_req
  RENAME PARTITION pk_req_04_2 TO pk_req_05
/
--
-- Rebuild des Primary Keys einzeln für jede Partition
--
ALTER INDEX pk_req
  REBUILD PARTITION pk_req_01
/
ALTER INDEX pk_req
  REBUILD PARTITION pk_req_02
/
ALTER INDEX pk_req
  REBUILD PARTITION pk_req_03
/
ALTER INDEX pk_req
  REBUILD PARTITION pk_req_04
/
ALTER INDEX pk_req
  REBUILD PARTITION pk_req_05
/

--
-- Add Partition (Der Partition Key ist Teil des Primary Keys)
--
-- Dies ist nur möglich, wenn die letzte Partition nicht
-- durch MAXVALUE begrenzt ist. Deshalb löschen wir die
-- letzte Partition zuerst. Die local Indexes werden beim
-- Hinzufügen von Partitions automatisch auch mit einer
-- Indexpartition ergänzt (sehr gute Wartbarkeit).
--
ALTER TABLE cdr DROP PARTITION cdr_05_1999;
ALTER TABLE cdr
  ADD PARTITION cdr_05_1999 VALUES LESS THAN
    (TO_DATE('01.06.1999','DD.MM.YYYY'))
    TABLESPACE cdr
    STORAGE (INITIAL 1K NEXT 100M MINEXTENTS 1 MAXEXTENTS UNLIMITED) PCTFREE 0;
ALTER TABLE cdr
  ADD PARTITION cdr_06_1999 VALUES LESS THAN
    (TO_DATE('01.07.1999','DD.MM.YYYY'))
    TABLESPACE cdr
    STORAGE (INITIAL 1K NEXT 100M MINEXTENTS 1 MAXEXTENTS UNLIMITED) PCTFREE 0;
ALTER TABLE cdr
  ADD PARTITION cdr_07_1999 VALUES LESS THAN
    (TO_DATE('01.08.1999','DD.MM.YYYY'))
    TABLESPACE cdr
    STORAGE (INITIAL 1K NEXT 100M MINEXTENTS 1 MAXEXTENTS UNLIMITED) PCTFREE 0;
--
-- Add Partition (Der Partition Key ist nicht Teil des Primary Keys)
--
-- Auch hier werden die local Indexes automatisch nachgefahren.
-- Die Indexpartition des global Primary Key bleibt logischerweise
-- unberührt, da dies eine vollkommen autonome Partition ist.
--
ALTER TABLE req DROP PARTITION req_07_1999;
ALTER TABLE req
  ADD PARTITION req_07_1999 VALUES LESS THAN
    (TO_DATE('01.08.1999','DD.MM.YYYY'))
    TABLESPACE req
    STORAGE (INITIAL 1K NEXT 100M MINEXTENTS 1 MAXEXTENTS UNLIMITED) PCTFREE 0;
ALTER TABLE req
  ADD PARTITION req_08_1999 VALUES LESS THAN
    (TO_DATE('01.09.1999','DD.MM.YYYY'))
    TABLESPACE req
    STORAGE (INITIAL 1K NEXT 100M MINEXTENTS 1 MAXEXTENTS UNLIMITED) PCTFREE 0;
--
-- Move Partition
--
ALTER TABLE req
  MOVE PARTITION req_08_1999
    TABLESPACE tab
    STORAGE (INITIAL 1K NEXT 1K MINEXTENTS 1 MAXEXTENTS UNLIMITED) PCTFREE 0;
--
-- Indexe müssen rebuilded werden, da sie durch Move
-- Partition IU wurden (Index Unusable)
--
ALTER INDEX idx_req_1
  REBUILD PARTITION req_08_1999
/
ALTER INDEX idx_req_2
  REBUILD PARTITION req_08_1999
/
ALTER INDEX idx_req_3
  REBUILD PARTITION req_08_1999
/
ALTER INDEX idx_req_4
  REBUILD PARTITION req_08_1999
/