Forum Stats

  • 3,852,764 Users
  • 2,264,135 Discussions
  • 7,905,134 Comments

Discussions

Before insert TRIGGER to create partitions problem

676821
676821 Member Posts: 19
edited Jan 14, 2009 11:37AM in SQL & PL/SQL
Hi there,

I´m having a problem with the following situation in Oracle 8i:

I have a table TEST_TABLE, which is partitioned by range using a DATE column. The idea is to have one partition for each month, so the HIGH_VALUE of the partitions is always the first day of the of the following month that the partition represents.

I created a BEFORE INSERT TRIGGER on the table TEST_TABLE, which tests if the partition for the month of the record that is being inserted exists and, in case it doesn´t, an AUTONOMOUS_TRANSACTION PROC is called to create the TRIGGER.

Running the code below one can see that although the partitions are being created as expected, when you try to insert a record with a date higher than that of the last partition for the first time, this error is returned:
ORA-14400: inserted partition key is beyond highest legal partition key.

Notice that if you run the same insert statement again, it´s inserted correctly on the partition that was created on the first try.

I´ll appreciate any help with this matter.

code
----------------
CREATE TABLE TEST_TABLE (
ID NUMBER,
DT DATE
)
TABLESPACE USERS
PARTITION BY RANGE (DT)
(
PARTITION PART_B42009 VALUES LESS THAN (TO_DATE('2009-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
LOGGING
TABLESPACE USERS
);
/

CREATE OR REPLACE PROCEDURE SP_ADD_PARTITION(P_DATE TEST_TABLE.DT%TYPE)
IS
PRAGMA AUTONOMOUS_TRANSACTION;
V_STR VARCHAR2(500);
BEGIN

V_STR := 'ALTER TABLE TEST_TABLE ADD'
|| ' PARTITION PART_' || TO_CHAR(P_DATE, 'YYYYMM')
|| ' VALUES LESS THAN ( TO_DATE ( '''
|| TO_CHAR(ADD_MONTHS(P_DATE, 1), 'YYYY-MM') || '-01 00:00:00'', '
|| '''SYYYY-MM-DD HH24:MI:SS'', ''NLS_CALENDAR=GREGORIAN''))';

EXECUTE IMMEDIATE(V_STR);

END SP_ADD_PARTITION;
/

CREATE OR REPLACE TRIGGER TR_B_I_R_TEST_TABLE
BEFORE INSERT
ON TEST_TABLE FOR EACH ROW
DECLARE
V_PARTITION_EXISTS NUMBER;
BEGIN

IF :NEW.DT >= TO_DATE('2009-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS') THEN

EXECUTE IMMEDIATE ('SELECT COUNT(1)'
|| ' FROM all_tab_partitions atp'
|| ' WHERE atp.table_name = ''TEST_TABLE'' '
|| ' AND atp.PARTITION_NAME = :v1 ')
INTO V_PARTITION_EXISTS
USING 'PART_' || TO_CHAR(:NEW.DT, 'YYYYMM');

IF V_PARTITION_EXISTS = 0 THEN

DBMS_OUTPUT.PUT_LINE ('Partition [' || 'PART_' || TO_CHAR(:NEW.DT, 'YYYYMM') || '] does not exist!');
DBMS_OUTPUT.PUT_LINE ('Creating..');
SP_ADD_PARTITION ( :NEW.DT );
DBMS_OUTPUT.PUT_LINE ('Success.');

EXECUTE IMMEDIATE ('SELECT COUNT(1)'
|| ' FROM all_tab_partitions atp'
|| ' WHERE atp.table_name = ''TEST_TABLE'' '
|| ' AND atp.PARTITION_NAME = :v1 ')
INTO V_PARTITION_EXISTS
USING 'PART_' || TO_CHAR(:NEW.DT, 'YYYYMM');

IF V_PARTITION_EXISTS = 1 THEN
DBMS_OUTPUT.PUT_LINE ('it´s visible at this stage..');
ELSE
DBMS_OUTPUT.PUT_LINE ('it´s not visible at this stage..');
END IF;

ELSE

DBMS_OUTPUT.PUT_LINE ('Partition [' || 'PART_' || TO_CHAR(:NEW.DT, 'YYYYMM')
|| '] already exists!');

END IF;

END IF;

DBMS_OUTPUT.PUT_LINE ('Continuing with insertion..');

END TR_B_I_R_TEST_TABLE;
/

-- Goes to the lower partition
INSERT INTO TEST_TABLE VALUES (1, TO_DATE('2008-12-31 23:59:59','YYYY-MM-DD HH24:MI:SS'));

-- Returns error on the first try
INSERT INTO TEST_TABLE VALUES (2, TO_DATE('2009-01-01 00:00:01','YYYY-MM-DD HH24:MI:SS'));

----------------
Tagged:

Best Answer

  • APC
    APC Member Posts: 11,316 Bronze Crown
    Answer ✓
    It's the use of the AUTONOMOUS TRANSACTION pragma. Your current transaction cannot see the result of that DDL because it occurs outside of the current transaction. The clue is in the name.

    Of course, you cannot execute the DDL in a trigger without using that pragma, so you're pretty well stymied. There is a solution in 11g but that's not going to help you. Unfortunately, your only option is to pre-create the required partitions ahead of the need. For instance, you could have a DBMS JOB to create a partition for the next month which runs on the last day of each month (or whatever date makes business sense).

    Cheers, APC

    blog: http://radiofreetooting.blogspot.com

Answers

  • APC
    APC Member Posts: 11,316 Bronze Crown
    Answer ✓
    It's the use of the AUTONOMOUS TRANSACTION pragma. Your current transaction cannot see the result of that DDL because it occurs outside of the current transaction. The clue is in the name.

    Of course, you cannot execute the DDL in a trigger without using that pragma, so you're pretty well stymied. There is a solution in 11g but that's not going to help you. Unfortunately, your only option is to pre-create the required partitions ahead of the need. For instance, you could have a DBMS JOB to create a partition for the next month which runs on the last day of each month (or whatever date makes business sense).

    Cheers, APC

    blog: http://radiofreetooting.blogspot.com
  • Laurent Schneider
    Laurent Schneider Member Posts: 5,220 Bronze Trophy
    edited Jan 14, 2009 9:25AM
    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
    Laurent Schneider
  • Laurent Schneider
    Laurent Schneider Member Posts: 5,220 Bronze Trophy
    or, much easier and cleaner,

    every sunday, check for values in MAXVALUE partition and move those rows in new partitions...
    Laurent Schneider
  • OrionNet
    OrionNet Member Posts: 4,542 Gold Trophy
    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
    OrionNet
  • 676821
    676821 Member Posts: 19
    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
This discussion has been closed.