When Inserting Data Unique Index Initially In Unusable State

Apr 9, 2013

We are getting an error as below when trying to load data into a table.

INSERT /*+ APPEND parallel(IA_SBSCR_DED_MAX,4) */ INTO EDW.IA_SBSCR_DED_MAX
*
ERROR at line 1:

ORA-12801: error signaled in parallel query server P000
ORA-26026: unique index EDW.XPKIA_SBSCR_DED_MAX_A initially in unusable state

The index has been rebuilt but we still have this issue.

View 8 Replies


ADVERTISEMENT

Index Is In Unusable State

Sep 1, 2010

I can see the error in alert log like

2437312:ORA-12801: error signaled in parallel query server P009
2437313:ORA-01502: index 'POS.XIETBK_POS_FACT_TRAN_DATE' or partition of such index is in unusable state

and tried to rebuild the index and i got following error.

ORA-14086: a partitioned index may not be rebuilt as a whole.

The table size for the index is large. we need to rebuild the hole index.

View 9 Replies View Related

SQL & PL/SQL :: ORA-01502 - Index Or Partition Of Such Index Is In Unusable State?

Nov 29, 2010

I am facing the error "ORA-01502: index or partition of such index is in unusable state " while loading the text data using
sql loader with direct path (direct = Y ,rows = 10000) option. Table consists an composite non unique index. If I query the dba indexes for the effected index it shows the index status as VALID. There was no maintaince done on the effected table or index. I have tried loading the same data using conventional path but didn't found any issues for the same.

View 3 Replies View Related

SQL & PL/SQL :: ORA-01502 / Partition Of Index Is In Unusable State

Aug 3, 2011

after merging two partitions into single partition (partition is by list) of a table ,when i analyzed the table it is giving this error : ORA-01502 INDEX TEST.PK_ID or partition of such index is in unusable state.

View 7 Replies View Related

SQL & PL/SQL :: ORA 20000 Index Partition In Unusable State?

Jan 3, 2012

ora 20000 index partition in an unusable state. what can i do

View 6 Replies View Related

Store Data In CLOD Data Type - How To Create A Unique Index

May 20, 2013

We have been recommended to store data in CLOD data type.

Sample data: 1:2:2000000:20000:4455:000099:444:099999:....etc it will grow to a large number.

We want to create a Unique index, for functional reason. Is it advised to create a unique index on a CLOB datatype?

View 2 Replies View Related

SQL & PL/SQL :: Index Became Unusable

May 16, 2011

why i encountered the error that the index became unusable.

View 2 Replies View Related

Server Administration :: Splitting Table Partition Without Making Primary Key Index Unusable?

Apr 8, 2013

splitting a table partition without making its primary key index ar any other indexes unusable.

I think it is possible to do so 10g onwards.

DB Details:
Oracle RDBMS 11.2.0.3, HP-Ux B.11.31, OLTP

View 2 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 :: Index Unused State

May 7, 2013

I made an Index Unused while doing some update by using sql developer right click 'Make unusealbe'. Now I need make it as useable.

marking as Usable. I have checked in all_indexes state is show as 'UNUSABLE'.

View 2 Replies View Related

Forms :: Generate Unique Document Numbers When More Than One User Inserting In Form At One Time

Dec 5, 2011

I have made one application form where users need to enter some data. This data is getting inserted in four tables. As in for now the data is properly getting saved and retrieved only for one user at a time. But problem arrives when more than one users are simultaneously making an entry and saving the data at one time.Same number is getting generated for the users who are saving the data at one time which should not happen.

View 4 Replies View Related

SQL & PL/SQL :: Stop Inserting Data Into A Table Before It Inserting Using Trigger?

Jul 26, 2011

shall we stop inserting data into a table before it inserting using Trigger?

View 3 Replies View Related

SQL & PL/SQL :: Unique Index Shorting

Jun 18, 2012

I have a running application where i have a table with pk defined on it and the pk clumn is inde in asc order by default. Can i alter the index in desc order as i always need to see the data in desc order.

View 7 Replies View Related

SQL & PL/SQL :: How To Add Column In Unique Index

Jan 17, 2011

I have created one unique index on one column of my table. Now i would like to add one more column in the same index without dropping the index.

SQL > CREATE TABLE DEBUG_TABLE
2 (
3 SLNO NUMBER,
4 MESSAGE VARCHAR2(4000 BYTE),
5 CREATED_DATE DATE DEFAULT SYSDATE,
6 CREATED_TIME TIMESTAMP(6) DEFAULT SYSDATE
7 );

Table created.

SQL > CREATE UNIQUE INDEX index_debug1 ON debug_table (SLNO);

Index created.

SQL > ALTER INDEX index_debug1 ADD COLUMN MESSAGE;
ALTER INDEX index_debug1 ADD COLUMN MESSAGE
*
ERROR at line 1:
ORA-02243: invalid ALTER INDEX or ALTER MATERIALIZED VIEW option

SQL >

View 6 Replies View Related

SQL & PL/SQL :: Update Each Row - Unique Index

Jun 23, 2011

I've got a table which has no unique identifier for a row. I have a field for an ID value but somhow there are duplicate values in this field.

Now I want to create an unique index over this field but therefore I have to remove the duplicate values.

So what I need is an update statement, maybe in a for each loop, which updates the ID field in each row starting with 1 and increasing the value.

In Informix there is a rowid value which is automatically set by Informix. But I think there´s no correspondent function/value in Oracle....

View 17 Replies View Related

Unique Function Based Index

Jun 27, 2011

I have a table which sees a lot of use for queries

CREATE TABLE CASE_STAGE
(
ID NUMBER(9) NOT NULL,
STAGE_ID NUMBER(9) NOT NULL,
CASE_PHASE_ID NUMBER(9) NOT NULL,
"CURRENT" NUMBER(1) NOT NULL,
--and other columns
)

ID is a primary key
CASE_PHASE_ID is a foreign key

"CURRENT" should only ever have values of 0 or 1. When it has a value of 1 it is unique for that CASE_PHASE_ID

What I have tried that doesn't work is

create unique index case_stage_F_IDX1 on case_stage("CURRENT", case_Phase_id) which gives me
ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found

What is the correct syntax, something like ("CURRENT"=1,case_phase_id) seems right but fails with an error about a missing bracket. Do I need to use a CASE statement here?

View 1 Replies View Related

How To Create Unique Index On A View

Aug 24, 2012

11.2.0.1...How do I create an index on a view or any workaround that my view won't get duplicates?

SQL> create unique index indx01 on db_backup_details_vw(id);
create unique index indx01 on db_backup_details_vw(id)
*
ERROR at line 1:
ORA-01702: a view is not appropriate here

View 7 Replies View Related

Can Create Unique Index On Subset Of Record

Apr 9, 2013

I have following records in table (T)

Idtype_cdNbrDt
1A1001/1/2013
2A101Null
3B100Null
4C100Null
5A100Null

Based on the type-cd, I need to create Unique index on Nbr & Dt columns

In above record set, I want to create Unique index on type_cd=A and not on B&C (Here i expect duplictes for a combination of Nbr & Dt)

Can we create Unique index on subset of record?

View 1 Replies View Related

SQL & PL/SQL :: Table With A Non-unique Index Consisting Of Three Columns

Sep 13, 2013

I have a table with a non-unique index consisting of three columns. The first column is not null while the remaining two are nullable. Queries using this index will chiefly be made in two ways.

1. Column one and two having values. Column three is null.
2. Column one and thre having values. Column two is null.

In both cases I expect range scan will be used since it's non-unique. In the first case the scan will be on values in column one and two. But what happens in case two. Will the range be on colum one, column two(being null) and cxolumn three? Or will it be on just column one since the second column is null? I have done some testing. I can see , using EXPLAN PLAN, that range scan is used in both cases. how the index is used?

Is there any other drawbacks with an index like this?

View 13 Replies View Related

SQL & PL/SQL :: Duplicated Entries In A Valid Unique Index?

May 26, 2010

I'm having problem with my database, which contains more than 1 rows with a same value on a field that has uniqueness contraint.

Here is the log from sqlplus. When I select on RI field, it shows 2 rows. But when I select on SCNUM field, it shows only 1 row. This SCNUM has an unique index on it.

And it is still in VALID state

SQL> set autotrace on
SQL> select ri, scnum from scratch1_p where ri in (536964983, 536955574);
select ri from scratch1_p where scnum='444393975';
RI SCNUM
---------- ----------
536955574 444393975

[code].....

View 14 Replies View Related

Performance Tuning :: Force Index Unique Scan?

Jun 14, 2012

Is there any hint to force "Index Unique Scan" over "Index Range Scan".

My query is generating different plans with the above two, and very slow when it uses "Index Range Scan".

View 19 Replies View Related

SQL & PL/SQL :: What Should Be Order Of Columns While Creating B-tree Non-unique Index

Jul 19, 2012

What should be the order of columns while creating b-tree non-unique index.

Low cardinality first
or
Hight cardinality first

View 15 Replies View Related

SQL & PL/SQL :: Index Effect While Inserting Records In A Table

Dec 17, 2011

I have one indexed column in a table.for performance improval purpose i created index to that column .

my doubt is..it may decrease the performance while inserting data into that column?

View 5 Replies View Related

SQL & PL/SQL :: Error With Initially Deferred Deferrable Constraint

Mar 6, 2013

I am unable to add the rows even after using initially deferred deferrable constraint.

My Queries are as follows:-

SQL> create table chicken (cid int primary key,eid int);

Table created.

SQL> create table egg (eid int primary key,cid int);

Table created.

SQL> alter table egg add constraint eggREFchicken foreign key (cid) references chicken(cid) initially deferred deferrable;

Table altered.

SQL> alter table chicken add constraint chickenREFegg foreign key (eid) references egg(eid) initially deferred deferrable;

Table altered.

SQL> insert into egg values(1,2);
insert into egg values(1,2)
*
ERROR at line 1:
ORA-02291: integrity constraint (SCOTT.EGGREFCHICKEN) violated - parent key not found

why it is not inserting even after doing initially deferred deferrable. I am using SQL Plus to run Queries.

View 11 Replies View Related

Data Guard :: Query Regarding Standby Database State When Primary Is Down / Unreachable

Feb 20, 2013

I want to know if there is any reflection on the standby database states when it's primary is down or not reachable? I want to use this information from standby node to determine if I can programmatically execute (dgmgrl) failover operation or not? This is considering that the broker configuration has no fast-start failover and no observer enabled/configured.

View 4 Replies View Related

Inserting Data In New Column Where Table Has Huge Data

May 26, 2013

I am trying to add a new column in a table and insert data from another column of same table.

alter table POSITION add INT_MK_DATA_ID number(10,0) null;
update POSITION set INT_MK_DATA_ID = INST_MARKET_DATA_ID;
commit

As there are huge number of records in the POSITION table ...its taking for ever to execute this query.

View 1 Replies View Related

Inserting Data Into Two Tables

Feb 15, 2009

I have two tables

table1
col1.....................................col2
primary key................ foreign key refer to col2 in tab2

table2

col1 ........................................... col2
foreign key refer to col1 in tab1 ............ primary key

now my question is how to insert data in to the two tables

View 2 Replies View Related

Inserting Blank Data

Jul 17, 2009

I have a table in oracle sql developer showing years, IDs and attendance etc I'm wanting to create a report based on this table but have encountered a problem with the years.

The years currently run from 2006 to 2009 and the problem is that some of the IDs are only present for say 2007 and 2008, thus leaving nothing for 2006 and 09. This in turn creates problems in the report.

What i would like to do is in the table, for each ID that is missing any years, is to insert a single row with just the ID and year so that in the report it will show a blank instead of nothing at all. So for the above example i would have numbers for 2007 and 2008 and for 2006 and 2009 there would just be a space.

View 6 Replies View Related

SQL & PL/SQL :: Inserting Data Using DBLINK

Feb 21, 2013

I am inserting XMLTYPE data using DBLINK I am getting the following error.

INSERT INTO APSP.SALES_HISTORY@APSP_LINK
SELECT * FROM KMBS.SALES_HISTORY

ORA-22804: remote operations not permitted on object tables or user-defined type columns

Source table structure

Name Null? Type
----------------------------------------- -------- ----------------------------
SC_NO NOT NULL NUMBER(25)
LT_DATE TIMESTAMP(6)
METHOD XMLTYPE

Target table structure(another DB)

Name Null? Type
----------------------------------------- -------- ----------------------------
SC_NO NOT NULL NUMBER(25)
LT_DATE TIMESTAMP(6)
METHOD XMLTYPE

how to insert XMLTYPE data using DBLINK.

View 16 Replies View Related

SQL & PL/SQL :: For Loop - Inserting Data?

Mar 7, 2012

create table stg1(x number, y number);
create table stg2(x number, y number);
create table stg(x number, y number);

I want to insert data from stg1, stg2 into stg

Instead of writing two insert statements, I want to write only one in a for loop to insert data into stg from stg1 and stg2

I tried this
begin
for i in 1..2 loop
insert into stg(x,y) select stgi.x, stgi.y from stgi;
end loop;
end;

it gives me table does not exist error:

so by stgi, i mean it should take stg1, stg2 etc

View 4 Replies View Related







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