Definitely a great approach here, and well worth remembering. Sometimes you *will* need to query views like DBA_EXTENTS in databases with very large numbers of objects/segments/extents, and these queries can definitely take a very long time to run. When the database belongs to a customer, gathering stats might not even be an option. :-( In cases like this (all one or two that I can recall), I have achieved good results by unwinding the DBA_ views and removing the fluff I don't need. There's a good chance you can probably eliminate a few joins, and on occasion (e.g., a database using only LMTs, so you don't care about information for DMTs) you might even eliminate some UNIONS. On one occasion, I was able to take a daily health-check query that ran for 36 hours in a particularly degenerate case, and get the information I *really* needed in less than 2 minutes. In this case, though, turning the problem on its head and finding a completely different (and much better!) source for the information is much superior. On Sat, Jan 31, 2015 at 9:20 AM, Kenny Payton <k3nnyp@xxxxxxxxx> wrote: > > Thanks Jonathan, this is too simple, I can’t wait to rewrite my > resize_tablespace.sql script. It’s funny how you head down a road and > never look back. I’ve been using a script that joins dba_extents, > db_data_files and dba_tablespaces for some time. It works great but time > consuming on large tablespaces with many segments/extents. This approach > turns the job upside down and I suspect will be much faster to derive the > same result. > > Kenny > > > > > > > On Jan 31, 2015, at 5:56 AM, Jonathan Lewis <jonathan@xxxxxxxxxxxxxxxxxx> > wrote: > > > > On second thoughts, why are you querying dba_extents to find where last > used block id is ? If all you want to do is shrink the datafile then > querying user_free_space (ordered by file id and block id) will allow you > to find the starting block of the highest free area in file. > > You only need to query dba_extents if you think you've got a lot of space > lower down the file and think that moving a couple of small objects might > be sufficient to clear the way to releasing it. > > > > > > Regards > Jonathan Lewis > http://jonathanlewis.wordpress.com > @jloracle > ------------------------------ > *From:* oracle-l-bounce@xxxxxxxxxxxxx [oracle-l-bounce@xxxxxxxxxxxxx] on > behalf of Deepak Sharma [dmarc-noreply@xxxxxxxxxxxxx] > *Sent:* 31 January 2015 05:57 > *To:* oracle-l@xxxxxxxxxxxxx > *Subject:* Datafile HWM without querying dba_extents > > In order to resize a datafile to release space at the end, we need to find > whatever the last block_id that is at the start of that free contiguous > space. > > Problem is that we have a very large database such that querying > dba_extents to find the last block is probably not an option. The standard > query(ies) that make use of dba_extents runs for hours at stretch and also > sometimes fails with a 'snapshot too old' (just gives up). > > Is there an alternative to using dba_extents? > > For example, if the datafile size is 100mb and the last 10mb is vacant, I > want to know the block_id of where that 10mb begins. > > > >