RE: Datafile HWM without querying dba_extents

From: Neil Chandler <neil_chandler_at_hotmail.com>
Date: Sun, 1 Feb 2015 15:46:31 +0000
Message-ID: <DUB113-W207021CF9A5FC14C602E5853F0_at_phx.gbl>

Occam's Razor:
Much of the Oracle data dictionary design is rather old, and I have had some notable success in the past querying an unruly dictionary by using the equally old RULE hint. Forget the fact it's de-supported from 11G - give it a go. It is still being actively used by Oracle for internal queries in 12.1.0.2.0, so what do they know :-) You just may be pleasantly surprised.
Neil C.

Subject: Re: Datafile HWM without querying dba_extents From: k3nnyp_at_gmail.com
Date: Sat, 31 Jan 2015 09:20:38 -0500
CC: dmarc-noreply_at_freelists.org; oracle-l_at_freelists.org To: jonathan_at_jlcomp.demon.co.uk

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_at_jlcomp.demon.co.uk> 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.                                                

--
http://www.freelists.org/webpage/oracle-l

Received on Sun Feb 01 2015 - 16:46:31 CET