Re: Datafile HWM without querying dba_extents
- From: Kenny Payton <k3nnyp@xxxxxxxxx>
- To: jonathan@xxxxxxxxxxxxxxxxxx
- Date: Sat, 31 Jan 2015 09:20:38 -0500
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 <http://jonathanlewis.wordpress.com/>
> @jloracle
> From: oracle-l-bounce@xxxxxxxxxxxxx <mailto:oracle-l-bounce@xxxxxxxxxxxxx>
> [oracle-l-bounce@xxxxxxxxxxxxx <mailto:oracle-l-bounce@xxxxxxxxxxxxx>] on
> behalf of Deepak Sharma [dmarc-noreply@xxxxxxxxxxxxx
> <mailto:dmarc-noreply@xxxxxxxxxxxxx>]
> Sent: 31 January 2015 05:57
> To: oracle-l@xxxxxxxxxxxxx <mailto: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.
>
>
Other related posts:
