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


ADVERTISEMENT

SQL & PL/SQL :: Column DA_OBJECT_NAME Is Not Unique / Same Object Name Can Have Multiple Entries

Jul 22, 2011

I have a table DN_ACTIONS with 9.5 million records. Column DA_OBJECT_NAME is not unique and same object name can have multiple entries. Column DA_ACTION_STATUS can have any values between 1 to 5.

I want to find all the DA_OBJECT_NAME in DN_ACTIONS where all of its entries has DA_ACTION_STATUS=3.

CREATE TABLE DN_ACTIONS
(
DA_ID NUMBER,
DA_OBJECT_NAME VARCHAR2(192 BYTE),
DA_ACTION_STATUS NUMBER(1)
);

best way as it does a self join on big table of 9.5 million.

(select distinct a.DA_OBJECT_NAME from DN_ACTIONS a where not exists ( select distinct DA_OBJECT_NAME from DN_ACTIONS b where a.DA_ OBJECT_NAME = b.DA_OBJECT_NAME and b.DA_ACTION_STATUS != 3))

What are the better ways to rewrite this query?

View 2 Replies View Related

Forms :: Frm-15500 Valid And Unique Object Name Must Be Entered

Feb 28, 2012

I'm trying to add a column in an existing form. I added a text item and defined it as a database item. When I tried to rename the item the message (frm-15500 valid and unique object name must be entered)appears, and therefore not able to exit the property Platte.

Is there a way I can exit the Platte and should I've first add the column to the data block and then rename in my form ?

View 1 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 :: Script To Query Index Statistics Data And Update Tracking Entries?

Oct 19, 2010

I have created a table: INDEX_SIZE_TRACKING with the following attributes

Index Name: name of the index. Type: VARCHAR(255). This is the primary key of the table.

Allocated Space: the memory space (in bytes) allocated to the index. Type: NUMBER

Used Space: the memory space used by the index. Type: NUMBER

Last Update: the time when index details are updated to this table. Type: VARCHAR(255)

I want to write a PL/SQL script to query index statistics data and update tracking entries in the INDEX_SIZE_TRACKING table. If there is no existing entry for the index, create a new one; otherwise, update the existing one. have a PL/SQL statement that can do this in oracle XE?

View 1 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

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

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 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 :: Getting Rows Which Are Not Duplicated

Aug 9, 2010

I'm having table like this:

deptno
10
10
20
20
30
40

my query isI need the records which are not duplicated.

my output must have the records 30 and 40 only.

View 11 Replies View Related

SQL & PL/SQL :: Delete Duplicated Record

May 21, 2010

I want to delete the duplicated record using following methods, it delete the records if all the record are matched according to condition, but if all records are not matched then it display error messages.

SQL> SELECT * FROM XYZ;

NAME FNAME ADDRESS JOB
-------------- -------------------- -------------------- -----------------------------------------
Bilal Khan Wali your rehman name district abbottabad student
Bilal Khan Wali your rehman name district abbottaba student
Bilal Kh Wali your rehma name district abbotta studen
Bilal K Wali your rehm name district abbotta studen
Bilal Khan Wali your rehman name istrict abbottaba tudent
lal Khan i your rehman name strict abbottaba tudent
[code]....

View 6 Replies View Related

PL/SQL :: How To Add Asterisks To Row When One Item In Row Is Duplicated / Show One Row

Sep 7, 2013

I totally realize that but here is what im trying to accomplish. I have a table with one column that repeats.the table looks like this.

create table testCheck (check_num varchar2(20), amount number(17,2), invh_Code varchar2(20))
 and it has data like so
INSERT INTO "TESTCHECK" (CHECK_NUM, AMOUNT, INVH_CODE) VALUES ('001', '50', '1123')
INSERT INTO "TESTCHECK" (CHECK_NUM, AMOUNT, INVH_CODE) VALUES ('001', '50', '1123')
INSERT INTO "TESTCHECK" (CHECK_NUM, AMOUNT, INVH_CODE) VALUES ('002', '100', '1234'

 I would like to write a query that would give me the distinct check_num's  but put an asterisk on the items that are actually duplicated.so in other words expected result in this case would look like this. 

"CHECK_NUM" "AMOUNT" "INVH_CODE"
"001*" 50 "1123"
"002" 100 "1234"  

I've been able to get as far as getting the unique values  but what i cant do is figure out for the life of me how to put the asterisk in there.here is my query. 

with Checkquery as (        
SELECT  count(*) over (partition by CHECK_NUM order by CHECK_NUM ROWS UNBOUNDED PRECEDING ) thiscount, CHECK_NUM,  AMOUNT,  INVH_CODE
FROM  TESTCHECK
group by
check_num,
AMOUNT,
INVH_CODE)
select check_num, amount,invh_Code from Checkquery where thiscount ='1';

I simply want to identify the dups put an asterisk on the record and then only select one of the records.

View 14 Replies View Related

PL/SQL :: Foreign Key Can Be Implemented In One Table And Cannot Be Duplicated To Work

Oct 17, 2013

I have now  created my tables and now I want to populate them with my data but I get errors below is my full sql command. From last time I learned that one foreign key can be implemented in one table and cannot be duplicated to work around this I changed my foreign key as you can see to "fkone" . but why am I getting errors when trying to add information I mean I have not gotten errors in the first two tables so what am I "doing wrong/ not understanding" 

CREATE TABLE CUSTOMER (CUSTOMERNAME varchar (25),STREET varchar (25),CUSTOMERCITY varchar (25),CONSTRAINT CUSTOMER_pk PRIMARY KEY (CUSTOMERNAME)); CREATE TABLE DEPOSIT (CUSTOMERNAME varchar (25),BRANCHNAME varchar (25),ACCOUNTNUMBER int,BALANCE decimal (7,2),CONSTRAINT DEPOSIT_pk PRIMARY KEY (ACCOUNTNUMBER)); CREATE TABLE LOAN(CUSTOMERNAME varchar (25),BRANCHNAME varchar (25),LOANNUMBER int,AMOUNT decimal (7,2),CONSTRAINT LOAN_pk PRIMARY KEY (LOANNUMBER)); CREATE TABLE BRANCH (BRANCHNAME varchar (25),BRANCHCITY varchar (25),ASSETS decimal (7,2) ,CONSTRAINT BRANCH_pk PRIMARY KEY (BRANCHNAME)); ALTER TABLE DEPOSITadd
[code]....

View 4 Replies View Related

Application Express :: Report Columns Duplicated When Region Is Copied

May 15, 2013

Re: Report Columns duplicated when region is copied.

When I copy a region which I have created via master-detail form, the report columns are duplicated. I'm actually copying the whole page but I have had other cases where I just copying the desired region and the columns were duplicated there as well. And, of course, there is no way that I know to delete columns from the form. But I doubt because -- in at least some cases I've tried -- the columns seems to be "linked" in that a change to one copy of the column was duplicated in the other when changes were applied.

In the example (below), there are three regions on the page and the problem occurs in region 2. (I think I will likely delete the first region.) It's been agonizing trying to get the report of region 2 and the tabular formish edits of region 3 on the same page. I made this in Oracle 11.2 in our APEX 4.1 and imported it to 4.2. You can see it here:

WS apex_examples_01
demo
demo
Appl. 991204

Page 57 is the original page created in 4.1 and imported
Page 58 is the page copied from 57 under 4.1 and imported
Page 59 is the page copied from 57 under 4.2 (Same result as in 4.1.)

If you want to try the page, select DSD01 from the DSD LOV, click "Query Process" and then click the edit icon. Ignore the region 1 errors -- I'll probably delete this region. The data is region 3 is for Run Date 12-May-13.

Is there some setting that allows this region to be copied without duplicating columns?

P.S. I think I know why it happens: APEX hates me!

View 3 Replies View Related

Server Utilities :: Why Duplicated Rows When A PK Is Present Running Sqlldr

Dec 10, 2010

Why duplicated rows when a PK is present running Sqlldr?

View 1 Replies View Related

SQL & PL/SQL :: Get Coding Right To Truncate Old Entries?

Jun 15, 2010

currently developing a app for MSM in tromsø, that are going to delete the oldest entrys in the database table and then update all the relative Run history entrys in another table.

And i can't get the coding right to truncate the old entrys.Here are the

DECLARE V_SLETT varchar2(125);
V_NYE varchar2(50);
V_SLETT := :SLETT;
V_NYE := :NYESTE;
truncate DELTAKER ( IDNR in (V_SLETT) );
begin
request := UPDATE_RUN;
end;

View 15 Replies View Related

How To Delete Duplicate Entries In A Table

Sep 8, 2008

In oracle 9i ........How to delete duplicate entries in a table ?

if suppose in a table named office, one or more entry(row) is repeated more then twice or minimum twice.
I need a query to delete the multiple entries ....

Note:
--->No constraints applied on the table.
--->No Primary Key
--->You cannot create any object....that is no view or a duplicate table can be created

View 3 Replies View Related

SQL & PL/SQL :: Check Existing Entries In Table

Apr 3, 2012

I am abolute new in PL SQL and I am working on following topic:

I have to check if an entry exists in a table.

- if it exists ...... do something
- if not ...... simply go on

I tried the following:

select Count(*)from products p
where p.productNbr = temp_Nbr
Group By t.produkt;

I fetched the result into a variable entries

if entries > 0 .....

MY problem is the following:

If there are entries (entries>0) --> it works
If there are no entries, entries is not 0 but 'no data found' --> crash

I cannot work with Exceptions because this all happens in a Loop which must go on in both cases!

View 14 Replies View Related

Query Table Entries With A (timestamps) Column?

Jan 11, 2007

I'm trying to generate count of the number of entries in a table for each day.The problem is the date column is of datatype timestamp and looks like this "2006-12-30 18:42:03.0"

How would I generate a report of number of entries in the table for each date (I'm not intrested in the "time" only the "date" i.e YYYY-MM-DD)?

SELECT COUNT(*) FROM my_table_name
WHERE my_date_column LIKE '2006-12-30%'
GO

It returns zero rows ( and I kno there are rows in the table) I'm using Oracle 10g.

View 2 Replies View Related

SQL & PL/SQL :: Best Method To Populate Calendar Entries On A Table

Jun 2, 2011

I have a table :

Product
A
B
C
D

and I was wondering if there is a quick method of populating it with calendar data so it would look like the following:

Product Year Month
A 2008 Jan
A 2008 Feb
A 2008 Mar
A 2008 Apr
A 2008 May
A 2008 Jun
A 2008 Jul
A 2008 Aug
A 2008 Sep
A 2008 Oct
A 2008 Nov
A 2008 Dec
A 2009 Jan
A 2009 Feb
Etc.

This would be done for all products for 4 years.

View 2 Replies View Related

Update Oracle Tnsnames.ora File With Some New Entries?

Oct 5, 2011

I need to Update Oracle tnsnames.ora file with the some new entries ....i was looking for ora 92, but could not find a proper link to download.now I have oracle 11 g, but i cant find tnsnames.ora file in that...is it because my installation was not proper...

View 1 Replies View Related

SQL & PL/SQL :: No Entries In Materialized View Log For Insert Append?

Aug 9, 2010

I've created a materialized view log on table with the following statement:

CREATE MATERIALIZED VIEW LOG ON table_a
WITH ROWID, SEQUENCE (column_a, column_b, column_c)
INCLUDING NEW VALUES;

The insert is done with the following statement:

INSERT
/*+ APPEND PARALLEL("table_a") */
INTO
"table_a"
("column_a",
"column_b",
"column_c",
"column_d_sum")
(select
column_a",
"column_b",
"column_c",
"column_d_sum"
from table_B)

But the Log is empty when the insert is finished. When I insert rows without the APPEND hint, rows are created in the log table. So, doesn't the log record bulk loads?

View 6 Replies View Related

Application Express :: Tabular Form Works Fine For First Entries

Sep 28, 2012

Tabular form works fine for first entries, but upon going back to add more records Page 55 has a button that links to the 'Approval' page which is a tabular form (Page 56).A text field at top of this tabular form receives the pass of a varchar2 field from page X which is an identifier (not PK). Tabular form query has condition WHERE ISO_NUMBER = :P56_ISO_NUMBER

When I add records on the first pass to the tabular form and click submit all is fine and dandy.When I get out of the tabular form and go back to it and add additional records I get this error:

Error in mru internal routine: ORA-20001: Error in MRU: row= 1, ORA-20001: ORA-20001: Current version of data in database has changed since user initiated update process. current checksum = "C4B43B9A17A41D287D55DEFE9B035944", item checksum = "607C07E22C9B03E6A45AF44EDC06BB31"., update "SPICE_HUNTER1"."DOC_APPRV_DOC" set "APPRV_DOC_ID" = :b1, "DOC_INFO_ID" = :b2, "ISO_NUMBER" = :b3, "DOC_APPROVER" = :b4, "DOC_APPROVED" = :b5, "DOC_APPROVAL_DT" = :b6, "DOC_COMMENT" = :b7, "APP_USER" = :b8Here's what I've done to try to figure it out:

1. Ran debug and saw it was happening in my MRU; and
2. In the past what I have cleaned out tabular form table data because I've found that when I'm in this testing mode going back and forth with changes gets fouled up, but with a clean slate it still happens.
3. I had Hidden the ID fields, and then remembered this was one of the issues with versions of this error. (I actually logged this one to remember!). So I made them Display as Text (Saves State) and unchecked their boxes.

View 1 Replies View Related







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