Oracle Scratchpad

June 2, 2009

Fixed Stats

Filed under: Execution plans,Performance,Statistics,Troubleshooting,Tuning — Jonathan Lewis @ 7:19 pm BST 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 BST 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 BST 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 BST 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 BST 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 BST 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 BST Jun 3,2009 | Reply

RSS feed for comments on this post. TrackBack URI

Comments and related questions are welcome.

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

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

Google+ photo

You are commenting using your Google+ 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 )


Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Powered by