-
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!
-
Originally Posted by LadyZero
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
-
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?
-
Originally Posted by LadyZero
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.
-
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?
-
Originally Posted by LadyZero
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.
-
Originally Posted by LadyZero
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|