Error in insert - 11g Standard Edition
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: Error in insert - 11g Standard Edition

  1. #1
    Join Date
    Nov 2013
    Posts
    3

    Error in insert - 11g Standard Edition

    Hi all!
    I have encountered an error in a transaction.

    When the application try to insert n .pdf or .jpg files in a table (field in BLOB data type), in dependence on them sizes it fail. The error generated is the ORA-00001: unique constraint (string.string) violated but the sequence that populate the _id works!

    In another db the transaction works, and the only difference between the first and second database is the edition: where fail we have the Standard, the other is Enterprise.

    Thanks!

  2. #2
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,478

    Cool

    Quote Originally Posted by LadyZero View Post
    Hi all!
    I have encountered an error in a transaction.

    When the application try to insert n .pdf or .jpg files in a table (field in BLOB data type), in dependence on them sizes it fail. The error generated is the ORA-00001: unique constraint (string.string) violated but the sequence that populate the _id works!

    In another db the transaction works, and the only difference between the first and second database is the edition: where fail we have the Standard, the other is Enterprise.

    Thanks!
    The ORA-00001 error:

    1) Does not behave different depending on database edition.
    2) Except for the constraints, does not care if your data is pdf of jpg.
    3) You are trying to duplicate a key value that is constrained as "unique", either the primary key or other.

    Compare the next value of the sequence that generate your "_id" column with the following query:
    Code:
    SELECT MAX(id) FROM MyTable;
    Good luck!
    "The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb

  3. #3
    Join Date
    Nov 2013
    Posts
    3
    Thanks a lot for your support!
    There's not a problem with the nextvalue of the sequence and the max value of the id column. They are different and the first one is bigger because of the failure of the last tried inserts.

    I was thinking about a maximum size in byte of an insert transaction with BLOB data type. Is it possible?
    Because when the sum of the byte size of the files is bigger than 2 MB it fail!
    Else, when the sum < 2MB, not.

    Any ideas about this?

  4. #4
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Quote Originally Posted by LadyZero View Post
    I was thinking about a maximum size in byte of an insert transaction with BLOB data type. Is it possible?
    Because when the sum of the byte size of the files is bigger than 2 MB it fail!
    Else, when the sum < 2MB, not.
    ORA-00001, as well pointed out by LKBrwn_DBA means that the system has detected a unique constraint violation - how many unique constraints does the target table has including the PK?

    On the other hand, if you can prove beyond any doubts that with exactly the same data but a smaller BLOB the statement does not fails I would suggest to open and SR with Oracle - Oracle support is going to love to see something like that.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  5. #5
    Join Date
    Nov 2013
    Posts
    3
    I omitted an important info. The exact error message is: ORA-00001: unique constraint (SYS.I_OBJ1) violated.
    What about the SYS.I_OBJ1 index?

  6. #6
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,013
    Quote Originally Posted by LadyZero View Post
    What about the SYS.I_OBJ1 index?
    What about the SYS.I_OBJ1 index? You should probably describe the table and show what unique indexes exist on that table. But you are definitely getting duplicate values for the columns in the SYS.I_OBJ1 index. Also why does sys own the index? You should drop and recreate the index with the schema owner as the owner of the index.

    Essentially, no one can help you until you let them know exactly what you are doing.
    this space intentionally left blank

  7. #7
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Quote Originally Posted by LadyZero View Post
    I omitted an important info. The exact error message is: ORA-00001: unique constraint (SYS.I_OBJ1) violated.
    What about the SYS.I_OBJ1 index?
    Would you please check on dba_indexes to which table is sys.i_obj1 index pointing at?
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

Posting Permissions

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



Click Here to Expand Forum to Full Width