Oracle Scratchpad

June 2, 2009

Fixed Stats

Filed under: Execution plans,Performance,Statistics,Troubleshooting,Tuning — Jonathan Lewis @ 7:19 pm GMT Jun 2,2009

There was a question about the notorious dba_extents view on the newsgroup a little while ago.

This is a view that has been popularly abused over the years despite warnings such as a note of mine in 2006 and even back as far as 2001 this one from Connor McDonald.

There are many reasons why this is a nasty view – and the number of reasons has grown as Oracle has evolved – but David FitzJarell has highlighted an important point in one of his blog postings that was worth a mention, so here it is.


  1. Hi,
    Here is a view that is similar to dba_extents and dba_free_space, but optimized for an access by file_id and block_id :
    You can also see it in Metalink Note Id: 379408.1 – but poorly formatted

    Comment by Franck Pachot — June 3, 2009 @ 8:37 am GMT Jun 3,2009 | Reply

  2. Hi,

    I started the thread but it was not a question but just something I wanted to share.
    Sometimes you have to use dba_extents unless you want to dig in the x$-tables.
    A couple of weeks I was called in to help recover as much as possible from a large database with corruptions and from which a number of archives where missing.
    To be able to determine what segments where involved we needed dba_extents.

    Regards Hans-Peter

    Comment by Hans-Peter Sloot — June 3, 2009 @ 12:09 pm GMT Jun 3,2009 | Reply

    • Hans-Peter,
      You’re right, of course, it didn’t start as a question – I had just jotted a few details, URLs etc. into a draft note, and then wrote it up some days later without checking back the exact context.

      Comment by Jonathan Lewis — June 3, 2009 @ 6:49 pm GMT Jun 3,2009 | Reply

  3. @Franck: for dba_free_space there was already a good alternative:

    regards Hans-Peter

    Comment by Hans-Peter Sloot — June 3, 2009 @ 12:18 pm GMT Jun 3,2009 | Reply

    • Hans-Peter,
      One of the things that I’m really proud of with that whole FAQ is that every page has a date and version number on it. And in that particular conrtibution you’ve even got the explicit warning that: “Of course, as versions of Oracle change, you may have to keep updating the definitions of your views to cater for new features.” The approach, and the comment, should appear much more frequently all over Oracle community.

      Comment by Jonathan Lewis — June 3, 2009 @ 6:52 pm GMT Jun 3,2009 | Reply

  4. […] Oracle Tips by David Fitzjarrell Filed under: Blogroll — coskan @ 9:58 pm I just discovered the Oracle Tips blog of David Fitzjarrell, by the help of this post from Jonathan Lewis . […]

    Pingback by Oracle Tips by David Fitzjarrell « Coskan’s Approach to Oracle — June 3, 2009 @ 9:59 pm GMT Jun 3,2009 | Reply

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Powered by