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]........
I am rebuilding some UNUSABLE local index partitions on Oracle 8.1.7.4.0 (64bit) database . The platform is a HPUX machine.
The DDL of the partition table/indexes: ========================= CREATE TABLE TESTME ( INST_NO CHAR(3) NOT NULL, ACCT_NO CHAR(16) NOT NULL, REC_NO CHAR(9) NOT NULL, TRAN_TYPE CHAR(2) DEFAULT ' ', STAT CHAR(2) DEFAULT ' ', [code]...
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
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?
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:
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?
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 ?
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.
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 -
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.
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?
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
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.
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.
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
ALTER TABLE SNYT.PART_ESTD SPLIT PARTITION ESTD_M13_S22 AT ('ESTD', 13, '22') INTO (PARTITION ESTD_M13_S21, PARTITION ESTD_M13_S22) update indexes;
(per http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1401247200346349807) ================================================================= If Global index used
ALTER TABLE SNYT.PART_ESTD SPLIT PARTITION ESTD_M13_S22 AT ('ESTD', 13, '22') INTO (PARTITION ESTD_M13_S21, PARTITION ESTD_M13_S22) UPDATE GLOBAL INDEXES;
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)
I think that performance better partition table than non-partition table. How to assure partition table is better than non-partition table at SELECT operation?
I have compare a specific query EXPLAIN PLAN at partition table and non-partition table. both tables data is same. Is it true way or not?
I have a table that partitioned into six partitions. each partitions placed in different table space and every two table space placed it on a different hardisk
when I will do query select with the non-partition keys condition, how the search process ? whether the sequence (scan sequentially from partition 1 to partition 6) or partition in a hardisk is accessed at the same time with other partition in other hardisk. ( in the image, partition 1,4 accessed at the same time with partition 2,5 and 3,6)
Can i alter the table to create partition on non partition table, i have tried and could not create it. Do we have some other means to do it as this is the live table and cannot drop them else will lose the data.
Can I add range sub partition to a hash partition table. Example like this.
CREATE TABLE test ( test_id VARCHAR2(10 ) , test_TYPE VARCHAR2(5) , CREATE_DATE date ) partition by hash (test_id, test_type) Partitions 3 SUBPARTITION BY RANGE (CREATE_DATE);
When Tried, I am getting syntax error as invalid option.
When I am trying to insert record from tbl_mittal into tbl_temp table. I am facing "ORA-14400: inserted partition key does not map to any partition" error
SQL> insert into tbl_temp select * from tbl_mittal; insert into tbl_temp select * from tbl_mittal * ERROR at line 1: ORA-14400: inserted partition key does not map to any partition
AS tbl_mittal is having hugh number of records so I am providing only few rows from tbl_mittal table as test data.
SNRCTL> stat LISTENER Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER))) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for IBM/AIX RISC System/6000: Version 11.2.0.1.0 - Production Start Date 19-JAN-2013 00:50:10 Uptime 0 days 0 hr. 29 min. 51 sec Trace Level off Security ON: Local OS Authentication [code]....
In every oracle documentation for e.g:11.2 Scan and Node TNS Listener Setup Examples [ID 1070607.1] we found the local listener status showing both local-ip and vip. Why is not showing in our case?
How to find the size pf a partition in a partition table?I guess we need to query views like dba_tab_partitions but I am not very sure. will running dbms_stats.gather_table_stats('schema_name,'table_name,'partition_name')
primary key constraint on transaction_dtl_bk is affecting the insertion of next correct rows.
CREATE OR REPLACE PROCEDURE NP_DB.san_po_nt_wnpg_1 ( dt DATE ) IS v_sql_error VARCHAR2 (100); -- added by sanjiv v_sqlcode VARCHAR2 (100); ---- added by sanjiv added by sanjiv
I think that performance better partition table than non-partition table. How to assure partition table is better than non-partition table at SELECT operation?
I have compare a specific query EXPLAIN PLAN at partition table and non-partition table. both tables data is same. Is it true way or not?
