-
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...
Jeff Hunter
-
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...
Jeff Hunter
-
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?
Doug
-
Oracle8i Enterprise Edition Release 8.1.7.1.0 - Production
on Tru64
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|