-
missing entry in sys.seg$ view
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
-
Code:
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.
Last edited by abhaysk; 06-13-2006 at 06:47 AM.
funky...
"I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."
"Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"
-
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.
-
output
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
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
|