missing entry in sys.seg$ view

[pallaviyd]

I am having one table in dba_segments whose entry is missing in sys.seg$ view.Please refer the below two queries:-

SQL> select OWNER,HEADER_FILE,HEADER_BLOCK
2 from dba_segments
3 where segment_name='TSNPJOBLOG';

OWNER HEADER_FILE HEADER_BLOCK
------------------------------ ----------- ------------
MVIEW_OWNER 76 120872



SQL> select nvl(FILE#,0),nvl(BLOCK#,0),TYPE#,TS#,MAXEXTS,EXTENTS
2 from sys.seg$
3 where file#='76' and block#='120872';

Can anyone please tell what could be the problem.

Thanks,
Pallavi

[abhaysk]

select 
  nvl(s.FILE#,0),
  nvl(s.BLOCK#,0),
  s.TYPE#,
  s.TS#,
  s.MAXEXTS,
  s.EXTENTS
from 
  sys.seg$ s, 
  sys.file$ f 
where 
  s.ts# = f.ts# and 
  s.file# = f.relfile# and
  f.file# = 76 and
  s.block# = 120872
;

BTW, seg$ is not a view, its a table!!

Rgds
Abhay.

[pallaviyd]

One more query output I would like to give:-

SQL> select nvl(FILE#,0),nvl(BLOCK#,0),TYPE#,TS#,MAXEXTS,EXTENTS
2 from sys.seg$
3 where file#='76' or block#='120872'
4 and nvl(MAXEXTS-EXTENTS,0)<=4 and nvl(MAXEXTS-EXTENTS,0)>=0;

NVL(FILE#,0) NVL(BLOCK#,0) TYPE# TS# MAXEXTS EXTENTS
------------ ------------- ---------- ---------- ---------- ----------
193 120872 5 9 5 1


So for "OR" condition the query gives the result whereas for "AND" its failing.

Thanks,
Pallavi.

[pallaviyd]

Your query output:-

select

nvl(s.FILE#,0),

nvl(s.BLOCK#,0),

s.TYPE#,

s.TS#,

s.MAXEXTS,

s.EXTENTS

from sys.seg$ s,sys.file$ f

where

s.ts# = f.ts# and

s.file# = f.relfile# and

f.file# = 76 and

s.block# = 120872

SQL> start bmcquery4.sql

NVL(S.FILE#,0) NVL(S.BLOCK#,0) TYPE# TS# MAXEXTS EXTENTS
-------------- --------------- ---------- ---------- ---------- ----------
193 120872 5 9 5 1