not in does not work?

[pando]

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?

[marist89]

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

I tried:

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...

[pando]

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

[marist89]

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...

[pando]

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 :-?

[molonede]

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?

[pando]

Oracle8i Enterprise Edition Release 8.1.7.1.0 - Production
on Tru64