Select on dba_extents
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: Select on dba_extents

  1. #1
    Join Date
    Jul 2001
    Posts
    108

    Select on dba_extents

    Hello,

    The following sql statement is running for a long time. Where as all other statements are running as normal.

    Select
    owner,
    segment_name,
    segment_type
    from
    dba_extents
    where
    file_id = &P1
    and
    &P2 between block_id and block_id + blocks -1;

    Is this a normal behaviour whenever we try to pull some information on blocks?

    Thanks,
    Nikee

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    lmt?
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  3. #3
    Join Date
    Jul 2001
    Posts
    108
    What is "lmt" stands for?
    -Nikee

  4. #4
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,027
    Originally posted by Nikee
    What is "lmt" stands for?
    He is asking if you are using locally managed tablespaces.
    this space intentionally left blank

  5. #5
    Join Date
    Oct 2002
    Posts
    807
    Listed on NYSE as Lockheed Martin. Oracle calls'em locally managed tablespaces.

  6. #6
    Join Date
    Jul 2001
    Posts
    108

    Question

    Hello,

    Thanks for the clarification.

    Only the TEMP and UNDO tablespaces are Locally managed tablespaces. All other tablespaces are DICTIONARY managed tablespaces.

    We are running on HP-UX 11.0 64bit OS.
    Oracle 9205 64bit.

    Please let me know if you need more information.

    Thanks,
    Nikee
    -Nikee

  7. #7
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    why oh why are you still using DMT - change them to LMT's

  8. #8
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Originally posted by Nikee

    Only the TEMP and UNDO tablespaces are Locally managed tablespaces. All other tablespaces are DICTIONARY managed tablespaces.
    In that case, I'd look at the query plan.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  9. #9
    Join Date
    Jul 2001
    Posts
    108
    Its an application restriction. Due to this reason we are still running on DMT mode.

    Also I heard that Oracle 92 has a bug related to DBA_SEGMENTS.
    ---------------------------------------------------------
    So what I have done is, I ran the CATSPC2.sql

    New views are created:
    - SYS_DBA_SEGS_2
    - DBA_SEGMENTS_2
    - USER_SEGMENTS_2
    - DBA_EXTENTS_2
    - USER_EXTENTS_2
    ---------------------------------------------------------

    and later on I ran the following sql statements:

    ---------------------------------------------------------
    sqlplus /
    create or replace synonym sys_dba_segs for sys.sys_dba_segs_2;
    create or replace synonym dba_segments for sys.dba_segments_2;
    create or replace synonym user_segments for sys.user_segments_2;
    create or replace synonym dba_extents for sys.dba_extents_2;
    create or replace synonym user_extents for sys.user_extents_2;
    exit;

    ---------------------------------------------------------
    Later on I ran the following sql command to see the results (As an example):

    Examining Segments that CANNOT Allocate Next Extents
    ---------------------------------------------------------
    SELECT
    a.owner as "Owner", a.tablespace_name as "Tspace",
    a.segment_name as "Segment", a.next_extent/1024 "Next(k)",
    a.pct_increase "%inc" FROM sys.dba_segments a
    WHERE
    next_extent > (SELECT MAX(b.bytes)
    FROM
    sys.dba_free_space b
    WHERE
    a.tablespace_name = b.tablespace_name) ;
    ---------------------------------------------------------

    Still I see the same old story. System is still processing the same statement.

    Thanks,
    Nikee
    -Nikee

  10. #10
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    Originally posted by Nikee
    Its an application restriction. Due to this reason we are still running on DMT mode.

    Rubbish, they application is blind to them

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