not in does not work?

Hi

I have this query to check weather the segments of two database reside in same tablespaces in both databases


select segment_name "trafr_seg", partition_name
from dba_segments a
where segment_name in
(
select segment_name from dba_segments@trafp b
where tablespace_name='IDX_128M_2'
and a.segment_name=b.segment_name
)
and tablespace_name='IDX_128M_2'
/


Now I want to do the other way round so check which segment in first database does not reside in same tablespaces as those in the second tablespace so simply I changed IN to NOT IN but it gives an SQL error

ERROR at line 6:
ORA-00920: invalid relational operator
ORA-02063: preceding line from TRAFP


NOT IN cannot be used in a correlated query?

NOT IN across a database link works for me. (8.1.7.2 on both systems)

I tried:

Code:

---

select table_name from user_Tables
where table_name not in (
  select table_name from user_Tables@db1)

---

Maybe a syntax error somewhere? Post your query for a second(+) set of eyes to look at it...

the query is

select segment_name "trafr_seg", partition_name
from dba_segments a
where segment_name not in
(
select segment_name from dba_segments@trafp b
where tablespace_name='INTERCON_ADD_D'
and a.segment_name=b.segment_name
)
and tablespace_name='INTERCON_ADD_D'
where tablespace_name='INTERCON_ADD_D'
*
ERROR at line 6:
ORA-00920: invalid relational operator
ORA-02063: preceding line from TRAFP

is exactly the same as the other one just with an extra NOT before in

I think it is compianing about the

where tablespace_name='INTERCON_ADD_D'

in the subquery


But I dont see why... I am puzzled


Oracle8i Enterprise Edition Release 8.1.7.1.0 - Production
on Tru64

Hmm, works for me.

Maybe if you try fully qualifying tablespace_name with b.tablespace_name??

Maybe NOT EXISTS would be different??

Either way, sounds like a bug...

tried pu a b but still getting error

NOT EXISTS wouldnt give the desired the result because I am matching rows not testing for existence right :-?

I copied your query,

select segment_name "trafr_seg", partition_name
from dba_segments a
where segment_name not in
(
select segment_name from dba_segments@trafp b
where tablespace_name='INTERCON_ADD_D'
and a.segment_name=b.segment_name
)
and tablespace_name='INTERCON_ADD_D'

and the only thing I changed was the dynamic link, to equal one of my own dynamic links. And it runs WITHOUT any errors. What version of the database are you on?

Oracle8i Enterprise Edition Release 8.1.7.1.0 - Production
on Tru64