Removing Duplicate Keys
Jul 3, 2008
Trying to delete duplicate rows from a table. The problem is, they aren't exactly duplicate rows. Let me explain.
I am migrating data from a Oracle 8.1.7 db to a 10.2.1 db. In the older db, this certain table does not have a PK/Unique Index, but in the new db there is a unique index. The fields that the index is unique on are:
SUBSCR_NO, SUBSCR_NO_RESETS, EXTERNAL_ID, EXTERNAL_ID_TYPE, ACTIVE_DATE.
In the old db, when I run this query I get 1229 rows. With a count of 2 each.
select SUBSCR_NO, SUBSCR_NO_RESETS, EXTERNAL_ID, EXTERNAL_ID_TYPE, ACTIVE_DATE, count(*)
from customer_id_equip_map
group by SUBSCR_NO, SUBSCR_NO_RESETS, EXTERNAL_ID, EXTERNAL_ID_TYPE, ACTIVE_DATE
having count(*)>1;
They are duplicates on those fields, but they are not totally duplicate rows because there is a field called is_current that has 0 in one row and has 1 in the other. What I need to do, is delete the 1229 rows with is_current=0.
View 4 Replies
ADVERTISEMENT
Apr 19, 2010
I have two oracle 9i databases A and B with complete equal schema. Only the data is different. I want to import all table data from A to B. The problem is that there are duplicate primary keys. Therefore I want to insert data with new primary keys (all referencing tables are concerned too).
View 13 Replies
View Related
Jul 18, 2013
I have requirement to suppress the duplicate nodes on same level in hierarchy query.
Below given is the script for it.
CREATE TABLE NODE_LVL (PARENT_NODE VARCHAR2(100), CHILD_NODE VARCHAR2(100));
INSERT INTO NODE_LVL VALUES('TBL_APL','TBL_AFL');
INSERT INTO NODE_LVL VALUES('TBL_APP','TBL_ACS');
INSERT INTO NODE_LVL VALUES('TBL_ADD','TBL_ADW');
INSERT INTO NODE_LVL VALUES('TBL_ADP','TBL_ADV');
INSERT INTO NODE_LVL VALUES('TBL_AOP','TBL_AOV');
[code]......
Table 'TBL_APP' is having 2 parent nodes i.e 'TBL_AOV' and 'TBL_ADV'
SELECT * FROM node_lvl WHERE child_node = 'TBL_APP';
At level 5 there is duplicate nodes i.e 'TBL_APP' and 'TBL_ACS' as parent_node and child_node respectively.
SELECT PARENT_NODE, CHILD_NODE, LEVEL FROM NODE_LVL START WITH PARENT_NODE = 'TBL_ACF' CONNECT BY PRIOR CHILD_NODE = PARENT_NODE;
I want to suppress such duplicates. So I added DISTINCT
SELECT DISTINCT PARENT_NODE, CHILD_NODE, LEVEL FROM NODE_LVL START WITH PARENT_NODE = 'TBL_ACF' CONNECT BY PRIOR CHILD_NODE = PARENT_NODE;
BUT requirement is to maintain the same order (of hierarchy) as it was before adding DISTINCT.
View 11 Replies
View Related
Mar 17, 2010
My requirement if id, join_date, join_time, result of table1 is matched with table2 at least one time then if repeating rows associated with the id should not come.Here is the test case.
create table table1
( id number , join_date varchar2(8), join_time varchar2(6), status varchar2(10));
create table table2
( id number , join_date varchar2(8), join_time varchar2(6), status varchar2(10));
insert into table1 values (01, '20010101', '0500', 'PASS');
insert into table1 values (01, '20010102', '0501', 'FAIL');
insert into table1 values (02, '20010103', '0502', 'PASS');
insert into table1 values (03, '20010104', '0503', 'FAIL');
insert into table1 values (04, '20010105', '0504', 'PASS');
insert into table1 values (05, '20010106', '0505', 'FAIL');
[code]...
I have tried the below mentioned query, whether any better query is there than this because in real-time data have 2 millions of record in table 1 and 60 thousand in table2.
select distinct a.id, a.join_date, a.join_time, a.status
from table1 a, table2 b
where a.id = b.id
and (a.id, a.join_date, a.join_time, a.status) not in (select b.id, b.join_date, b.join_time, b.status
from table2 b)
and a.id = (
select distinct a.id
[code]....
View 20 Replies
View Related
Oct 7, 2010
In my sql developer I have created a table. Created primary keys. Using alter table I have also created the foreign key. Now I need to add one more foreign key to the same table. Do I have to drop the existing foreign key and then create the 2 new foreign keys using the alter table condition or can I retain the existing FK and just use alter table to add another FK.
View 9 Replies
View Related
Feb 24, 2007
the relationship on my script.What I want to do is make it possible to assign many courses to a student.But I have read a few books on foreign keys and primary keys, and I still dont get how to go on doing this....
This is how the table creation with the keys looks right now...
CREATE TABLE CAMPUS (
CAMPUSID NUMBER(4) NOT NULL,
NAME VARCHAR2(80) NOT NULL,
CONSTRAINT CAMPUS_PK PRIMARY KEY (CAMPUSID));
[code]...
View 3 Replies
View Related
Sep 10, 2010
I have few BDs replicated using Advanced Replication, some tables are read only (Basic Replication) and another ones are Updatable (Advanced Replication).
The infraestucture is Materialized View Replication, my trouble is, I do not know how to should treat the updatable tables that have foreign keys, I read in another FAQs that you should replicate the indexes using the same way to replicate tables (dmbs_repcat.create_master_repobject)
Which is the correct manner to treat the foreign keys with this kind of updatable snapshots,
View 2 Replies
View Related
Feb 19, 2013
I need to copy the changed and deleted data in an other table. I have searched this site ,asktom and other sites also. I found the following solution from asktom website. But it gives me the changed columns data only and i need the primary key with changed data and deleted rows also.
DROP TABLE emp;
CREATE TABLE emp AS (SELECT * FROM scott.emp);
CREATE TABLE audit_table
[Code].....
View 10 Replies
View Related
Apr 3, 2006
how to disable function keys in forms6i(i.e., i want to deactivate the default function keys when running a form)
View 5 Replies
View Related
Dec 27, 2012
why function keys are not working in forms9i?
Like whenever I press F7 for query or F8 to fetch records or F10 to save records they are not functioning.
View 2 Replies
View Related
Jul 22, 2010
I have to create indexes on foreign key columns ,now if composite index is already there with foreign key column then that will work or i will have to create a single column index.
View 17 Replies
View Related
Mar 6, 2012
i need to delete a record from table but it showed a error for foreign key constraint so i disabled the constraint and again deleted, now the row is deleted.
Again inserted another values instead the deleted value. after that i tried to alter the constraint but i received the error ORA-02298 cannot validate - Parent keys not found
alter table t1 enable constraint FK_T1;
But i am not able to enable the key. what to do?
View 6 Replies
View Related
Jun 9, 2011
I used
SELECT USER_CONS_COLUMNS.CONSTRAINT_NAME,
COLUMN_NAME,
CONSTRAINT_TYPE,
[Code]...
to create a parent key table sort of thing, but for some reason there are rows that display multiple times
CONSTRAINT_NAME COLUMN_NAME CONSTRAINT_TYPE R_CONSTRAINT_NAME DELETE_RU
-------------------- -------------------- --------------- ------------------------------ ---------
SYS_C00135225 PATIENT_STATUS C
SYS_C00135226 BALANCE C
SYS_C00135227 PATIENT_STATUS C
PK_PATIENT PATIENT_NUMBER C
[Code] ..........
is it something to do with the join command
View 4 Replies
View Related
Feb 3, 2009
Inside procedure,I need to validate all the columns (Ex:col1 should not accept more than 40 chars) and update status(containing error or not) of these columns into another column in the same table.For this,I mentioned only 'UPDATE' statements.So 'WHERE' clause of some update statements not using Primary keys.
In that table composite primary key was created.This procedure is successfully complied & executed now.But this procedure took more than 10 mins to execute.I need to reduce the time to less than a min.
View 8 Replies
View Related
Feb 27, 2013
Every time I hit up, down, left, and right arrow keys to return to a previous sql command I got ^[[D, fix this issue? The backspace works fine with me.
View 2 Replies
View Related
May 19, 2007
How can I create a list of items in a field, for instance lets say I have a table called car and one of the sub categories is parts. How can I make it so that parts can be any number of pre-defined entities? Or even table, for instance if I make a table called parts how can I use that in the car table in place of parts?
My second question is about using foreign keys as primary keys. If I am writing an email sql DB and I decided to use the members usrname as the primary key in the member table but then made another table that lists all the emails and decided to make the foreign key member username the primary key there.
Is that safe to do or should i create a sequence in which to identify a primary key for the email list table? Also what if I extend member to several other tables and use it as a primary key there too, seems like a kind of dangerous way to do things...
View 1 Replies
View Related
Mar 31, 2011
I was reading the documentation for oracle 11gr2, with reference to URL>.....
The following examples show how to correctly choose the cluster key and set the HASH IS, SIZE, and HASHKEYS parameters. For all examples, assume that the data block size is 2K and that on average, 1950 bytes of each block is available data space (block size minus overhead).Note that 34 hash keys are assigned for each data block
how they arrive at 34 hash keys because another portion of the document states
This space determines the maximum number of cluster or hash values stored in a data block. If SIZE is not a divisor of the data block size, then Oracle Database uses the next largest divisor.
if that is the case, then number of hash keys should be 1900/55 = 34.55 which should have rounded up to 35.
View 1 Replies
View Related
Aug 9, 2011
table was defined as below & indexes are also created on name /dept columns, data is also available :
create table test (
name varchar2(10),
version NUMBER(12),
dept varchar2(10)
[code].....
Now the requirement is that the parition keys has to be changed to 'dept' from the existing 'version' . How to accomplish this without any implication on the indexes and other constraints.
View 1 Replies
View Related
Dec 27, 2011
I have taken database backup using exp command and when I try to import in other pc the foreign keys are not imported. It saying error message that no matching unique key or primary key for this column.
how will i take backup including with primary keys?
View 7 Replies
View Related
May 17, 2011
is there any query to find number of primary keys present in a table.
View 13 Replies
View Related
May 12, 2011
I received the below error while importing the dump file.
IMP-00017: following statement failed with ORACLE error 2298:
"ALTER TABLE "tab1" ENABLE CONSTRAINT "TSK_FK""
IMP-00003: ORACLE error 2298 encountered
ORA-02298: cannot validate (bala.TSK_FK) - parent keys not found
View 2 Replies
View Related
Mar 28, 2007
I am using Oracle 10g Express Edition on Fedora core 5 32+ bit os. The problem is when I use the SQL Plus command line to make SQL statements I can not get the previously typed command back at the prompt when I use the up and down arrow keys on my keyboard. This is quite easy when I am using a shell, but here with this Oracle command line interface it is not working at all. Here is the example as what actually is happening whe I press the up or down arrow keys.
SQL> drop table mailorders;
Table dropped.
SQL> ^[[A^[[A^[[A^[[A^[[A^[[A^[[A^[[A^[[A^[[A^[[A^[[A
View 2 Replies
View Related
Sep 19, 2013
db and dev 10g rel2 ,suppose that i have a table with a lot of duplicate rows ,what i need is to delete the duplicates and retain one row of these duplicates . likecolumn -- with those values...how to delete two (hi's) and retain the third , ?it is all applied to all the duplicate values in the column.
View 5 Replies
View Related
Aug 5, 2012
To remove fragmentation which is the best method.
First one :
-----------------
1)Created a backup table from the Fragmented table (This table is a partitioned one).
2)Analyzed this table.
3)DROP the Fragmented table
4)Inserted the backuped up data from backup table to the Re-created table.
5)Analyze this table.
Second method
-------------------
1) Create a backup table newly, with PCTFREE =0
2) Inserted the data from Fragmented table ( This is a partitioned table) to backup table
3) Analyzed this table.
4) Truncate Fragmented table
4) Did Exchange partition of Fragmented table with Backup table.
The second method is not found to be removing the Fragmentation. Before the fragmentation was 28% after Second method the fragmentation is still the same. While the first method the fragmentation reduced to 16%.
Query used to find Fragmentation.
select table_name,
round((blocks*8),2) "table size kb",
round((num_rows*avg_row_len/1024),2) "actual data in table kb",
round((blocks*8),2)- round((num_rows*avg_row_len/1024),2) "wasted space kb",
[code]...
View 9 Replies
View Related
Sep 10, 2010
TYPE CashRecord IS RECORD(client_id VARCHAR2(100),
account_letter VARCHAR2(100),
cash_amount VARCHAR2(100),
cash_amount_ccy VARCHAR2(100) );
TYPE CashRecordTable IS TABLE OF CashRecord INDEX BY VARCHAR2(100); -- Indexed by client_id~account_letter~ccy
So if I did something like this;
l_cash_records CashRecordTable;
-- say for example that l_cash_rec/l_cash_rec2 has been defined..
l_cash_records('some index') := l_cash_rec;
l_cash_records('some index 2') := l_cash_rec2;
l_cash_records.COUNT would give me 2
How can I somehow remove 'some index 2' so that l_cash_records.COUNT is 1 ?
View 3 Replies
View Related
Jan 26, 2012
This is a simple question, but I cannot seem to find a solution. Here's the basic query:
select distinct accountno, parcelno, streetno||' '|| predirection ||' '|| streetname||' '|| streettype||' '|| postdirection||' '|| unitname||', '|| propertycity
from tblacctpropertyaddress ....
What I want to do is add is this logic: If Predirection is null, then no space between streetno & streetname. Same for postdirection and unitname. (for example, if both postdirection and unitname are null, there are no spaces between streettype and the comma before propertycity)
Also, when unitname is not null, I want to add the string "Unit " prior to the returned value in unitname.
View 5 Replies
View Related
Dec 10, 2012
One of my clients need to remove three(of four) CPU to comply the licensing agreement with Oracle.
To avoid problems and also to list the possible problems that removing the CPU can bring, I wish to make a survey of the possible impacts, especially in performance, that removal can cause.
How can I get this information?
View 8 Replies
View Related
Dec 27, 2006
I am working with Oracle 10G, and have been working on setting up little pl/sql checks to make sure that the data that is imported is in the correct format.
The wall I have hit is removing illegal characters from the data I import. I have started to set something up where the string for a certain column must be be made of only there characters:
"ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz-" (note that there is a - besides just letters) and I may want to add some other characters later. So basically the script will drop or replace any character not found in my definitions with "", thus removing the illegal character and joining the previous and next characters.
I thought for sure there would be a script posted somewhere online that did this but I can't find it and my syntax skills are lacking.
View 8 Replies
View Related
May 3, 2012
I've a table in data warehouse production with data which is partitioned by RANGE, sub-partitioned by HASH.Later we realized that subpartitioned are not needed as volume is less.
I tried below approah in DEV DB:
1. create table ABC_BAK as select * from ABC;
2. dbms_metadata.get_ddl('TABLE','ABC')
3. Removed the subpartitions details from the output of step 2 and prepared the script of create table without subpartitions
4. Drop table ABC
5. Create table ABC from script step 3.
6. Insert into ABC select * from ABC_BAK
7. Drop table ABC_BAK
View 1 Replies
View Related
Dec 13, 2008
I am persisting xml with below mention tag which contains preformatted text data.
<SECTION>
<HDG><![CDATABusiness Summary]></HDG>
<BODY>
<![CDATA[(C) 2008 D&B
COPYRIGHT 2008 DUN & BRADSTREET INC. - PROVIDED UNDER CONTRACT
FOR THE EXCLUSIVE USE OF SUBSCRIBER 263763803.
ATTN: null
[code]....
Now, when i retrieve this xml from oracle CLOB column, i am getting data in body tag as below:
(C) 2008 D&B COPYRIGHT 2008 DUN & BRADSTREET INC. - PROVIDED UNDER CONTRACT FOR THE EXCLUSIVE USE OF SUBSCRIBER 263763803. ATTN: null US TEST COMPANY 984 DUNS: 36-252-8379 RATING DS US FICTITIOUS COMPANY 984 BUSINESS SERVICES EMPLOYS UNDETERMINED 899 EATON AVE SIC NO. BETHLEHEM PA 18025 7389 TEL: 610 882-0005 RICHARD DOE, MANAGER RECORD TYPE: DUNS SUPPORT THE "DS" INDICATOR ASSIGNED TO THIS BUSINESS MEANS THAT THE LIMITED INFORMATION CURRENTLY IN THE D&B FILE DOES NOT ALLOW US TO CLASSIFY IT WITHIN OUR RATING SYSTEM. WE ARE PROVIDING THIS INFORMATION TO YOU IN THE INTEREST OF SPEED WITHOUT HAVING COMPLETED AN INVESTIGATION. THEREFORE, THIS REPORT MAY NOT REFLECT THE CURRENT STATUS OF THIS BUSINESS. D&B CAN INVESTIGATE THIS BUSINESS AND UPDATE THIS INFORMATION BASED ON THE RESULTS OF THAT INVESTIGATION.
All text formatting goes for a toss (newline, trailing tab characters are getting trimmed).provide any inputs on how to avoid this. Text formatting needs to be preserved.
View 1 Replies
View Related
