RE: Datafile HWM without querying dba_extents
- From: Neil Chandler <neil_chandler@xxxxxxxxxxx>
- To: "k3nnyp@xxxxxxxxx" <k3nnyp@xxxxxxxxx>, "jonathan@xxxxxxxxxxxxxxxxxx" <jonathan@xxxxxxxxxxxxxxxxxx>
- Date: Sun, 1 Feb 2015 15:46:31 +0000
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@xxxxxxxxx
Date: Sat, 31 Jan 2015 09:20:38 -0500
CC: dmarc-noreply@xxxxxxxxxxxxx; oracle-l@xxxxxxxxxxxxx
To: jonathan@xxxxxxxxxxxxxxxxxx
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.
Other related posts:
