missing entry in sys.seg$ view
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: missing entry in sys.seg$ view

  1. #1
    Join Date
    Jun 2006
    Posts
    7

    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

  2. #2
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    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 07: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"

  3. #3
    Join Date
    Jun 2006
    Posts
    7
    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.

  4. #4
    Join Date
    Jun 2006
    Posts
    7

    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
  •  


Click Here to Expand Forum to Full Width