--ENABLE AUTO TRACING, ETC FROM SQL*PLUS FOR VERBOSE INPUT

SPOOL PARTITION_DELETE_TEST2.LOG;
SET TIMING ON;
SET AUTOTRACE ON;
ALTER SESSION SET TIMED_STATISTICS = TRUE;

--DROP/CREATE A SAMPLE "Partitioned" TRANSACTION TABLE AND ADD APPLICABLE LOCAL INDEXES

DROP SEQUENCE TRANS_SAMPLE_PARTITIONED_SEQ;
DROP TABLE TRANS_SAMPLE_PARTITIONED;
CREATE TABLE TRANS_SAMPLE_PARTITIONED (TXN_ID NUMBER(10) NOT NULL, CREATE_DATE DATE NOT NULL)
PARTITION BY RANGE(CREATE_DATE) (PARTITION TSP_20050501 VALUES LESS THAN (TO_DATE('01-MAY-2005', 'DD-MON-YYYY'))   TABLESPACE TEST);

--DYNAMICALLY CREATED PARTITIONS FOR N+1, WHERE N IS THE NUMBER OF DAYS OF HISTORY YOU WOULD LIKE TO KEEP
--I.E. ALTER TABLE TRANS_SAMPLE_PARTITIONED ADD PARTITION ({PARTITION_NAME} VALUES LESS THAN {TO_DATE HERE} {OPTIONS})

DECLARE
   V_LAST_DATE   DATE;
   MNTH          VARCHAR2 (2);
   MON           VARCHAR2 (3);
   DY            VARCHAR2 (2);
   YR            VARCHAR2 (4);
   STMT          VARCHAR2 (500);
BEGIN
   BEGIN
      FOR J IN REVERSE 1 .. 45
      LOOP
         V_LAST_DATE := SYSDATE - J + 2;
         MNTH := TO_CHAR (V_LAST_DATE, 'MM');
         MON := TO_CHAR (V_LAST_DATE, 'MON');
         DY := TO_CHAR (V_LAST_DATE, 'DD');
         YR := TO_CHAR (V_LAST_DATE, 'YYYY');
         STMT :=
               'alter table trans_sample_partitioned add partition tsp_'
            || YR
            || MNTH
            || DY
            || ' values less than (to_date('
            || CHR (39)
            || YR
            || '-'
            || MON
            || '-'
            || DY
            || CHR (39)
            || ','
            || CHR (39)
            || 'YYYY-MON-DD'
            || CHR (39)
            || '))  tablespace test';
         DBMS_OUTPUT.PUT_LINE (STMT);

         BEGIN
            EXECUTE IMMEDIATE STMT;
         EXCEPTION
            WHEN OTHERS
            THEN
               NULL;
         END;
      END LOOP;
   END;
END;
/

ALTER TABLE TRANS_SAMPLE_PARTITIONED
ADD CONSTRAINT  TRANS_SAMPLE_PARTITIONED_PK
PRIMARY KEY(TXN_ID) DEFERRABLE
USING INDEX GLOBAL TABLESPACE TEST;

CREATE INDEX TRANS_SAMPLE_PART_DATE_IDX ON TRANS_SAMPLE_PARTITIONED(CREATE_DATE) GLOBAL
PARTITION BY RANGE(CREATE_DATE)
(PARTITION VALUES_TO_KEEP VALUES LESS THAN (TO_DATE('01-JUL-2110', 'DD-MON-YYYY'))  TABLESPACE TEST,
 PARTITION THE_REST VALUES LESS THAN (MAXVALUE)  TABLESPACE TEST);

CREATE SEQUENCE TRANS_SAMPLE_PARTITIONED_SEQ;

DECLARE
BEGIN
   FOR J IN REVERSE 0 .. 35
   LOOP
      FOR I IN 1 .. 50000
      LOOP
         INSERT INTO TRANS_SAMPLE_PARTITIONED
            SELECT TRANS_SAMPLE_PARTITIONED_SEQ.NEXTVAL, TRUNC (SYSDATE) - J
              FROM DUAL;
      END LOOP;
      COMMIT;
   END LOOP;
END;
/

EXECUTE DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'siebel',TABNAME => 'trans_sample_partitioned', GRANULARITY=>'ALL', CASCADE=>TRUE);

SELECT PARTITION_NAME, NUM_ROWS FROM USER_TAB_PARTITIONS WHERE TABLE_NAME='TRANS_SAMPLE_PARTITIONED' ORDER BY PARTITION_POSITION;

SELECT COUNT (*)
  FROM TRANS_SAMPLE_PARTITIONED;

SELECT   CREATE_DATE, COUNT (TXN_ID)
    FROM TRANS_SAMPLE_PARTITIONED
GROUP BY CREATE_DATE;

--PERFORM THE DELETE, BUT THIS TIME, USING THE CREATE/TRUNCATE PARTITION METHOD 

DECLARE
   V_NEXT_DATE      DATE;
   V_CUTOFF_DATE    DATE;
   V_DAYS_TO_KEEP   NUMBER         := 30;
   V_CUTOFF_PART    VARCHAR2 (100);
   MNTH             VARCHAR2 (2);
   MON              VARCHAR2 (3);
   DY               VARCHAR2 (2);
   YR               VARCHAR2 (4);
   STMT             VARCHAR2 (500);

   CURSOR C_TRUNC_PART (P_CUTOFF_PART IN VARCHAR2)
   IS
      SELECT    'alter table '
             || TABLE_NAME
             || ' truncate partition '
             || PARTITION_NAME 
             || ' update global indexes'
             AS "statement"
        FROM USER_TAB_PARTITIONS
       WHERE TABLE_NAME = 'TRANS_SAMPLE_PARTITIONED'
         AND PARTITION_NAME < P_CUTOFF_PART;
BEGIN
   --FIRST CREATE TOMORROW's partition
   v_next_date := SYSDATE + 1;
   mnth := TO_CHAR (v_next_date, 'MM');
   mon := TO_CHAR (v_next_date, 'MON');
   dy := TO_CHAR (v_next_date, 'DD');
   yr := TO_CHAR (v_next_date, 'YYYY');
   stmt :=
         'ALTER TABLE TRANS_SAMPLE_PARTITIONED ADD PARTITION TSP_'
      || yr
      || mnth
      || dy
      || ' VALUES LESS THAN (TO_DATE('
      || CHR (39)
      || yr
      || '-'
      || mon
      || '-'
      || dy
      || CHR (39)
      || ','
      || CHR (39)
      || 'YYYY-MON-DD'
      || CHR (39)
      || '))  TABLESPACE TEST UPDATE GLOBAL INDEXES';
   DBMS_OUTPUT.put_line (stmt);

   BEGIN
      EXECUTE IMMEDIATE stmt;
   EXCEPTION
      WHEN OTHERS
      THEN
         NULL;
   END;

   --Compute the name of the Cutoff partition
   v_cutoff_date := TRUNC(SYSDATE) + 1 - v_days_to_keep;
   DBMS_OUTPUT.put_line (   'CUT OFF DATE IS '
                         || TO_CHAR (v_cutoff_date, 'MM-DD-YYYY')
                        );
   mnth := TO_CHAR (v_cutoff_date, 'MM');
   mon := TO_CHAR (v_cutoff_date, 'MON');
   dy := TO_CHAR (v_cutoff_date, 'DD');
   yr := TO_CHAR (v_cutoff_date, 'YYYY');
   v_cutoff_part := 'TSP_' || yr || mnth || dy;

   --Next Truncate Partitions older than v_days_to_keep
   BEGIN
      OPEN c_trunc_part (v_cutoff_part);

      LOOP
         FETCH c_trunc_part
          INTO stmt;

         EXIT WHEN c_trunc_part%NOTFOUND;

         BEGIN
            DBMS_OUTPUT.put_line (stmt);

            EXECUTE IMMEDIATE stmt;
         EXCEPTION
            WHEN OTHERS
            THEN
               NULL;
         END;
      END LOOP;

      CLOSE c_trunc_part;
   END;
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line (SQLCODE);
END;
/

EXECUTE DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'SIEBEL',TABNAME => 'TRANS_SAMPLE_PARTITIONED', GRANULARITY=>'ALL', CASCADE=>TRUE);

SELECT PARTITION_NAME, NUM_ROWS FROM USER_TAB_PARTITIONS WHERE TABLE_NAME='TRANS_SAMPLE_PARTITIONED' ORDER BY PARTITION_POSITION;

SELECT COUNT (*)
  FROM trans_sample_partitioned;

SELECT   create_date, COUNT (txn_id)
    FROM trans_sample_partitioned
GROUP BY create_date;

SPOOL OFF;