Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2005
    Posts
    1

    Unanswered: Oracle problem in split partition

    Please help me
    I have a need to split partition when insert new row. So I do it in Before insert trigger.

    CREATE TABLE TempTable
    (
    ID NUMBER (12) NOT NULL
    )
    PARTITION BY RANGE (ID)
    (
    PARTITION TempTable_R VALUES LESS THAN (MAXVALUE)
    )

    CREATE OR REPLACE TRIGGER TempTableBefInsTrigger
    BEFORE INSERT ON TempTable
    REFERENCING NEW AS newRow
    FOR EACH ROW
    DECLARE
    PRAGMA AUTONOMOUS_TRANSACTION;
    BEGIN
    Insert_PROC(:newRow.ID);
    END TempTable;


    CREATE OR REPLACE PROCEDURE Insert_PROC (ID NUMBER) AS
    PRAGMA AUTONOMOUS_TRANSACTION;
    msgResult NVARCHAR2(1000);
    sqlString VARCHAR2(1000);
    BEGIN
    sqlString := 'ALTER TABLE TempTable ADD PARTITION TempTable_R AT (ID) INTO ( PARTITION TempTable_' || ID || ', PARTITION TempTable_R)';
    BEGIN
    execute immediate sqlString;
    EXCEPTION
    WHEN OTHERS THEN msgResult := SQLERRM;
    dbms_output.put_line('Error TempTable table: ' || msgResult);
    END;
    END;

    If I insert:
    INSERT INTO TempTable VALUES(1);
    COMMIT;
    INSERT INTO TempTable VALUES(100);
    COMMIT;

    -> Split OK for all;

    But If I insert:
    INSERT INTO TempTable VALUES(1);
    INSERT INTO TempTable VALUES(100);
    COMMIT;

    Error: ORA-00054: resource busy and acquire with NOWAIT specified

    I think doing an DDL in a trigger is not good. But I have a needed to do that
    So, how do I can split partition any time when i insert new record.
    Please help!!!!

    Thanks.

  2. #2
    Join Date
    Nov 2005
    Posts
    17
    It looks like you are trying to do a combination of ADD and SPLIT. Since the table is defined with a MAXVALUE partition, you cannot ADD. Try a SPLIT instead. I have no idea if this is somehow related to the exception, but it may be a start.

  3. #3
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    You are locking yourself out, you cant do DDL on an object in the middle of a transaction. Try a regular job to check if you need a new partition instead.

    Alan

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •