RE: Datafile HWM without querying dba_extents
- From: Jonathan Lewis <jonathan@xxxxxxxxxxxxxxxxxx>
- To: "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
- Date: Sat, 31 Jan 2015 10:41:09 +0000
If you're running a suitable query the time is more likely to be dependent on
the number of objects in the tablespace rather than the size of the database.
Have you checked the execution plan to see where the time is spent ? Have you
collected any stats on the relevant X$ or is Oracle guessing a wildly silly
number ?
If you want to bypass any reference to dba_extents you can call:
execute dbms_space_admin.tablespace_dump_bitmaps('{tablespace name}')
This will dump the space management bitmap(s) for the tablespace.
This will give you some information about the tablespace - including the extent
size in blocks.
It won't tell you where the HWM is, but if you check where the last non-zero
entry appears in the bitmap you can work out where the last used extent is.
Obviously you get multiple entries if you have multiple files; but you also get
multiple entries if a file has grown so large that a secondary bitmap has been
created.
Here's a tablespace with one small file; 1MB locally managed (Unit: 128)
Header Control:
RelFno: 5, Unit: 128, Size: 294400, Flag: 1
AutoExtend: NO, Increment: 0, MaxSize: 0
Initial Area: 126, Tail: 294399, First: 8, Free: 2283
Deallocation scn: 148317558.2950
Header Opcode:
Save: No Pending Op
File Space Bitmap Block:
BitMap Control:
RelFno: 5, BeginBlock: 128, Flag: 0, First: 8, Free: 63472
FF00FF0000000000 0000000000000000 0000000000000000 0000000000000000
0000000000000000 0000000000000000 0000000000000000 0000000000000000
The bitmap shows 8 used extents, followed by 8 free extents, followed by 8 used
extents.
(each set bit is a used extent, FF = 1111 1111)
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:58
To: oracle-l@xxxxxxxxxxxxx
Subject: Re: Datafile HWM without querying dba_extents
I should have added that it's an 11g DB and we're using LMT tablespaces.
On Friday, January 30, 2015 11:57 PM, Deepak Sharma
<sharmakdeep_oracle@xxxxxxxxx> wrote:
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:
