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

On Sep 19, 2:56*am, "timo" <timo.ta... (AT) luukku (DOT) com> wrote:
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

On Sep 19, 2:56*am, "timo" <timo.ta... (AT) luukku (DOT) com> wrote:
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

On Sep 19, 2:56*am, "timo" <timo.ta... (AT) luukku (DOT) com> wrote:
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

On Sep 19, 2:56*am, "timo" <timo.ta... (AT) luukku (DOT) com> wrote:
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

On Sep 19, 7:43*am, "fitzjarr... (AT) cox (DOT) net" <orat... (AT) msn (DOT) com> wrote:
An error on my part left the output from my prior post unusable. The
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

On Sep 19, 7:43*am, "fitzjarr... (AT) cox (DOT) net" <orat... (AT) msn (DOT) com> wrote:
An error on my part left the output from my prior post unusable. The
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

On Sep 19, 7:43*am, "fitzjarr... (AT) cox (DOT) net" <orat... (AT) msn (DOT) com> wrote:
An error on my part left the output from my prior post unusable. The
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

On Sep 19, 7:43*am, "fitzjarr... (AT) cox (DOT) net" <orat... (AT) msn (DOT) com> wrote:
An error on my part left the output from my prior post unusable. The
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

Hi DAvid,
thanks for a smart way to do it !
BR,
Timo

"fitzjarrell (AT) cox (DOT) net" <oratune (AT) msn (DOT) com> wrote

On Sep 19, 7:43 am, "fitzjarr... (AT) cox (DOT) net" <orat... (AT) msn (DOT) com> wrote:
An error on my part left the output from my prior post unusable. The
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