How To Handle Unique Constraint Violation

Feb 5, 2013

I've set up Oracle Streams replication between multiple N-way zones like this:

CODE------------------------------
Nway1 |Intersect| Nway2
DB1 --|-- DB3 --|-- DB5              DB1, DB2, DB3, DB4 - N-way1
  |   |    |    |    |               DB3, DB4, DB5, DB6 - N-way2
DB2 --|-- DB4 --|-- DB6              DB3, DB4 - intersection zone
      |         |  
-------------------------------

Physically DB1 .... DBN connected sequentially, so I want to prevent segmentation if some DB is unaccessible, but at the same time fight unneeded redundancy which uses too much link bandwidth to send N-1 LCR-s to all members of a single N-way group (so I want to split one big N-way zone into smaller ones and sequentially connect them into chain - it significantly reduces load on link if N is big enough (>10)). Also I want to have 2 DB in intersection zone to prevent single point of failure.

This scheme has one drawback - if change originated on DB3 or DB4, then it will be propagated (more correctly - applied and captured again) to DB5 and DB6 by both DB1 and DB2 (and, as far as I know, I have no means in capture rules to detect state of DB2 from DB1 and vise versa), so on DB5 and DB6 I get:

CODEORA-00001: unique constraint (DUMMYUSR.UNIQUE_RECORDS) violated error

I've set up standard conflict handler for apply process:

CODE

declare                                                                                        
    cols DBMS_UTILITY.NAME_ARRAY;                                                              
begin                                                                                          
    cols(1) := 'no';                                                                          
    cols(2) := 'name';                                                                        
    cols(3) := 'ddate';                                                                        
    dbms_apply_adm.set_update_conflict_handler(                                                
        object_name       => 'DUMMYUSR.DUMMYTBL',                                              
        method_name       => 'DISCARD',                                                        
        resolution_column => 'no',                                                            
        column_list       => cols                                                              
    );                                                                                        
end;

but it seems that it does not handle uniqueness conflicts. What is the best way to handle uniqueness conflict (is there a better way than to write custom error handler) and how serious is the impact on insert performance of having unique constraint and corresponding error handler. (In real world I will have to deal with tables with metainformation and without any keys).

Also, how to proceed with no error or raise exception from apply error handler with error that caused this handler to run? In oracle docs I can find only example that modifies LCR and runs lcr.EXECUTE(TRUE), but what to do if I don't want to reexecute LCR, but merely check error code and propagate error if it is not ORA-00001?

View 1 Replies


ADVERTISEMENT

RMAN Unique Constraint Violation

Aug 23, 2011

Our RMAN backup failed because of the error:

starting full resync of recovery catalog
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of backup command at 08/22/2011 22:19:07
RMAN-03014: implicit resync of recovery catalog failed
RMAN-03009: failure of full resync command on default channel at 08/22/2011 22:19:07
ORA-00001: unique constraint (ODBA.DF_U2) violated

Searching metalink, its related to bug 6014994 and the proposed workaround is to delete the constraint:

Cause:
Dropping a datafile from a tablespace followed immediately by adding
another datafile to the same tablespace will cause this Unique Key violation.
Taking a RMAN debug trace will show the file# related to the error
This is reported as bug <<6014994>> Unpublished on Metalink and fixed in 11g
RMAN RESYNC catalog signals DF_U2 violated constraint when a file# is reused in
the same tablespace
Solution
WORKAROUND: Drop df_u2 constraint

where can I delete the constraint, is it possible to do in RMAN or in the target instance?

View 1 Replies View Related

SQL & PL/SQL :: Trigger To Handle Primary Key Violation?

Sep 5, 2012

I am trying to handle PK violation error on a certain table, on INSERT, my best guess is I should use a trigger. The basic idea is this:

The table consists of 7 columns, and 6 of them are PK, and the seventh one is "amount". I want to handle PK violation in such way that, if it occurs during INSERT, then instead of inserting a new row, it should just update the "amount".

View 16 Replies View Related

SQL & PL/SQL :: Get The Rows That Produced A Constraint Violation

Nov 5, 2012

drop table t1
/
create table t1
(
id int PRIMARY KEY
)
/
insert into t1
select 1
from dual
union all
select 2
from dual
union all
select 2
from dual
union all
select 3
from dual
/

ERREUR à la ligne 1 :
ORA-00001: violation de contrainte unique (SYSTEM.SYS_C0011990)

how to get the rows that produced the constraint violation ?

View 10 Replies View Related

Forms :: Oracle Unable To Update Record - Unique Key Violation Error

May 10, 2011

I have the search screen in my form so after searching if i select the row by using the button it will navigate the first tab page that is "gas" screen here if i tried to change the value like update and save the form it is not allowing me to update the value raising the error message "oracle unable to insert the record". if i see the "display error" in menu it is having the select statement with error "unique key violation error ora-00001".

In save button containe "commit_form"

View 15 Replies View Related

Server Utilities :: Data Pump Import Order - Causing Constraint Violation

Nov 16, 2010

We are trying to import data into existing tables in a schema using data pump

However the foreign key tables are being imported first and then the master table data thus violating the constraints

Apparently it seems larger tables are being imported first regardless of referential integrity constraints thus causing constraint violation (contrary to my understanding)

Is it a normal behaviour during data pump import?

Is it possible that the keys being sequence generated are causing this?

As I understand import will commit after each table In that case can we defer commit at all at the expense of large undo, set constraints to deferrable and try the import?

View 3 Replies View Related

Unique Constraint Violated?

Mar 26, 2007

From a step by step instructions I'm asked to put the following into sql*plus:

CREATE TABLE Lab2Lecturer
(staffNO VarCHAR2(10) NOT NULL,
title VARCHAR2(3),
fName VARCHAR2(30),

[code]...

Then the following:

INSERT INTO Lab2Lecturer
(staffNO, title, fName, lName, streetAddress, suburb, city, postCode, country, lecturerLevel, bankNO, bankName, salary, workLoad, researchArea)
VALUES
('1000', 'Dr', 'Johanna','Santoso',
'3 Robinson Av', 'Kew', 'Melbourne', '3080', 'Australia', 'C', '1000567237', 'CommBank', 65000.00,1.0, 'O-R DB');

and finally,

INSERT INTO Lab2Lecturer
(staffNO, title, fName, lName, streetAddress, suburb, city, postCode,country, lecturerLevel, BankNO,bankName, salary, workLoad, researchArea)
VALUES
('1000', 'Dr', 'Justine', 'Martin', '6 Algorithm AV', 'Montmorency', 'Melbourne', '3089', 'Australia', 'D', '1000123456', 'CommBank', 89000.00, 1.0, 'CBR');

when I try entering in the second one I get an error 'unique constraint violated'.So whats wrong exactly?

View 1 Replies View Related

PL/SQL :: Unique Constraint Violated

Oct 4, 2013

My DB version is

BANNER                                                     
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production                       
CORE 10.2.0.4.0 Production                                     
TNS for Linux: Version 10.2.0.4.0 - Production               
NLSRTL Version 10.2.0.4.0 - Production  

These error codes I'm getting in production.First of all, I've no duplicates present in that table for which this error has been raised.I've checked the index and related columns as well. NO DATA is there.So NO CHANCE for unique constraint violation.

SELECT * FROM ORDER_OCC_REQUISITION_X_REF WHERE LAB_ORDER_OCC_TEST_ID IN(SELECT LAB_ORDER_OCC_TEST_ID FROM LAB_ORDER_OCC_TEST WHERE LAB_ORDER_OCC_ID = 7944858);

no rows selected

 Now when I'm trying to insert one row inside this table I'm getting this error, as you are seeing no records for this occurrence_id. 

SELECT * FROM USER_INDEXES WHERE INDEX_NAME = 'ORD_OCC_REQ_UQ_TEST_IX_04';
--ORDER_OCC_REQUISITION_X_REF (Table name)
--MERGE_DT, LAB_ORDER_OCC_TEST_ID, TEST_ID, ACTIVE_YN (columns for the index 'ORD_OCC_REQ_UQ_TEST_IX_04')
As you can see there is no data then this error should not be raised.  Update procedure.
/*******************************************************************************************************************
     * Name                    : UPDATE_REQUISITION_X_REF
     * Description             : This Procedure update ORDER_OCC_REQUISITION_X_REF table with requisition_id
     *                           that was generated due to merge process.
     * In  parameters          : IN_merge_id         NUMBER   The order_ref_no of the orders to be merged (comma seperated)
     ***********************************************************************************************************************/
    PROCEDURE   UPDATE_REQUISITION_X_REF ( IN_merge_id  IN TT_ORD_REQUISITION_WORK_AREA.merge_id%TYPE)
    IS
  
[Code]....

View 8 Replies View Related

ORA-00001 - Unique Constraint Violated

May 14, 2010

I have faced an error as below:-

1 : 23000 : java.sql.BatchUpdateException: ORA-00001: unique constraint (SDS1.PK_EXP_TXT) violated
1 : 23000 : java.sql.SQLException: ORA-00001: unique constraint (SDS1.PK_EXP_TXT) violated
java.sql.BatchUpdateException: ORA-00001: unique constraint (SDS1.PK_EXP_TXT) violated
at oracle.jdbc.driver.DatabaseError.throwBatchUpdateException(DatabaseError.java:367)
at oracle.jdbc.driver.OraclePreparedStatement.executeBatch(OraclePreparedStatement.java:9119)
[Code] ......

After i check data in all_indexes table, i have found some old data which are belongs to 2008 and 2009. The sample as below:-

OWNER INDEX_NAME LAST_ANALYZED
SDS1PK_ACTION 31-OCT-09 07:04:49
SDS1AK_ACTION 31-OCT-09 07:04:49
SDS1PK_COND 31-OCT-09 07:04:50
SDS1AK_COND 31-OCT-09 07:04:50
SDS1COND_FK1 31-OCT-09 07:04:50
SDS1COND_FK2 31-OCT-09 07:04:50

is it the problem due to the old data not remove from the all_indexes table ?.. if YES is it I have to delete the old data manually from the all_indexes table ?

View 7 Replies View Related

SQL & PL/SQL :: Unique Constraint When 1 Column Is Not Null

Jun 15, 2012

I have 3 columns in a table: colX, colY, colZ.

Trying to find a way to prevent duplicates with these, but only if colX is not null.

For example, if there are already values for: colX = 1, colY = 1, colZ = 1

then:

Allowed: colX = null, colY = 1, colZ = 1
Not allowed: colX = 1, colY = 1, colZ = 1

I can't create a unique constraint on these columns because there are many null values for column colX, and as mentioned, when colX is null, colY and colZ can be any values.

I also tried using a before insert trigger to find duplicates before posting and raise an error if found, but this causes an ORA-04091 mutating error since the trigger in the table is referencing itself to check for duplicates.

Also, I know there is something called a function based index, but I cannot use those with my code, so I need another solution if possible.

View 4 Replies View Related

SQL & PL/SQL :: ORA00001 Unique Constraint Violated

Nov 4, 2012

I also put in the relevant code in case it's needed.

SQL> @lab_05_01.sql
SQL> -- Oracle Database 10g: Administration Workshop II
SQL> -- Oracle Server Technologies - Curriculum Development
SQL> --
SQL> -- ***Training purposes only***
SQL> -- ***Not appropriate for production use***
SQL> --
SQL> -- This script performs a batch promotion update.
SQL> -- The logic of the updates is not important -

[code]....

View 2 Replies View Related

Forms :: ORA-00001 - Unique Constraint?

Mar 15, 2011

I have disabled the primary key on a table using this...ALTER TABLE C_LOV_BOROUGH DISABLE CONSTRAINT CLOVBOROUGH_PK CASCADE;

but now when I try to insert duplicate value. It gives this error.

ORA-00001: unique constraint (LOT_DBA.CLOVBOROUGH_PK) violated

View 5 Replies View Related

SQL & PL/SQL :: Unique Constraint Versus Distinct?

Apr 30, 2013

about the functionalty w.r.t. unique constraint and Distinct clause. Below is the example which is confusing me lot.

--Below statement will create table and unique constraint
Create Table A (A Varchar2 (10) Unique);
Insert Into A Values (Null);
Insert Into A Values (1);
Insert Into A Values (2);

[code]...

If we are saying each null value is having a unique value, then why oracle distinct showing records.

View 3 Replies View Related

Creating Unique Constraint That Excludes Null

May 22, 2013

I have a table where I want user to fill in unique values for a field which is easy to do.

Problem is sometimes the values can be null so an ordinary unique constraint does not work because multiple null records. Is there a way of validating only non null values to ensure all data entered that is non null is unique?

View 1 Replies View Related

SQL & PL/SQL :: Local Unique Constraint On Partition Table

Aug 2, 2011

I have created a table below, my TL asked me to create a local unique constraint for the below table.

I went through all sites and could not find the correct solution, how to create LOCAL UNIQUE CONSTRAINT ON SUB PARTITION TABLE and LOCAL UNIQUE INDEX ON PARTITION TABLE. Creating Local Unique constraint should take care of creating local unique index creation.

Unique key columns are DET,GDS,ARRIVE_DT

CREATE TABLE SUB_PAR_TAB
(
ID VARCHAR2(100) NOT NULL,
REGION VARCHAR2(40) NOT NULL,
SOURCE VARCHAR2(80) NOT NULL,
DET VARCHAR2(80) NOT NULL,
GDS VARCHAR2(40) NOT NULL,
ARRIVE_DTDATE,
SYS_SOURCE VARCHAR2(25) ,
[code]........

View 5 Replies View Related

Replication :: ORA-00001 - Unique Constraint Violated

Aug 3, 2008

I got this error message in my replication environment.

ORA-12012: error on auto execute of job 2182370
ORA-12008: error in materialized view refresh path
ORA-00001: unique constraint (TE.S_TE_MTH_DBASE_SALES_INFO_A_U1) violated
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 189

Materialized view and Base table having only one unique index. There are no referable constraints in my base table. Because source table not refer any other tables.

Even; materialized view log created using rowid because there are no primary key constraints in my base table.When I manually refresh materialized view I got the below error message

ORA-00001: unique constraint (TE.S_TE_MTH_DBASE_SALES_INFO_A_U1) violated

How it's possible? Because there is parent/child relationship in my source table and there is no way to enter duplicate records it's unique index.

View 8 Replies View Related

PL/SQL :: ORA-00001 / Unique Constraint Because ID Is Primary Key On TABLE1

Jun 27, 2012

I have in a plsql block somewhere a statement like

INSERT INTO TABLE1( id , col)
SELECT id, col
FROM TABLE2;

This statement returns an error ORA-00001: unique constraint because id is a primary key on TABLE1. I would like to know what is the value of id that raised the exception.

View 15 Replies View Related

SQL & PL/SQL :: Unique Constraint Error Due To Multiple Insert By Java?

Apr 18, 2010

I have two different java process trying to insert in the same time in the same table for the same trade. The structure of the table Report is

create table report
( TRADE_ID NUMBER,
VERSION NUMBER,
MESSAGE_TIME TIMESTAMP)

There is a unique key on (TRADE_ID and VERSION) So if a new trade_id is inserted, the version is set to 1 and the second becomes 2 and so on. The version is calculated as last version of the trade_id ie. version + 1. It was woking fine till a new Java process was build that fired inserts through ten different java instances at the same time resulting in unique key error. So in detail what is hapenning is if three records of trade_id's comes in at the same time it should allocate versions in a first come first serve basis and there should be three versions of trade id 1,2 and 3. Now due to the multiple instances they all seems to get fired at once and all ending up with version one and thus resulting in unique key constrain error while trying to insert into the table.

View 3 Replies View Related

Server Utilities :: ORA-00001 - Unique Constraint Violated Error During Impdp?

Oct 17, 2011

I am trying to import the dump using the impdp utility and followed the below steps I have disabled all the constraints and executed the below command

impdp DIRECTORY=EXPDP_DAILY_BACKUP_DIR DUMPFILE=expdp_QA_full_11102011_214501.dmp
logfile=imp_EXPDP_QA_full_12102011_203254.log remap_schema=prod:prod remap_tablespace=prod:prod
schemas=prod TABLE_EXISTS_ACTION=truncate content=data_only.

but I am getting the below error like this for 1 or 2 tables . and If I import those tables seperately its getting imported successfully. i am not getting the below error always .

ORA-31693: Table data object "PROD"."DAS_ID_GENERATOR" failed to load/unload and is being skipped due to error:
ORA-00001: unique constraint (PROD.DAS_ID_GENERATOR_P) violated
ORA-31693: Table data object "PROD"."TKT_DIST_SRV_STAT" failed to load/unload and is being skipped due to error:
ORA-00001: unique constraint (PROD.SERVER_STATS_P) violated

View 8 Replies View Related

Non Unique Index Using A Non Unique Field

Aug 14, 2013

Using Oracle 11g, below is the table, partitions, unique and non-unique local index: 

CREATE TABLE DOCA(  DOCA_ID  NUMBER  NOT NULL ,  DOCA_BKG_PAX_ID  NUMBER  NULL ,  ROW_PURGE_DATE  DATE  NULL ,)PARTITION BY RANGE(ROW_PURGE_DATE)INTERVAL(NUMTOYMINTERVAL(1, 'MONTH'))(  PARTITION P2007 VALUES LESS THAN (TO_DATE('01/01/2008', 'dd/mm/yyyy')),  PARTITION P200801 VALUES LESS THAN (TO_DATE('01/02/2008', 'dd/mm/yyyy')),)  TABLESPACE T0; ALTER TABLE DOCA ENABLE ROW MOVEMENT;    

CREATE UNIQUE INDEX XPKDOCA ON DOCA(  DOCA_ID  ASC,  ROW_PURGE_DATE ASC)LOCALREVERSE TABLESPACE I0;  ALTER TABLE DOCA  ADD CONSTRAINT  XPKDOCA PRIMARY KEY (DOCA_ID);    CREATE INDEX XFKDOCA_DOCA_BKG_PAX_ID ON DOCA(  DOCA_BKG_PAX_ID  ASC)LOCALREVERSETABLESPACE I0;  

I would like to know the difference between the performance of the unique and non-unique local indexes?.

View 10 Replies View Related

SQL & PL/SQL :: Primary Key Violation Error

Jun 25, 2013

In my application I am facing peculiar error. For easy understanding I am considering emp table. In my package P1 I have two procedures Proc1,Proc2.

Proc1 : It is receiving employee details and for checking if employee already exists it is calling Proc2.
Proc2 : Here it checks if employee exists. If exists it deletes old record, commit and inserts new record in sequence.

But however in few instances Proc2 is returning unique constraint (CONSTRAINT_NAME) violated. I am very much confused why this error is coming.

View 12 Replies View Related

Get Table Name / Constraint Name / Constraint Type With Join Processes In String Type

Dec 25, 2007

i want to get table name, constraint name, constraint type with join processes in string type. this is what i want: alter table tablename add constraint constraintname constrainttype(columnname)

View 1 Replies View Related

Client Tools :: Access Violation And F4 Functionality

Jan 14, 2012

I am using the Toad10.After i enter the user, Pwd it displays the Toad error that""Access violation at address 6761CB21 in module 'ORA805.dll'. Read of address 00000010 ""

while i try to use the F4 functionality, it again displays the above msg and Object not found...After i execute the query, all the characters data displays in chinees.

View 1 Replies View Related

Client Tools :: Access Violation Error In Toad?

Jun 2, 2008

What i have to do to overcome the following error..whenever im using toad im getting this error since start writing and executing procedures and functions.

access violation at address 006b5b1b in module 'toad.exe' read of address 0000011d

View 4 Replies View Related

Application Express :: Session State Protection Violation

Nov 21, 2012

I have upgraded from APEX 4.1 to 4.2.all read only items with dynamic action are arising below error:

session state protection violation: this may caused by manual alteration of protected item P2_IDACCSET.

View 16 Replies View Related

Application Express :: Session State Protection Violation Error

Jan 15, 2013

Using apex 4.2. I have created form item on page and item attribute DISABLES IN SETTINGS , set value as YES and SESSION STATE value as YES.

SETTINGS->DISABLES-> SESSION STATE

by doing this i am getting the error. Error is

Session state protection violation: This may be caused by manual alteration of protected page item P98_CHECK_AMOUNT. If you are unsure what caused this error, please contact the application administrator for assistance.

View 8 Replies View Related

Handle When No Value In (Select Into)?

Mar 24, 2008

I have a stored procedure that does a "select name into v_name" SQL statement, which works fine. The only problem is when the query finds no data (the procedure will error because there is no value to put into the variable). Now i have a work around to this by running the query first with a count statement (which will always have results) and then if it is not equal to 0, then i will run the select into.

My question is, is there a better way to handle this kind of issue?

View 1 Replies View Related

SQL & PL/SQL :: How To Handle The Exception

May 19, 2011

The following code is working fine,But the thing is if column already exists in the table,then also the other statements should be executed instead of coming out of procedure.SO how can I handle that exception??

SQL> CREATE OR REPLACE PROCEDURE sp_execparameters(tname IN VARCHAR2,
colname IN VARCHAR2,datatype IN VARCHAR2)
2 AS
3 v_sqlstr1 VARCHAR2(1000);
4 BEGIN
5 v_sqlstr1 := 'alter table '||tname||' add '||colname ||' '|| datatype ;
[code].........

View 3 Replies View Related

How To Handle The Old Data

Oct 6, 2013

we are using oracle database. We hold the company records in the database. The records of the company should be available at anytime but over years the database keeps growing. Now how to handle the old data. All the data is important but if this goes for few more years then we need more and more disk space to handle. Is there any efficient methodologies to handle the old data? For us old mean the data that is 10 year old. 

View 9 Replies View Related

SQL & PL/SQL :: How To Handle A File With UTL_FILE

Mar 11, 2013

I Got a scnario like a upload file contains

1.Header(Contains the File Name,Branch Name,MIS date)
2.Body(Customer Details)
3.Footer (File Name,Contians Total Number of Records and Number of Customers)

Like

CustomerDet_11-12-2013.csv,Chennai,11-12-2013
101,AAA,Savings,'M',10000
102,BBB,Current,'O',2000
103,CCC,Deposit Acct,'F',3000
CustomerDet_11-12-2013.csv,3,15000

How to handle this scnario through UTL_File

View 31 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved