 | |
cannot validate FK - parents not found
comp.databases.oracle.misc comp.databases.oracle.misc
 |
| | Thread Tools | Display Modes |
#11
 
| |||
| |||
|
Re: cannot validate FK - parents not found -
09-23-2008
, 12:40 AM
|
On Sep 19, 2:56 am, "timo" <timo.ta... (AT) luukku (DOT) com> wrote: Hi, what's the most efficient way to search rows in a (big) table preventing FK to be created; we've a table where PK consists of several columns and those columns are referenced by child tables. And as the table has already rows with problems those should be identified easily and corrected - so now we get ORA-02298: cannot validate (AAA.BBBBB_FK) - parent keys not found BR, Timo Let Oracle find them for you by creating the EXCEPTIONS table and using it as shown below: SQL> @?/rdbms/admin/utlexcpt SQL> rem SQL> rem $Header: utlexcpt.sql,v 1.1 1992/10/20 11:57:02 GLUMPKIN Stab $ SQL> rem SQL> Rem Copyright (c) 1991 by Oracle Corporation SQL> Rem NAME SQL> Rem except.sql - <one-line expansion of the name SQL> Rem DESCRIPTION SQL> Rem <short description of component this file declares/ defines SQL> Rem RETURNS SQL> Rem SQL> Rem NOTES SQL> Rem <other useful comments, qualifications, etc. SQL> Rem MODIFIED (MM/DD/YY) SQL> Rem glumpkin 10/20/92 - Renamed from EXCEPT.SQL SQL> Rem epeeler 07/22/91 - add comma SQL> Rem epeeler 04/30/91 - Creation SQL SQL> create table exceptions(row_id rowid, 2 owner varchar2(30), 3 table_name varchar2(30), 4 constraint varchar2(30)); Table created. SQL SQL> create table parents( 2 my_id number, 3 mydata varchar2(40) 4 ); Table created. SQL SQL> alter table parents 2 add constraint parents_pk 3 primary key(my_id); Table altered. SQL SQL> create table children( 2 child_id number, 3 child_loc varchar2(40), 4 parent_id number 5 ); Table created. SQL SQL> alter table children 2 add constraint children_pk 3 primary key(child_id); Table altered. SQL SQL> begin 2 for i in 1..100000 loop 3 insert into parents 4 values(i, 'Location #'||i); 5 end loop; 6 7 for i in 1..100100 loop 8 insert into children 9 values(i-1, 'Sub-location #'||i, i); 10 end loop; 11 12 commit; 13 14 end; 15 / PL/SQL procedure successfully completed. SQL SQL> alter table children 2 add constraint parent_child_fk 3 foreign key (parent_id) references parents(my_id) 4 exceptions into exceptions; add constraint parent_child_fk * ERROR at line 2: ORA-02298: cannot validate (BING.PARENT_CHILD_FK) - parent keys not found SQL SQL> select count(*) from exceptions; COUNT(*) ---------- 100 SQL SQL> select owner, table_name, rowid 2 from exceptions; OWNER TABLE_NAME ROWID ------------------------------ ------------------------------ ------------------ BING CHILDREN AAAYpMAAEAAAAwsAAA BING CHILDREN AAAYpMAAEAAAAwsAAB BING CHILDREN AAAYpMAAEAAAAwsAAC BING CHILDREN AAAYpMAAEAAAAwsAAD BING CHILDREN AAAYpMAAEAAAAwsAAE BING CHILDREN AAAYpMAAEAAAAwsAAF BING CHILDREN AAAYpMAAEAAAAwsAAG BING CHILDREN AAAYpMAAEAAAAwsAAH BING CHILDREN AAAYpMAAEAAAAwsAAI BING CHILDREN AAAYpMAAEAAAAwsAAJ BING CHILDREN AAAYpMAAEAAAAwsAAK OWNER TABLE_NAME ROWID ------------------------------ ------------------------------ ------------------ BING CHILDREN AAAYpMAAEAAAAwsAAL BING CHILDREN AAAYpMAAEAAAAwsAAM BING CHILDREN AAAYpMAAEAAAAwsAAN BING CHILDREN AAAYpMAAEAAAAwsAAO BING CHILDREN AAAYpMAAEAAAAwsAAP BING CHILDREN AAAYpMAAEAAAAwsAAQ BING CHILDREN AAAYpMAAEAAAAwsAAR BING CHILDREN AAAYpMAAEAAAAwsAAS BING CHILDREN AAAYpMAAEAAAAwsAAT BING CHILDREN AAAYpMAAEAAAAwsAAU BING CHILDREN AAAYpMAAEAAAAwsAAV OWNER TABLE_NAME ROWID ------------------------------ ------------------------------ ------------------ BING CHILDREN AAAYpMAAEAAAAwsAAW BING CHILDREN AAAYpMAAEAAAAwsAAX BING CHILDREN AAAYpMAAEAAAAwsAAY BING CHILDREN AAAYpMAAEAAAAwsAAZ BING CHILDREN AAAYpMAAEAAAAwsAAa BING CHILDREN AAAYpMAAEAAAAwsAAb BING CHILDREN AAAYpMAAEAAAAwsAAc BING CHILDREN AAAYpMAAEAAAAwsAAd BING CHILDREN AAAYpMAAEAAAAwsAAe BING CHILDREN AAAYpMAAEAAAAwsAAf BING CHILDREN AAAYpMAAEAAAAwsAAg OWNER TABLE_NAME ROWID ------------------------------ ------------------------------ ------------------ BING CHILDREN AAAYpMAAEAAAAwsAAh BING CHILDREN AAAYpMAAEAAAAwsAAi BING CHILDREN AAAYpMAAEAAAAwsAAj BING CHILDREN AAAYpMAAEAAAAwsAAk BING CHILDREN AAAYpMAAEAAAAwsAAl BING CHILDREN AAAYpMAAEAAAAwsAAm BING CHILDREN AAAYpMAAEAAAAwsAAn BING CHILDREN AAAYpMAAEAAAAwsAAo BING CHILDREN AAAYpMAAEAAAAwsAAp BING CHILDREN AAAYpMAAEAAAAwsAAq BING CHILDREN AAAYpMAAEAAAAwsAAr OWNER TABLE_NAME ROWID ------------------------------ ------------------------------ ------------------ BING CHILDREN AAAYpMAAEAAAAwsAAs BING CHILDREN AAAYpMAAEAAAAwsAAt BING CHILDREN AAAYpMAAEAAAAwsAAu BING CHILDREN AAAYpMAAEAAAAwsAAv BING CHILDREN AAAYpMAAEAAAAwsAAw BING CHILDREN AAAYpMAAEAAAAwsAAx BING CHILDREN AAAYpMAAEAAAAwsAAy BING CHILDREN AAAYpMAAEAAAAwsAAz BING CHILDREN AAAYpMAAEAAAAwsAA0 BING CHILDREN AAAYpMAAEAAAAwsAA1 BING CHILDREN AAAYpMAAEAAAAwsAA2 OWNER TABLE_NAME ROWID ------------------------------ ------------------------------ ------------------ BING CHILDREN AAAYpMAAEAAAAwsAA3 BING CHILDREN AAAYpMAAEAAAAwsAA4 BING CHILDREN AAAYpMAAEAAAAwsAA5 BING CHILDREN AAAYpMAAEAAAAwsAA6 BING CHILDREN AAAYpMAAEAAAAwsAA7 BING CHILDREN AAAYpMAAEAAAAwsAA8 BING CHILDREN AAAYpMAAEAAAAwsAA9 BING CHILDREN AAAYpMAAEAAAAwsAA+ BING CHILDREN AAAYpMAAEAAAAwsAA/ BING CHILDREN AAAYpMAAEAAAAwsABA BING CHILDREN AAAYpMAAEAAAAwsABB OWNER TABLE_NAME ROWID ------------------------------ ------------------------------ ------------------ BING CHILDREN AAAYpMAAEAAAAwsABC BING CHILDREN AAAYpMAAEAAAAwsABD BING CHILDREN AAAYpMAAEAAAAwsABE BING CHILDREN AAAYpMAAEAAAAwsABF BING CHILDREN AAAYpMAAEAAAAwsABG BING CHILDREN AAAYpMAAEAAAAwsABH BING CHILDREN AAAYpMAAEAAAAwsABI BING CHILDREN AAAYpMAAEAAAAwsABJ BING CHILDREN AAAYpMAAEAAAAwsABK BING CHILDREN AAAYpMAAEAAAAwsABL BING CHILDREN AAAYpMAAEAAAAwsABM OWNER TABLE_NAME ROWID ------------------------------ ------------------------------ ------------------ BING CHILDREN AAAYpMAAEAAAAwsABN BING CHILDREN AAAYpMAAEAAAAwsABO BING CHILDREN AAAYpMAAEAAAAwsABP BING CHILDREN AAAYpMAAEAAAAwsABQ BING CHILDREN AAAYpMAAEAAAAwsABR BING CHILDREN AAAYpMAAEAAAAwsABS BING CHILDREN AAAYpMAAEAAAAwsABT BING CHILDREN AAAYpMAAEAAAAwsABU BING CHILDREN AAAYpMAAEAAAAwsABV BING CHILDREN AAAYpMAAEAAAAwsABW BING CHILDREN AAAYpMAAEAAAAwsABX OWNER TABLE_NAME ROWID ------------------------------ ------------------------------ ------------------ BING CHILDREN AAAYpMAAEAAAAwsABY BING CHILDREN AAAYpMAAEAAAAwsABZ BING CHILDREN AAAYpMAAEAAAAwsABa BING CHILDREN AAAYpMAAEAAAAwsABb BING CHILDREN AAAYpMAAEAAAAwsABc BING CHILDREN AAAYpMAAEAAAAwsABd BING CHILDREN AAAYpMAAEAAAAwsABe BING CHILDREN AAAYpMAAEAAAAwsABf BING CHILDREN AAAYpMAAEAAAAwsABg BING CHILDREN AAAYpMAAEAAAAwsABh BING CHILDREN AAAYpMAAEAAAAwsABi OWNER TABLE_NAME ROWID ------------------------------ ------------------------------ ------------------ BING CHILDREN AAAYpMAAEAAAAwsABj 100 rows selected. SQL David Fitzjarrell |
correct query, and output, are:
SQL> select owner, table_name, row_id
2 from exceptions;
OWNER TABLE_NAME ROW_ID
---------- ---------- ------------------
BING CHILDREN AAAYp0AAEAAAF4xACK
BING CHILDREN AAAYp0AAEAAAF4xACL
BING CHILDREN AAAYp0AAEAAAF4xACM
BING CHILDREN AAAYp0AAEAAAF4xACN
BING CHILDREN AAAYp0AAEAAAF4xACO
BING CHILDREN AAAYp0AAEAAAF4xACP
BING CHILDREN AAAYp0AAEAAAF4xACQ
BING CHILDREN AAAYp0AAEAAAF4xACR
BING CHILDREN AAAYp0AAEAAAF4xACS
BING CHILDREN AAAYp0AAEAAAF4xACT
BING CHILDREN AAAYp0AAEAAAF4xACU
OWNER TABLE_NAME ROW_ID
---------- ---------- ------------------
BING CHILDREN AAAYp0AAEAAAF4xACV
BING CHILDREN AAAYp0AAEAAAF4xACW
BING CHILDREN AAAYp0AAEAAAF4xACX
BING CHILDREN AAAYp0AAEAAAF4xACY
BING CHILDREN AAAYp0AAEAAAF4xACZ
BING CHILDREN AAAYp0AAEAAAF4xACa
BING CHILDREN AAAYp0AAEAAAF4xACb
BING CHILDREN AAAYp0AAEAAAF4xACc
BING CHILDREN AAAYp0AAEAAAF4xACd
BING CHILDREN AAAYp0AAEAAAF4xACe
BING CHILDREN AAAYp0AAEAAAF4xACf
OWNER TABLE_NAME ROW_ID
---------- ---------- ------------------
BING CHILDREN AAAYp0AAEAAAF4xACg
BING CHILDREN AAAYp0AAEAAAF4xACh
BING CHILDREN AAAYp0AAEAAAF4xACi
BING CHILDREN AAAYp0AAEAAAF4xACj
BING CHILDREN AAAYp0AAEAAAF4xACk
BING CHILDREN AAAYp0AAEAAAF4xACl
BING CHILDREN AAAYp0AAEAAAF4xACm
BING CHILDREN AAAYp0AAEAAAF4xACn
BING CHILDREN AAAYp0AAEAAAF4xACo
BING CHILDREN AAAYp0AAEAAAF4xACp
BING CHILDREN AAAYp0AAEAAAF4xACq
OWNER TABLE_NAME ROW_ID
---------- ---------- ------------------
BING CHILDREN AAAYp0AAEAAAF4xACr
BING CHILDREN AAAYp0AAEAAAF4xACs
BING CHILDREN AAAYp0AAEAAAF4xACt
BING CHILDREN AAAYp0AAEAAAF4xACu
BING CHILDREN AAAYp0AAEAAAF4xACv
BING CHILDREN AAAYp0AAEAAAF4xACw
BING CHILDREN AAAYp0AAEAAAF4xACx
BING CHILDREN AAAYp0AAEAAAF4xACy
BING CHILDREN AAAYp0AAEAAAF4xACz
BING CHILDREN AAAYp0AAEAAAF4xAC0
BING CHILDREN AAAYp0AAEAAAF4xAC1
OWNER TABLE_NAME ROW_ID
---------- ---------- ------------------
BING CHILDREN AAAYp0AAEAAAF4xAC2
BING CHILDREN AAAYp0AAEAAAF4xAC3
BING CHILDREN AAAYp0AAEAAAF4xAC4
BING CHILDREN AAAYp0AAEAAAF4xAC5
BING CHILDREN AAAYp0AAEAAAF4xAC6
BING CHILDREN AAAYp0AAEAAAF4xAC7
BING CHILDREN AAAYp0AAEAAAF4xAC8
BING CHILDREN AAAYp0AAEAAAF4xAC9
BING CHILDREN AAAYp0AAEAAAF4xAC+
BING CHILDREN AAAYp0AAEAAAF4xAC/
BING CHILDREN AAAYp0AAEAAAF4xADA
OWNER TABLE_NAME ROW_ID
---------- ---------- ------------------
BING CHILDREN AAAYp0AAEAAAF4xADB
BING CHILDREN AAAYp0AAEAAAF4xADC
BING CHILDREN AAAYp0AAEAAAF4xADD
BING CHILDREN AAAYp0AAEAAAF4xADE
BING CHILDREN AAAYp0AAEAAAF4xADF
BING CHILDREN AAAYp0AAEAAAF4xADG
BING CHILDREN AAAYp0AAEAAAF4xADH
BING CHILDREN AAAYp0AAEAAAF4xADI
BING CHILDREN AAAYp0AAEAAAF4xADJ
BING CHILDREN AAAYp0AAEAAAF4xADK
BING CHILDREN AAAYp0AAEAAAF4xADL
OWNER TABLE_NAME ROW_ID
---------- ---------- ------------------
BING CHILDREN AAAYp0AAEAAAF4xADM
BING CHILDREN AAAYp0AAEAAAF41AAA
BING CHILDREN AAAYp0AAEAAAF41AAB
BING CHILDREN AAAYp0AAEAAAF41AAC
BING CHILDREN AAAYp0AAEAAAF41AAD
BING CHILDREN AAAYp0AAEAAAF41AAE
BING CHILDREN AAAYp0AAEAAAF41AAF
BING CHILDREN AAAYp0AAEAAAF41AAG
BING CHILDREN AAAYp0AAEAAAF41AAH
BING CHILDREN AAAYp0AAEAAAF41AAI
BING CHILDREN AAAYp0AAEAAAF41AAJ
OWNER TABLE_NAME ROW_ID
---------- ---------- ------------------
BING CHILDREN AAAYp0AAEAAAF41AAK
BING CHILDREN AAAYp0AAEAAAF41AAL
BING CHILDREN AAAYp0AAEAAAF41AAM
BING CHILDREN AAAYp0AAEAAAF41AAN
BING CHILDREN AAAYp0AAEAAAF41AAO
BING CHILDREN AAAYp0AAEAAAF41AAP
BING CHILDREN AAAYp0AAEAAAF41AAQ
BING CHILDREN AAAYp0AAEAAAF41AAR
BING CHILDREN AAAYp0AAEAAAF41AAS
BING CHILDREN AAAYp0AAEAAAF41AAT
BING CHILDREN AAAYp0AAEAAAF41AAU
OWNER TABLE_NAME ROW_ID
---------- ---------- ------------------
BING CHILDREN AAAYp0AAEAAAF41AAV
BING CHILDREN AAAYp0AAEAAAF41AAW
BING CHILDREN AAAYp0AAEAAAF41AAX
BING CHILDREN AAAYp0AAEAAAF41AAY
BING CHILDREN AAAYp0AAEAAAF41AAZ
BING CHILDREN AAAYp0AAEAAAF41AAa
BING CHILDREN AAAYp0AAEAAAF41AAb
BING CHILDREN AAAYp0AAEAAAF41AAc
BING CHILDREN AAAYp0AAEAAAF41AAd
BING CHILDREN AAAYp0AAEAAAF41AAe
BING CHILDREN AAAYp0AAEAAAF41AAf
OWNER TABLE_NAME ROW_ID
---------- ---------- ------------------
BING CHILDREN AAAYp0AAEAAAF41AAg
100 rows selected.
SQL>
To return the missing parent key values:
SQL> select parent_id
2 from children
3 where rowid in (select row_id from exceptions where table_name =
'CHILDREN');
PARENT_ID
----------
100001
100002
100003
100004
100005
100006
100007
100008
100009
100010
100011
PARENT_ID
----------
100012
100013
100014
100015
100016
100017
100018
100019
100020
100021
100022
PARENT_ID
----------
100023
100024
100025
100026
100027
100028
100029
100030
100031
100032
100033
PARENT_ID
----------
100034
100035
100036
100037
100038
100039
100040
100041
100042
100043
100044
PARENT_ID
----------
100045
100046
100047
100048
100049
100050
100051
100052
100053
100054
100055
PARENT_ID
----------
100056
100057
100058
100059
100060
100061
100062
100063
100064
100065
100066
PARENT_ID
----------
100067
100068
100069
100070
100071
100072
100073
100074
100075
100076
100077
PARENT_ID
----------
100078
100079
100080
100081
100082
100083
100084
100085
100086
100087
100088
PARENT_ID
----------
100089
100090
100091
100092
100093
100094
100095
100096
100097
100098
100099
PARENT_ID
----------
100100
100 rows selected.
SQL>
David Fitzjarrell
#12
| |||
| |||
|
|
On Sep 19, 2:56 am, "timo" <timo.ta... (AT) luukku (DOT) com> wrote: Hi, what's the most efficient way to search rows in a (big) table preventing FK to be created; we've a table where PK consists of several columns and those columns are referenced by child tables. And as the table has already rows with problems those should be identified easily and corrected - so now we get ORA-02298: cannot validate (AAA.BBBBB_FK) - parent keys not found BR, Timo Let Oracle find them for you by creating the EXCEPTIONS table and using it as shown below: SQL> @?/rdbms/admin/utlexcpt SQL> rem SQL> rem $Header: utlexcpt.sql,v 1.1 1992/10/20 11:57:02 GLUMPKIN Stab $ SQL> rem SQL> Rem Copyright (c) 1991 by Oracle Corporation SQL> Rem NAME SQL> Rem except.sql - <one-line expansion of the name SQL> Rem DESCRIPTION SQL> Rem <short description of component this file declares/ defines SQL> Rem RETURNS SQL> Rem SQL> Rem NOTES SQL> Rem <other useful comments, qualifications, etc. SQL> Rem MODIFIED (MM/DD/YY) SQL> Rem glumpkin 10/20/92 - Renamed from EXCEPT.SQL SQL> Rem epeeler 07/22/91 - add comma SQL> Rem epeeler 04/30/91 - Creation SQL SQL> create table exceptions(row_id rowid, 2 owner varchar2(30), 3 table_name varchar2(30), 4 constraint varchar2(30)); Table created. SQL SQL> create table parents( 2 my_id number, 3 mydata varchar2(40) 4 ); Table created. SQL SQL> alter table parents 2 add constraint parents_pk 3 primary key(my_id); Table altered. SQL SQL> create table children( 2 child_id number, 3 child_loc varchar2(40), 4 parent_id number 5 ); Table created. SQL SQL> alter table children 2 add constraint children_pk 3 primary key(child_id); Table altered. SQL SQL> begin 2 for i in 1..100000 loop 3 insert into parents 4 values(i, 'Location #'||i); 5 end loop; 6 7 for i in 1..100100 loop 8 insert into children 9 values(i-1, 'Sub-location #'||i, i); 10 end loop; 11 12 commit; 13 14 end; 15 / PL/SQL procedure successfully completed. SQL SQL> alter table children 2 add constraint parent_child_fk 3 foreign key (parent_id) references parents(my_id) 4 exceptions into exceptions; add constraint parent_child_fk * ERROR at line 2: ORA-02298: cannot validate (BING.PARENT_CHILD_FK) - parent keys not found SQL SQL> select count(*) from exceptions; COUNT(*) ---------- 100 SQL SQL> select owner, table_name, rowid 2 from exceptions; OWNER TABLE_NAME ROWID ------------------------------ ------------------------------ ------------------ BING CHILDREN AAAYpMAAEAAAAwsAAA BING CHILDREN AAAYpMAAEAAAAwsAAB BING CHILDREN AAAYpMAAEAAAAwsAAC BING CHILDREN AAAYpMAAEAAAAwsAAD BING CHILDREN AAAYpMAAEAAAAwsAAE BING CHILDREN AAAYpMAAEAAAAwsAAF BING CHILDREN AAAYpMAAEAAAAwsAAG BING CHILDREN AAAYpMAAEAAAAwsAAH BING CHILDREN AAAYpMAAEAAAAwsAAI BING CHILDREN AAAYpMAAEAAAAwsAAJ BING CHILDREN AAAYpMAAEAAAAwsAAK OWNER TABLE_NAME ROWID ------------------------------ ------------------------------ ------------------ BING CHILDREN AAAYpMAAEAAAAwsAAL BING CHILDREN AAAYpMAAEAAAAwsAAM BING CHILDREN AAAYpMAAEAAAAwsAAN BING CHILDREN AAAYpMAAEAAAAwsAAO BING CHILDREN AAAYpMAAEAAAAwsAAP BING CHILDREN AAAYpMAAEAAAAwsAAQ BING CHILDREN AAAYpMAAEAAAAwsAAR BING CHILDREN AAAYpMAAEAAAAwsAAS BING CHILDREN AAAYpMAAEAAAAwsAAT BING CHILDREN AAAYpMAAEAAAAwsAAU BING CHILDREN AAAYpMAAEAAAAwsAAV OWNER TABLE_NAME ROWID ------------------------------ ------------------------------ ------------------ BING CHILDREN AAAYpMAAEAAAAwsAAW BING CHILDREN AAAYpMAAEAAAAwsAAX BING CHILDREN AAAYpMAAEAAAAwsAAY BING CHILDREN AAAYpMAAEAAAAwsAAZ BING CHILDREN AAAYpMAAEAAAAwsAAa BING CHILDREN AAAYpMAAEAAAAwsAAb BING CHILDREN AAAYpMAAEAAAAwsAAc BING CHILDREN AAAYpMAAEAAAAwsAAd BING CHILDREN AAAYpMAAEAAAAwsAAe BING CHILDREN AAAYpMAAEAAAAwsAAf BING CHILDREN AAAYpMAAEAAAAwsAAg OWNER TABLE_NAME ROWID ------------------------------ ------------------------------ ------------------ BING CHILDREN AAAYpMAAEAAAAwsAAh BING CHILDREN AAAYpMAAEAAAAwsAAi BING CHILDREN AAAYpMAAEAAAAwsAAj BING CHILDREN AAAYpMAAEAAAAwsAAk BING CHILDREN AAAYpMAAEAAAAwsAAl BING CHILDREN AAAYpMAAEAAAAwsAAm BING CHILDREN AAAYpMAAEAAAAwsAAn BING CHILDREN AAAYpMAAEAAAAwsAAo BING CHILDREN AAAYpMAAEAAAAwsAAp BING CHILDREN AAAYpMAAEAAAAwsAAq BING CHILDREN AAAYpMAAEAAAAwsAAr OWNER TABLE_NAME ROWID ------------------------------ ------------------------------ ------------------ BING CHILDREN AAAYpMAAEAAAAwsAAs BING CHILDREN AAAYpMAAEAAAAwsAAt BING CHILDREN AAAYpMAAEAAAAwsAAu BING CHILDREN AAAYpMAAEAAAAwsAAv BING CHILDREN AAAYpMAAEAAAAwsAAw BING CHILDREN AAAYpMAAEAAAAwsAAx BING CHILDREN AAAYpMAAEAAAAwsAAy BING CHILDREN AAAYpMAAEAAAAwsAAz BING CHILDREN AAAYpMAAEAAAAwsAA0 BING CHILDREN AAAYpMAAEAAAAwsAA1 BING CHILDREN AAAYpMAAEAAAAwsAA2 OWNER TABLE_NAME ROWID ------------------------------ ------------------------------ ------------------ BING CHILDREN AAAYpMAAEAAAAwsAA3 BING CHILDREN AAAYpMAAEAAAAwsAA4 BING CHILDREN AAAYpMAAEAAAAwsAA5 BING CHILDREN AAAYpMAAEAAAAwsAA6 BING CHILDREN AAAYpMAAEAAAAwsAA7 BING CHILDREN AAAYpMAAEAAAAwsAA8 BING CHILDREN AAAYpMAAEAAAAwsAA9 BING CHILDREN AAAYpMAAEAAAAwsAA+ BING CHILDREN AAAYpMAAEAAAAwsAA/ BING CHILDREN AAAYpMAAEAAAAwsABA BING CHILDREN AAAYpMAAEAAAAwsABB OWNER TABLE_NAME ROWID ------------------------------ ------------------------------ ------------------ BING CHILDREN AAAYpMAAEAAAAwsABC BING CHILDREN AAAYpMAAEAAAAwsABD BING CHILDREN AAAYpMAAEAAAAwsABE BING CHILDREN AAAYpMAAEAAAAwsABF BING CHILDREN AAAYpMAAEAAAAwsABG BING CHILDREN AAAYpMAAEAAAAwsABH BING CHILDREN AAAYpMAAEAAAAwsABI BING CHILDREN AAAYpMAAEAAAAwsABJ BING CHILDREN AAAYpMAAEAAAAwsABK BING CHILDREN AAAYpMAAEAAAAwsABL BING CHILDREN AAAYpMAAEAAAAwsABM OWNER TABLE_NAME ROWID ------------------------------ ------------------------------ ------------------ BING CHILDREN AAAYpMAAEAAAAwsABN BING CHILDREN AAAYpMAAEAAAAwsABO BING CHILDREN AAAYpMAAEAAAAwsABP BING CHILDREN AAAYpMAAEAAAAwsABQ BING CHILDREN AAAYpMAAEAAAAwsABR BING CHILDREN AAAYpMAAEAAAAwsABS BING CHILDREN AAAYpMAAEAAAAwsABT BING CHILDREN AAAYpMAAEAAAAwsABU BING CHILDREN AAAYpMAAEAAAAwsABV BING CHILDREN AAAYpMAAEAAAAwsABW BING CHILDREN AAAYpMAAEAAAAwsABX OWNER TABLE_NAME ROWID ------------------------------ ------------------------------ ------------------ BING CHILDREN AAAYpMAAEAAAAwsABY BING CHILDREN AAAYpMAAEAAAAwsABZ BING CHILDREN AAAYpMAAEAAAAwsABa BING CHILDREN AAAYpMAAEAAAAwsABb BING CHILDREN AAAYpMAAEAAAAwsABc BING CHILDREN AAAYpMAAEAAAAwsABd BING CHILDREN AAAYpMAAEAAAAwsABe BING CHILDREN AAAYpMAAEAAAAwsABf BING CHILDREN AAAYpMAAEAAAAwsABg BING CHILDREN AAAYpMAAEAAAAwsABh BING CHILDREN AAAYpMAAEAAAAwsABi OWNER TABLE_NAME ROWID ------------------------------ ------------------------------ ------------------ BING CHILDREN AAAYpMAAEAAAAwsABj 100 rows selected. SQL David Fitzjarrell |
correct query, and output, are:
SQL> select owner, table_name, row_id
2 from exceptions;
OWNER TABLE_NAME ROW_ID
---------- ---------- ------------------
BING CHILDREN AAAYp0AAEAAAF4xACK
BING CHILDREN AAAYp0AAEAAAF4xACL
BING CHILDREN AAAYp0AAEAAAF4xACM
BING CHILDREN AAAYp0AAEAAAF4xACN
BING CHILDREN AAAYp0AAEAAAF4xACO
BING CHILDREN AAAYp0AAEAAAF4xACP
BING CHILDREN AAAYp0AAEAAAF4xACQ
BING CHILDREN AAAYp0AAEAAAF4xACR
BING CHILDREN AAAYp0AAEAAAF4xACS
BING CHILDREN AAAYp0AAEAAAF4xACT
BING CHILDREN AAAYp0AAEAAAF4xACU
OWNER TABLE_NAME ROW_ID
---------- ---------- ------------------
BING CHILDREN AAAYp0AAEAAAF4xACV
BING CHILDREN AAAYp0AAEAAAF4xACW
BING CHILDREN AAAYp0AAEAAAF4xACX
BING CHILDREN AAAYp0AAEAAAF4xACY
BING CHILDREN AAAYp0AAEAAAF4xACZ
BING CHILDREN AAAYp0AAEAAAF4xACa
BING CHILDREN AAAYp0AAEAAAF4xACb
BING CHILDREN AAAYp0AAEAAAF4xACc
BING CHILDREN AAAYp0AAEAAAF4xACd
BING CHILDREN AAAYp0AAEAAAF4xACe
BING CHILDREN AAAYp0AAEAAAF4xACf
OWNER TABLE_NAME ROW_ID
---------- ---------- ------------------
BING CHILDREN AAAYp0AAEAAAF4xACg
BING CHILDREN AAAYp0AAEAAAF4xACh
BING CHILDREN AAAYp0AAEAAAF4xACi
BING CHILDREN AAAYp0AAEAAAF4xACj
BING CHILDREN AAAYp0AAEAAAF4xACk
BING CHILDREN AAAYp0AAEAAAF4xACl
BING CHILDREN AAAYp0AAEAAAF4xACm
BING CHILDREN AAAYp0AAEAAAF4xACn
BING CHILDREN AAAYp0AAEAAAF4xACo
BING CHILDREN AAAYp0AAEAAAF4xACp
BING CHILDREN AAAYp0AAEAAAF4xACq
OWNER TABLE_NAME ROW_ID
---------- ---------- ------------------
BING CHILDREN AAAYp0AAEAAAF4xACr
BING CHILDREN AAAYp0AAEAAAF4xACs
BING CHILDREN AAAYp0AAEAAAF4xACt
BING CHILDREN AAAYp0AAEAAAF4xACu
BING CHILDREN AAAYp0AAEAAAF4xACv
BING CHILDREN AAAYp0AAEAAAF4xACw
BING CHILDREN AAAYp0AAEAAAF4xACx
BING CHILDREN AAAYp0AAEAAAF4xACy
BING CHILDREN AAAYp0AAEAAAF4xACz
BING CHILDREN AAAYp0AAEAAAF4xAC0
BING CHILDREN AAAYp0AAEAAAF4xAC1
OWNER TABLE_NAME ROW_ID
---------- ---------- ------------------
BING CHILDREN AAAYp0AAEAAAF4xAC2
BING CHILDREN AAAYp0AAEAAAF4xAC3
BING CHILDREN AAAYp0AAEAAAF4xAC4
BING CHILDREN AAAYp0AAEAAAF4xAC5
BING CHILDREN AAAYp0AAEAAAF4xAC6
BING CHILDREN AAAYp0AAEAAAF4xAC7
BING CHILDREN AAAYp0AAEAAAF4xAC8
BING CHILDREN AAAYp0AAEAAAF4xAC9
BING CHILDREN AAAYp0AAEAAAF4xAC+
BING CHILDREN AAAYp0AAEAAAF4xAC/
BING CHILDREN AAAYp0AAEAAAF4xADA
OWNER TABLE_NAME ROW_ID
---------- ---------- ------------------
BING CHILDREN AAAYp0AAEAAAF4xADB
BING CHILDREN AAAYp0AAEAAAF4xADC
BING CHILDREN AAAYp0AAEAAAF4xADD
BING CHILDREN AAAYp0AAEAAAF4xADE
BING CHILDREN AAAYp0AAEAAAF4xADF
BING CHILDREN AAAYp0AAEAAAF4xADG
BING CHILDREN AAAYp0AAEAAAF4xADH
BING CHILDREN AAAYp0AAEAAAF4xADI
BING CHILDREN AAAYp0AAEAAAF4xADJ
BING CHILDREN AAAYp0AAEAAAF4xADK
BING CHILDREN AAAYp0AAEAAAF4xADL
OWNER TABLE_NAME ROW_ID
---------- ---------- ------------------
BING CHILDREN AAAYp0AAEAAAF4xADM
BING CHILDREN AAAYp0AAEAAAF41AAA
BING CHILDREN AAAYp0AAEAAAF41AAB
BING CHILDREN AAAYp0AAEAAAF41AAC
BING CHILDREN AAAYp0AAEAAAF41AAD
BING CHILDREN AAAYp0AAEAAAF41AAE
BING CHILDREN AAAYp0AAEAAAF41AAF
BING CHILDREN AAAYp0AAEAAAF41AAG
BING CHILDREN AAAYp0AAEAAAF41AAH
BING CHILDREN AAAYp0AAEAAAF41AAI
BING CHILDREN AAAYp0AAEAAAF41AAJ
OWNER TABLE_NAME ROW_ID
---------- ---------- ------------------
BING CHILDREN AAAYp0AAEAAAF41AAK
BING CHILDREN AAAYp0AAEAAAF41AAL
BING CHILDREN AAAYp0AAEAAAF41AAM
BING CHILDREN AAAYp0AAEAAAF41AAN
BING CHILDREN AAAYp0AAEAAAF41AAO
BING CHILDREN AAAYp0AAEAAAF41AAP
BING CHILDREN AAAYp0AAEAAAF41AAQ
BING CHILDREN AAAYp0AAEAAAF41AAR
BING CHILDREN AAAYp0AAEAAAF41AAS
BING CHILDREN AAAYp0AAEAAAF41AAT
BING CHILDREN AAAYp0AAEAAAF41AAU
OWNER TABLE_NAME ROW_ID
---------- ---------- ------------------
BING CHILDREN AAAYp0AAEAAAF41AAV
BING CHILDREN AAAYp0AAEAAAF41AAW
BING CHILDREN AAAYp0AAEAAAF41AAX
BING CHILDREN AAAYp0AAEAAAF41AAY
BING CHILDREN AAAYp0AAEAAAF41AAZ
BING CHILDREN AAAYp0AAEAAAF41AAa
BING CHILDREN AAAYp0AAEAAAF41AAb
BING CHILDREN AAAYp0AAEAAAF41AAc
BING CHILDREN AAAYp0AAEAAAF41AAd
BING CHILDREN AAAYp0AAEAAAF41AAe
BING CHILDREN AAAYp0AAEAAAF41AAf
OWNER TABLE_NAME ROW_ID
---------- ---------- ------------------
BING CHILDREN AAAYp0AAEAAAF41AAg
100 rows selected.
SQL>
To return the missing parent key values:
SQL> select parent_id
2 from children
3 where rowid in (select row_id from exceptions where table_name =
'CHILDREN');
PARENT_ID
----------
100001
100002
100003
100004
100005
100006
100007
100008
100009
100010
100011
PARENT_ID
----------
100012
100013
100014
100015
100016
100017
100018
100019
100020
100021
100022
PARENT_ID
----------
100023
100024
100025
100026
100027
100028
100029
100030
100031
100032
100033
PARENT_ID
----------
100034
100035
100036
100037
100038
100039
100040
100041
100042
100043
100044
PARENT_ID
----------
100045
100046
100047
100048
100049
100050
100051
100052
100053
100054
100055
PARENT_ID
----------
100056
100057
100058
100059
100060
100061
100062
100063
100064
100065
100066
PARENT_ID
----------
100067
100068
100069
100070
100071
100072
100073
100074
100075
100076
100077
PARENT_ID
----------
100078
100079
100080
100081
100082
100083
100084
100085
100086
100087
100088
PARENT_ID
----------
100089
100090
100091
100092
100093
100094
100095
100096
100097
100098
100099
PARENT_ID
----------
100100
100 rows selected.
SQL>
David Fitzjarrell
#13
| |||
| |||
|
|
On Sep 19, 2:56 am, "timo" <timo.ta... (AT) luukku (DOT) com> wrote: Hi, what's the most efficient way to search rows in a (big) table preventing FK to be created; we've a table where PK consists of several columns and those columns are referenced by child tables. And as the table has already rows with problems those should be identified easily and corrected - so now we get ORA-02298: cannot validate (AAA.BBBBB_FK) - parent keys not found BR, Timo Let Oracle find them for you by creating the EXCEPTIONS table and using it as shown below: SQL> @?/rdbms/admin/utlexcpt SQL> rem SQL> rem $Header: utlexcpt.sql,v 1.1 1992/10/20 11:57:02 GLUMPKIN Stab $ SQL> rem SQL> Rem Copyright (c) 1991 by Oracle Corporation SQL> Rem NAME SQL> Rem except.sql - <one-line expansion of the name SQL> Rem DESCRIPTION SQL> Rem <short description of component this file declares/ defines SQL> Rem RETURNS SQL> Rem SQL> Rem NOTES SQL> Rem <other useful comments, qualifications, etc. SQL> Rem MODIFIED (MM/DD/YY) SQL> Rem glumpkin 10/20/92 - Renamed from EXCEPT.SQL SQL> Rem epeeler 07/22/91 - add comma SQL> Rem epeeler 04/30/91 - Creation SQL SQL> create table exceptions(row_id rowid, 2 owner varchar2(30), 3 table_name varchar2(30), 4 constraint varchar2(30)); Table created. SQL SQL> create table parents( 2 my_id number, 3 mydata varchar2(40) 4 ); Table created. SQL SQL> alter table parents 2 add constraint parents_pk 3 primary key(my_id); Table altered. SQL SQL> create table children( 2 child_id number, 3 child_loc varchar2(40), 4 parent_id number 5 ); Table created. SQL SQL> alter table children 2 add constraint children_pk 3 primary key(child_id); Table altered. SQL SQL> begin 2 for i in 1..100000 loop 3 insert into parents 4 values(i, 'Location #'||i); 5 end loop; 6 7 for i in 1..100100 loop 8 insert into children 9 values(i-1, 'Sub-location #'||i, i); 10 end loop; 11 12 commit; 13 14 end; 15 / PL/SQL procedure successfully completed. SQL SQL> alter table children 2 add constraint parent_child_fk 3 foreign key (parent_id) references parents(my_id) 4 exceptions into exceptions; add constraint parent_child_fk * ERROR at line 2: ORA-02298: cannot validate (BING.PARENT_CHILD_FK) - parent keys not found SQL SQL> select count(*) from exceptions; COUNT(*) ---------- 100 SQL SQL> select owner, table_name, rowid 2 from exceptions; OWNER TABLE_NAME ROWID ------------------------------ ------------------------------ ------------------ BING CHILDREN AAAYpMAAEAAAAwsAAA BING CHILDREN AAAYpMAAEAAAAwsAAB BING CHILDREN AAAYpMAAEAAAAwsAAC BING CHILDREN AAAYpMAAEAAAAwsAAD BING CHILDREN AAAYpMAAEAAAAwsAAE BING CHILDREN AAAYpMAAEAAAAwsAAF BING CHILDREN AAAYpMAAEAAAAwsAAG BING CHILDREN AAAYpMAAEAAAAwsAAH BING CHILDREN AAAYpMAAEAAAAwsAAI BING CHILDREN AAAYpMAAEAAAAwsAAJ BING CHILDREN AAAYpMAAEAAAAwsAAK OWNER TABLE_NAME ROWID ------------------------------ ------------------------------ ------------------ BING CHILDREN AAAYpMAAEAAAAwsAAL BING CHILDREN AAAYpMAAEAAAAwsAAM BING CHILDREN AAAYpMAAEAAAAwsAAN BING CHILDREN AAAYpMAAEAAAAwsAAO BING CHILDREN AAAYpMAAEAAAAwsAAP BING CHILDREN AAAYpMAAEAAAAwsAAQ BING CHILDREN AAAYpMAAEAAAAwsAAR BING CHILDREN AAAYpMAAEAAAAwsAAS BING CHILDREN AAAYpMAAEAAAAwsAAT BING CHILDREN AAAYpMAAEAAAAwsAAU BING CHILDREN AAAYpMAAEAAAAwsAAV OWNER TABLE_NAME ROWID ------------------------------ ------------------------------ ------------------ BING CHILDREN AAAYpMAAEAAAAwsAAW BING CHILDREN AAAYpMAAEAAAAwsAAX BING CHILDREN AAAYpMAAEAAAAwsAAY BING CHILDREN AAAYpMAAEAAAAwsAAZ BING CHILDREN AAAYpMAAEAAAAwsAAa BING CHILDREN AAAYpMAAEAAAAwsAAb BING CHILDREN AAAYpMAAEAAAAwsAAc BING CHILDREN AAAYpMAAEAAAAwsAAd BING CHILDREN AAAYpMAAEAAAAwsAAe BING CHILDREN AAAYpMAAEAAAAwsAAf BING CHILDREN AAAYpMAAEAAAAwsAAg OWNER TABLE_NAME ROWID ------------------------------ ------------------------------ ------------------ BING CHILDREN AAAYpMAAEAAAAwsAAh BING CHILDREN AAAYpMAAEAAAAwsAAi BING CHILDREN AAAYpMAAEAAAAwsAAj BING CHILDREN AAAYpMAAEAAAAwsAAk BING CHILDREN AAAYpMAAEAAAAwsAAl BING CHILDREN AAAYpMAAEAAAAwsAAm BING CHILDREN AAAYpMAAEAAAAwsAAn BING CHILDREN AAAYpMAAEAAAAwsAAo BING CHILDREN AAAYpMAAEAAAAwsAAp BING CHILDREN AAAYpMAAEAAAAwsAAq BING CHILDREN AAAYpMAAEAAAAwsAAr OWNER TABLE_NAME ROWID ------------------------------ ------------------------------ ------------------ BING CHILDREN AAAYpMAAEAAAAwsAAs BING CHILDREN AAAYpMAAEAAAAwsAAt BING CHILDREN AAAYpMAAEAAAAwsAAu BING CHILDREN AAAYpMAAEAAAAwsAAv BING CHILDREN AAAYpMAAEAAAAwsAAw BING CHILDREN AAAYpMAAEAAAAwsAAx BING CHILDREN AAAYpMAAEAAAAwsAAy BING CHILDREN AAAYpMAAEAAAAwsAAz BING CHILDREN AAAYpMAAEAAAAwsAA0 BING CHILDREN AAAYpMAAEAAAAwsAA1 BING CHILDREN AAAYpMAAEAAAAwsAA2 OWNER TABLE_NAME ROWID ------------------------------ ------------------------------ ------------------ BING CHILDREN AAAYpMAAEAAAAwsAA3 BING CHILDREN AAAYpMAAEAAAAwsAA4 BING CHILDREN AAAYpMAAEAAAAwsAA5 BING CHILDREN AAAYpMAAEAAAAwsAA6 BING CHILDREN AAAYpMAAEAAAAwsAA7 BING CHILDREN AAAYpMAAEAAAAwsAA8 BING CHILDREN AAAYpMAAEAAAAwsAA9 BING CHILDREN AAAYpMAAEAAAAwsAA+ BING CHILDREN AAAYpMAAEAAAAwsAA/ BING CHILDREN AAAYpMAAEAAAAwsABA BING CHILDREN AAAYpMAAEAAAAwsABB OWNER TABLE_NAME ROWID ------------------------------ ------------------------------ ------------------ BING CHILDREN AAAYpMAAEAAAAwsABC BING CHILDREN AAAYpMAAEAAAAwsABD BING CHILDREN AAAYpMAAEAAAAwsABE BING CHILDREN AAAYpMAAEAAAAwsABF BING CHILDREN AAAYpMAAEAAAAwsABG BING CHILDREN AAAYpMAAEAAAAwsABH BING CHILDREN AAAYpMAAEAAAAwsABI BING CHILDREN AAAYpMAAEAAAAwsABJ BING CHILDREN AAAYpMAAEAAAAwsABK BING CHILDREN AAAYpMAAEAAAAwsABL BING CHILDREN AAAYpMAAEAAAAwsABM OWNER TABLE_NAME ROWID ------------------------------ ------------------------------ ------------------ BING CHILDREN AAAYpMAAEAAAAwsABN BING CHILDREN AAAYpMAAEAAAAwsABO BING CHILDREN AAAYpMAAEAAAAwsABP BING CHILDREN AAAYpMAAEAAAAwsABQ BING CHILDREN AAAYpMAAEAAAAwsABR BING CHILDREN AAAYpMAAEAAAAwsABS BING CHILDREN AAAYpMAAEAAAAwsABT BING CHILDREN AAAYpMAAEAAAAwsABU BING CHILDREN AAAYpMAAEAAAAwsABV BING CHILDREN AAAYpMAAEAAAAwsABW BING CHILDREN AAAYpMAAEAAAAwsABX OWNER TABLE_NAME ROWID ------------------------------ ------------------------------ ------------------ BING CHILDREN AAAYpMAAEAAAAwsABY BING CHILDREN AAAYpMAAEAAAAwsABZ BING CHILDREN AAAYpMAAEAAAAwsABa BING CHILDREN AAAYpMAAEAAAAwsABb BING CHILDREN AAAYpMAAEAAAAwsABc BING CHILDREN AAAYpMAAEAAAAwsABd BING CHILDREN AAAYpMAAEAAAAwsABe BING CHILDREN AAAYpMAAEAAAAwsABf BING CHILDREN AAAYpMAAEAAAAwsABg BING CHILDREN AAAYpMAAEAAAAwsABh BING CHILDREN AAAYpMAAEAAAAwsABi OWNER TABLE_NAME ROWID ------------------------------ ------------------------------ ------------------ BING CHILDREN AAAYpMAAEAAAAwsABj 100 rows selected. SQL David Fitzjarrell |
correct query, and output, are:
SQL> select owner, table_name, row_id
2 from exceptions;
OWNER TABLE_NAME ROW_ID
---------- ---------- ------------------
BING CHILDREN AAAYp0AAEAAAF4xACK
BING CHILDREN AAAYp0AAEAAAF4xACL
BING CHILDREN AAAYp0AAEAAAF4xACM
BING CHILDREN AAAYp0AAEAAAF4xACN
BING CHILDREN AAAYp0AAEAAAF4xACO
BING CHILDREN AAAYp0AAEAAAF4xACP
BING CHILDREN AAAYp0AAEAAAF4xACQ
BING CHILDREN AAAYp0AAEAAAF4xACR
BING CHILDREN AAAYp0AAEAAAF4xACS
BING CHILDREN AAAYp0AAEAAAF4xACT
BING CHILDREN AAAYp0AAEAAAF4xACU
OWNER TABLE_NAME ROW_ID
---------- ---------- ------------------
BING CHILDREN AAAYp0AAEAAAF4xACV
BING CHILDREN AAAYp0AAEAAAF4xACW
BING CHILDREN AAAYp0AAEAAAF4xACX
BING CHILDREN AAAYp0AAEAAAF4xACY
BING CHILDREN AAAYp0AAEAAAF4xACZ
BING CHILDREN AAAYp0AAEAAAF4xACa
BING CHILDREN AAAYp0AAEAAAF4xACb
BING CHILDREN AAAYp0AAEAAAF4xACc
BING CHILDREN AAAYp0AAEAAAF4xACd
BING CHILDREN AAAYp0AAEAAAF4xACe
BING CHILDREN AAAYp0AAEAAAF4xACf
OWNER TABLE_NAME ROW_ID
---------- ---------- ------------------
BING CHILDREN AAAYp0AAEAAAF4xACg
BING CHILDREN AAAYp0AAEAAAF4xACh
BING CHILDREN AAAYp0AAEAAAF4xACi
BING CHILDREN AAAYp0AAEAAAF4xACj
BING CHILDREN AAAYp0AAEAAAF4xACk
BING CHILDREN AAAYp0AAEAAAF4xACl
BING CHILDREN AAAYp0AAEAAAF4xACm
BING CHILDREN AAAYp0AAEAAAF4xACn
BING CHILDREN AAAYp0AAEAAAF4xACo
BING CHILDREN AAAYp0AAEAAAF4xACp
BING CHILDREN AAAYp0AAEAAAF4xACq
OWNER TABLE_NAME ROW_ID
---------- ---------- ------------------
BING CHILDREN AAAYp0AAEAAAF4xACr
BING CHILDREN AAAYp0AAEAAAF4xACs
BING CHILDREN AAAYp0AAEAAAF4xACt
BING CHILDREN AAAYp0AAEAAAF4xACu
BING CHILDREN AAAYp0AAEAAAF4xACv
BING CHILDREN AAAYp0AAEAAAF4xACw
BING CHILDREN AAAYp0AAEAAAF4xACx
BING CHILDREN AAAYp0AAEAAAF4xACy
BING CHILDREN AAAYp0AAEAAAF4xACz
BING CHILDREN AAAYp0AAEAAAF4xAC0
BING CHILDREN AAAYp0AAEAAAF4xAC1
OWNER TABLE_NAME ROW_ID
---------- ---------- ------------------
BING CHILDREN AAAYp0AAEAAAF4xAC2
BING CHILDREN AAAYp0AAEAAAF4xAC3
BING CHILDREN AAAYp0AAEAAAF4xAC4
BING CHILDREN AAAYp0AAEAAAF4xAC5
BING CHILDREN AAAYp0AAEAAAF4xAC6
BING CHILDREN AAAYp0AAEAAAF4xAC7
BING CHILDREN AAAYp0AAEAAAF4xAC8
BING CHILDREN AAAYp0AAEAAAF4xAC9
BING CHILDREN AAAYp0AAEAAAF4xAC+
BING CHILDREN AAAYp0AAEAAAF4xAC/
BING CHILDREN AAAYp0AAEAAAF4xADA
OWNER TABLE_NAME ROW_ID
---------- ---------- ------------------
BING CHILDREN AAAYp0AAEAAAF4xADB
BING CHILDREN AAAYp0AAEAAAF4xADC
BING CHILDREN AAAYp0AAEAAAF4xADD
BING CHILDREN AAAYp0AAEAAAF4xADE
BING CHILDREN AAAYp0AAEAAAF4xADF
BING CHILDREN AAAYp0AAEAAAF4xADG
BING CHILDREN AAAYp0AAEAAAF4xADH
BING CHILDREN AAAYp0AAEAAAF4xADI
BING CHILDREN AAAYp0AAEAAAF4xADJ
BING CHILDREN AAAYp0AAEAAAF4xADK
BING CHILDREN AAAYp0AAEAAAF4xADL
OWNER TABLE_NAME ROW_ID
---------- ---------- ------------------
BING CHILDREN AAAYp0AAEAAAF4xADM
BING CHILDREN AAAYp0AAEAAAF41AAA
BING CHILDREN AAAYp0AAEAAAF41AAB
BING CHILDREN AAAYp0AAEAAAF41AAC
BING CHILDREN AAAYp0AAEAAAF41AAD
BING CHILDREN AAAYp0AAEAAAF41AAE
BING CHILDREN AAAYp0AAEAAAF41AAF
BING CHILDREN AAAYp0AAEAAAF41AAG
BING CHILDREN AAAYp0AAEAAAF41AAH
BING CHILDREN AAAYp0AAEAAAF41AAI
BING CHILDREN AAAYp0AAEAAAF41AAJ
OWNER TABLE_NAME ROW_ID
---------- ---------- ------------------
BING CHILDREN AAAYp0AAEAAAF41AAK
BING CHILDREN AAAYp0AAEAAAF41AAL
BING CHILDREN AAAYp0AAEAAAF41AAM
BING CHILDREN AAAYp0AAEAAAF41AAN
BING CHILDREN AAAYp0AAEAAAF41AAO
BING CHILDREN AAAYp0AAEAAAF41AAP
BING CHILDREN AAAYp0AAEAAAF41AAQ
BING CHILDREN AAAYp0AAEAAAF41AAR
BING CHILDREN AAAYp0AAEAAAF41AAS
BING CHILDREN AAAYp0AAEAAAF41AAT
BING CHILDREN AAAYp0AAEAAAF41AAU
OWNER TABLE_NAME ROW_ID
---------- ---------- ------------------
BING CHILDREN AAAYp0AAEAAAF41AAV
BING CHILDREN AAAYp0AAEAAAF41AAW
BING CHILDREN AAAYp0AAEAAAF41AAX
BING CHILDREN AAAYp0AAEAAAF41AAY
BING CHILDREN AAAYp0AAEAAAF41AAZ
BING CHILDREN AAAYp0AAEAAAF41AAa
BING CHILDREN AAAYp0AAEAAAF41AAb
BING CHILDREN AAAYp0AAEAAAF41AAc
BING CHILDREN AAAYp0AAEAAAF41AAd
BING CHILDREN AAAYp0AAEAAAF41AAe
BING CHILDREN AAAYp0AAEAAAF41AAf
OWNER TABLE_NAME ROW_ID
---------- ---------- ------------------
BING CHILDREN AAAYp0AAEAAAF41AAg
100 rows selected.
SQL>
To return the missing parent key values:
SQL> select parent_id
2 from children
3 where rowid in (select row_id from exceptions where table_name =
'CHILDREN');
PARENT_ID
----------
100001
100002
100003
100004
100005
100006
100007
100008
100009
100010
100011
PARENT_ID
----------
100012
100013
100014
100015
100016
100017
100018
100019
100020
100021
100022
PARENT_ID
----------
100023
100024
100025
100026
100027
100028
100029
100030
100031
100032
100033
PARENT_ID
----------
100034
100035
100036
100037
100038
100039
100040
100041
100042
100043
100044
PARENT_ID
----------
100045
100046
100047
100048
100049
100050
100051
100052
100053
100054
100055
PARENT_ID
----------
100056
100057
100058
100059
100060
100061
100062
100063
100064
100065
100066
PARENT_ID
----------
100067
100068
100069
100070
100071
100072
100073
100074
100075
100076
100077
PARENT_ID
----------
100078
100079
100080
100081
100082
100083
100084
100085
100086
100087
100088
PARENT_ID
----------
100089
100090
100091
100092
100093
100094
100095
100096
100097
100098
100099
PARENT_ID
----------
100100
100 rows selected.
SQL>
David Fitzjarrell
|
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.