SQLcl: MCP Server & SQL Prompt

I second Andrew arguments. In 11g, you have automatic partition creation with interval partitioning.

In 8i, DBMS_JOB rules !

Something like (picture)

table T (partition p2007, partition p2008, partition MAXVALUES)

trigger TR if values > (select max(highvalue) from user_tab_parts) then DBMS_JOB.CREATE_JOB('at midnight, split partition MAXVALUES in MAXVALUES/P2009 ');
and insert row in partition MAXVALUES

HTH
Laurent

Edited by: Laurent Schneider on Jan 14, 2009 3:24 PM

or, much easier and cleaner,

every sunday, check for values in MAXVALUE partition and move those rows in new partitions...

Hello,

You don't need trigger to identify missing partitions and add them, see following procedure

using v_missing_months you can create n number of partitions ahead of time. You can also use this to create partition one month in advance and it will work for any "RANGE" partitioned table. Then you can scheduled this procedure to run using dbms_jobs or from cron job (using unix)

DECLARE
      TYPE partrec IS RECORD (
         table_name        varchar2 (30),
         partition_name    varchar2 (30),
         upperbound        varchar2 (83),
         tablespace_name   varchar2 (70)
      );

      CURSOR p_cur
      IS
         SELECT   table_name, partition_name, high_value, tablespace_name
             FROM user_tab_partitions
            WHERE high_value_length > 2
                ORDER BY partition_position DESC;

      CURSOR part_cur (i_table_name IN varchar2)
      IS
         SELECT   table_name, partition_name, high_value, tablespace_name
             FROM user_tab_partitions
            WHERE table_name = i_table_name
         ORDER BY partition_position DESC;

      prec                         partrec;
      v_last_partition             varchar2 (50);
      v_next_partition             varchar2 (50);
      v_last_part_date             varchar2 (50);
      v_next_part_date             date;
      v_part_tmp_date              date;
      v_part_date                  date;
      v_initial_date               date;
      i_initial_date               date;
      v_sql                        varchar2 (300);
      v_initial_part               varchar2 (30);
      i_initial_part               varchar2 (30);

      c_dummy_partition   CONSTANT varchar2 (5)   := 'DUMMY';
      parent_tname                 varchar2 (30);
      v_missing_months             number;
      i                            number;

   BEGIN

      EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_DATE_FORMAT="YYYYMMDD"';
      --
      -- Adding partitions to existing range partitioned tables
      --
      FOR cur IN p_cur
      LOOP
         OPEN part_cur (cur.table_name);
         FETCH part_cur
          INTO prec;
        CLOSE part_cur;
         v_last_partition := prec.partition_name;
        -- DBMS_OUTPUT.put_line ('Last partition Name=' || v_last_partition);
         v_last_part_date := SUBSTR (prec.upperbound, 10, 20);
        -- DBMS_OUTPUT.put_line ('Last partition date' || v_last_part_date);
         v_part_date := TO_DATE (v_last_part_date, 'YYYY-MM-DD HH24:MI:SS');
        -- DBMS_OUTPUT.put_line ('Last Partition Date = ' || v_part_date);
         v_part_tmp_date := v_part_date;
         v_missing_months := MONTHS_BETWEEN (SYSDATE, v_part_date);

        -- NOTE : MODIFY following line to create partition in one month or n month in advance.
         v_missing_months := v_missing_months + 12; 

        -- DBMS_OUTPUT.put_line
                  --        (   'No of monthly partitions  will be created is:'
                     --      || CEIL (v_missing_months)
                     --     );

         FOR i IN 1 .. v_missing_months
         LOOP
            IF (v_part_date = LAST_DAY (v_part_date + i))
            THEN
               v_part_date := v_part_date + i;
               v_next_part_date := ADD_MONTHS (v_part_date, i);
            ELSE
               v_next_part_date := ADD_MONTHS (v_part_date, i);
            END IF;
            v_part_tmp_date := v_part_tmp_date + i;
            DBMS_OUTPUT.put_line (   'Next partition Date = '
                                || TO_CHAR (v_next_part_date, 'YYYYMM')
                             );

            v_next_partition :=
               REPLACE (v_last_partition,
                        SUBSTR (v_last_partition, -6),
                        TO_CHAR (v_next_part_date - i, 'YYYYMM')
                       );

            DBMS_OUTPUT.put_line ('New Partition Name = ' || v_next_partition);
            v_sql :=
                  'ALTER TABLE '
               || prec.table_name
               || ' ADD PARTITION '
               || v_next_partition
               || ' VALUES LESS THAN ( TO_DATE('''
               || v_next_part_date
               || ' 00:00:00'', ''YYYYMMDD HH24:MI:SS''))'
               || ' tablespace '
               || prec.tablespace_name;

  DBMS_OUTPUT.put_line (v_sql);
           EXECUTE IMMEDIATE v_sql;
            COMMIT;
        END LOOP;
      END LOOP;
   EXCEPTION
      WHEN OTHERS
      THEN
         DBMS_OUTPUT.put_line (SUBSTR (SQLERRM, 1, 300));
         RAISE;
   END;

Regards

Thanks a lot for all the replies.

Altough they were all helpful, for some reason, I was asked not to use JOBS (if possible).

After APC confirmed my first solution would not work I gave it some thought and came up with another approach which might not be the most appropriate solution in this case, but the result was achieved without JOBS.

Solution I´m using:

The lower partition, which is created with the table, will hold the current month´s records and the previous ones;

A BEFORE INSERT TRIGGER will call an AUTONOMOUS TRANSACTION PROCEDURE that check if next month´s partition (relative to the record that´s being inserted) is already created. If it´s not, the procedure then creates it;

The insertion now will work, as the partition created outside the transaction is for next month. This way the partition for the current month is always going to be available.

PS.: It might be important to point out that this solution only works in this situation because the business rules ensure that there will never be a record with a date greater than the current date. If this rule didn´t exist the solution would not be suitable.

Thanks again.

Bruno