Return-Path: X-Original-To: oracle-l@orafaq.com Delivered-To: oracle-l@orafaq.com Received: from puck1183.startdedicated.com (localhost [127.0.0.1]) by puck1183.startdedicated.com (Postfix) with ESMTP id 457CB19601F9 for ; Mon, 2 Feb 2015 15:59:23 +0100 (CET) Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by puck1183.startdedicated.com (Postfix) with ESMTP for ; Mon, 2 Feb 2015 15:59:23 +0100 (CET) Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 0D37522A36; Mon, 2 Feb 2015 09:59:22 -0500 (EST) Authentication-Results: turing.freelists.org; dkim=fail (verification failed; insecure key) header.i=@gmail.com; dkim-adsp=none (insecure policy) X-Virus-Scanned: Debian amavisd-new at turing.freelists.org Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing.freelists.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id GrglDWgJA5sf; Mon, 2 Feb 2015 09:59:21 -0500 (EST) Received: from turing.freelists.org (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id E51DF22A13; Mon, 2 Feb 2015 09:59:05 -0500 (EST) Received: with ECARTIS (v1.0.0; list oracle-l); Mon, 02 Feb 2015 09:57:44 -0500 (EST) Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id D31D122591 for ; Mon, 2 Feb 2015 09:57:43 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing.freelists.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id cCv7pINN8Los for ; Mon, 2 Feb 2015 09:57:43 -0500 (EST) Received: from mail-wg0-f46.google.com (mail-wg0-f46.google.com [74.125.82.46]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 6C681218B3 for ; Mon, 2 Feb 2015 09:57:43 -0500 (EST) Received: by mail-wg0-f46.google.com with SMTP id l2so39241938wgh.5 for ; Mon, 02 Feb 2015 06:57:42 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113; h=mime-version:in-reply-to:references:date:message-id:subject:from:to :cc:content-type; bh=rumnxjzFtLJyywsnbmIgLqfatOndO3NUk5t5vSUBnbY=; b=f1knxjDnHJ34hgOsV9OK+Wf0AzZ8kTixCMtPo2I1il41BFNwVvAB0Z5ybg4cQAeeSY 6XdgJGnai9+x6KnYRSwlgtpLfiUu3tDtOCpVDE+PpXdj/PSdAwLtaF1yAShUErB7duUB zbtLKRuYHe9d+84S3/07rg0WT6QlNBm7YtwyIJ0z7/BCaFoES88/Vb05sDwlftCAhjfl JzHoPMawHgkYCzBEsLZ2ySsquvp0ccinpp/69gyhb72V5iWsA/xuRPZ7TXEfFsbCS58J oaDxkZO5/wqGpozhuqCAWwrQLhiMyJGAv3Y4wCTV7fSQZjPXaKSvjnq/39n4Yu1SDMXk 686Q== MIME-Version: 1.0 X-Received: by 10.194.23.39 with SMTP id j7mr45264327wjf.9.1422889062579; Mon, 02 Feb 2015 06:57:42 -0800 (PST) Received: by 10.27.13.194 with HTTP; Mon, 2 Feb 2015 06:57:42 -0800 (PST) In-Reply-To: References: <1603010529.536903.1422817078886.JavaMail.yahoo@mail.yahoo.com> Date: Mon, 2 Feb 2015 08:57:42 -0600 Message-ID: Subject: Re: Datafile HWM without querying dba_extents From: Ethan Post To: Jonathan Lewis Cc: "oracle-l@freelists.org" Content-Type: multipart/alternative; boundary=047d7b3a8bce221538050e1c2d68 X-archive-position: 58428 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-to: oracle-l-bounce@freelists.org X-original-sender: post.ethan@gmail.com Precedence: normal Reply-To: post.ethan@gmail.com List-help: List-unsubscribe: List-software: Ecartis version 1.0.0 List-Id: oracle-l X-List-ID: oracle-l List-subscribe: List-owner: List-post: List-archive: X-list: oracle-l --047d7b3a8bce221538050e1c2d68 Content-Type: text/plain; charset=UTF-8 Going off the top of my head here, I would likely just try to shrink the datafile by "X" GB's at a time until it won't shrink anymore in a loop and move on to the next datafile, until tablespace is X% free. On Sun, Feb 1, 2015 at 6:06 PM, Jonathan Lewis wrote: > > > My oversight - it's a side effect of being able to pick file sizes that > don't have to be exactly aligned with possible extent sizes; one of the > side effects is described here: > http://jonathanlewis.wordpress.com/2012/05/31/lmt-headers > > > The difference in your case is 7 x 8KB blocks - so your tablespace is > either system-allocated extents or uniform with extent size greater than > 64KB. > > You need to tweak the code either to check that the difference between > last block of the file and the derived last of block of the free space is > less than the minimum extent size; or you could do something with > "user_bytes" and knowing the size of the file space header. > > > > Regards > Jonathan Lewis > http://jonathanlewis.wordpress.com > @jloracle > ------------------------------ > *From:* Deepak Sharma [sharmakdeep_oracle@yahoo.com] > *Sent:* 01 February 2015 18:57 > *To:* Jonathan Lewis; oracle-l@freelists.org > > *Subject:* Re: Datafile HWM without querying dba_extents > > *I tried this in our Dev DB for 2 files (50 and 51). I was able to > resize both of these by 1 byte to begin with, which means that there is > Free Space at the end of the file.* > > > *The problem I see is in the calculation to determine whether the Free > Space chunk for Max(block_id) was indeed at the end of the file (by > comparing the extent size with total file size). * > > *What am I missing? It could be that the extent size calculation for > these 2 files are different. I know that they are in 2 separate tabespaces > using LMT as well as ASSM.* > > ============= > FILE:50 > ============= > SQL> select bytes from dba_data_files where file_id=50; > > BYTES > ---------- > 53477376 > > 1 row selected. > > SQL> alter database datafile 50 resize 53477375; > > Database altered. > > -- Resize it back to its original size > SQL> alter database datafile 50 resize 53477376; > > Database altered. > > SQL> select file_id, block_id, bytes, blocks from dba_free_space where > file_id=50 > 2 and block_id = (select max(block_id) from dba_free_space where > file_id=50); > > FILE_ID BLOCK_ID BYTES BLOCKS > ---------- ---------- ---------- ---------- > 50 6409 983040 120 > > 1 row selected. > > SQL> select 6408*8192 + 120*8192 from dual; > > 6408*8192+120*8192 > ------------------ > 53477376 * <=== MATCHES the FILE_SIZE* > > 1 row selected. > > ============= > FILE:51 > ============= > SQL> select bytes from dba_data_files where file_id=51; > > BYTES > ---------- > 840294400 > > 1 row selected. > > SQL> alter database datafile 51 resize 840294399; > > Database altered. > > -- Resize it back to its original size > SQL> alter database datafile 51 resize 840294400; > > Database altered. > > SQL> select file_id, block_id, bytes, blocks from dba_free_space where > file_id=51 > 2 and block_id = (select max(block_id) from dba_free_space where > file_id=51); > > FILE_ID BLOCK_ID BYTES BLOCKS > ---------- ---------- ---------- ---------- > 51 75625 220725248 26944 > > 1 row selected. > SQL> select 75624*8192 + 26944*8192 from dual; > > 75624*8192+26944*8192 > --------------------- > 840237056* <=== DOES NOT MATCH the FILE_SIZE* > > 1 row selected. > > > On Saturday, January 31, 2015 11:55 PM, Jonathan Lewis < > jonathan@jlcomp.demon.co.uk> wrote: > > > > Your case 2 comment is correct - but if you do a resize datafile aimed at > the highest starting block and it fails you know that you can't shrink the > file. Alternatively, if you check the start block and block count and find > that that doesn't take you to the end of file then you know that you can't > resize the file downwards. > > > > > Regards > Jonathan Lewis > http://jonathanlewis.wordpress.com > @jloracle > ------------------------------ > *From:* Deepak Sharma [sharmakdeep_oracle@yahoo.com] > *Sent:* 31 January 2015 21:21 > *To:* Jonathan Lewis; dmarc-noreply@freelists.org; oracle-l@freelists.org > *Subject:* Re: Datafile HWM without querying dba_extents > > Thanks for sharing your thoughts, esp. getting the TS dump (will give > it a try). > > As for the dba_free_space, I have 2 datafile cases as below (T- Used, x > - Empty) : > > Case1: > > 0123456789*0*123456789 > xxTTxxxxTTxxxxxxxxxx > > Case2: > > 0123*4*567890123456789 > xxTTxxxxxxxxxxxxxxTT > > In Case1, the contiguous free space for max block_id (per > dba_free_space) would begin at BlkId 10 > > In Case2, the max blockid for free chunk would begin at BlkId 5, but we > really cannot shrink that Datafile, since the last blocks are already used. > > So, the MAX(block_id) for a given File_Id, in dba_free_space may not > necessarily point to the free blocks at the 'End' of a datafile. > > Regards, > Deepak > > > On Saturday, January 31, 2015 4:58 AM, Jonathan Lewis < > jonathan@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. > > > > > > Regards > Jonathan Lewis > http://jonathanlewis.wordpress.com > @jloracle > ------------------------------ > *From:* oracle-l-bounce@freelists.org [oracle-l-bounce@freelists.org] on > behalf of Deepak Sharma [dmarc-noreply@freelists.org] > *Sent:* 31 January 2015 05:57 > *To:* oracle-l@freelists.org > *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. > > > > > > --047d7b3a8bce221538050e1c2d68 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable
Going off the top of my head here, I would likel= y just try to shrink the datafile by "X" GB's at a time until= it won't shrink anymore in a loop and move on to the next datafile, un= til tablespace is X% free.
=
On Sun, Feb 1, 2015 at 6:06 PM, Jonathan Lew= is <jonathan@jlcomp.demon.co.uk> wrote:


My oversight - it's a side effect of being able to pick file sizes that= don't have to be exactly aligned with possible extent sizes; =C2=A0one= of the side effects is described here: =C2=A0http://jonatha= nlewis.wordpress.com/2012/05/31/lmt-headers

The difference in your case is 7 x 8KB blocks - so your tablespace is = either system-allocated extents or uniform with extent size greater than 64= KB.

You need to tweak the code either to check that the difference between= last block of the file and the derived last of block of the free space is = less than the minimum extent size; or you could do something with "use= r_bytes" and knowing the size of the file space header.


=C2=A0=C2=A0
Regards
Jonathan Lewis
http://jon= athanlewis.wordpress.com
@jloracle
Fro= m: Deepak Sharma [sharmakdeep_oracle@yahoo.com]
Sent: 01 February 2015 18:57
To: Jonathan Lewis; oracle-l@freelists.org

Subject: Re: Datafile HWM without querying dba_extents

I tried this in our Dev DB for = 2 files (50 and 51). I was able to resize both of these by 1 byte to begin with, which means that there is Free Space at the end of the file.<= /b>

The problem I see is in the cal= culation to determine whether the Free Space chunk for Max(block_id) was indeed at the end of the file (by comparing the extent size with total fil= e size).

What am I missing? It could be = that the extent size calculation for these 2 files are different. I know that they are in 2 separate tabespaces using LMT as well as ASSM.

=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
FILE:50
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
SQL> select bytes from dba_data_files where file_id=3D50;

=C2=A0=C2=A0=C2=A0=C2=A0 BYTES
----------
=C2=A0 53477376

1 row selected.

SQL> alter database datafile 50 resize 53477375;

Database altered.

-- Resize it back to its original size
SQL> alter database datafile 50 resize 53477376;

Database altered.

SQL> select file_id, block_id, bytes, blocks from dba_free_space where f= ile_id=3D50
=C2=A0 2=C2=A0 and block_id =3D (select max(block_id) from dba_free_space w= here file_id=3D50);

=C2=A0=C2=A0 FILE_ID=C2=A0=C2=A0 BLOCK_ID=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 BYT= ES=C2=A0=C2=A0=C2=A0=C2=A0 BLOCKS
---------- ---------- ---------- ----------
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 50=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0 6409=C2=A0=C2=A0=C2=A0=C2=A0 983040=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0 120

1 row selected.

SQL> select 6408*8192 + 120*8192 from dual;

6408*8192+120*8192
------------------
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 534= 77376=C2=A0=C2=A0=C2=A0=C2=A0 <=3D=3D=3D MATCHES the FILE_SIZE
1 row selected.

=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
FILE:51
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
SQL> select bytes from dba_data_files where file_id=3D51;

=C2=A0=C2=A0=C2=A0=C2=A0 BYTES
----------
=C2=A0840294400

1 row selected.

SQL> alter database datafile 51 resize 840294399;

Database altered.

-- Resize it back to its original size
SQL> alter database datafile 51 resize 840294400;

Database altered.

SQL> select file_id, block_id, bytes, blocks from dba_free_space where f= ile_id=3D51
=C2=A0 2=C2=A0 and block_id =3D (select max(block_id) from dba_free_space w= here file_id=3D51);

=C2=A0=C2=A0 FILE_ID=C2=A0=C2=A0 BLOCK_ID=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 BYT= ES=C2=A0=C2=A0=C2=A0=C2=A0 BLOCKS
---------- ---------- ---------- ----------
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 51=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= 75625=C2=A0 220725248=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 26944

1 row selected.
SQL> select 75624*8192 + 26944*8192 from dual;

75624*8192+26944*8192
---------------------
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 84023705= 6=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 <= =3D=3D=3D DOES NOT MATCH the FILE_SIZE

1 row selected.


On Saturday, January 31, 2015 11:55 P= M, Jonathan Lewis <jonathan@jlcomp.demon.co.uk> wrote:



Your case 2 comment is correct - but if you do a resize datafile aimed at t= he highest starting block and it fails you know that you can't shrink t= he file.=C2=A0 Alternatively, if you check the start block and block count = and find that that doesn't take you to the end of file then you know that you can't resize the file downwards.


=C2=A0=C2=A0
Regards
Jonathan Lewis
http://jon= athanlewis.wordpress.com
@jloracle
Fro= m: Deepak Sharma [sharmakdeep_oracle@yahoo.com]
Sent: 31 January 2015 21:21
To: Jonathan Lewis; dmarc-noreply@freelists.org; oracle-l@freelists.org
Subject: Re: Datafile HWM without querying dba_extents

Thanks for sharing your thoughts, esp.=C2=A0 getting the T= S dump (will give it a try).

As for the dba_free_space, I have 2 datafile cases as belo= w (T- Used, x - Empty) :

Case1:

01234567= 890123456789
xxTTxxxxTTxxxxxxxxxx

Case2:

01234567890123456789
xxTTxxxxxxxxxxxxxxTT

In Case1, the contiguous free space for max block_id (per = dba_free_space) would begin at BlkId 10

In Case2, the max blockid for free chunk would begin at Bl= kId 5, but we really cannot shrink that Datafile, since the last blocks are= already used.

So, the MAX(block_id) for a given File_Id, in dba_free_spa= ce may not necessarily point to the free blocks at the 'End' of a d= atafile.

Regards,
Deepak


On Saturday, January 31, 2015 4:58 AM= , Jonathan Lewis <jonathan@jlcomp.demon.co.uk> wrote:




On second thoughts, why are you querying dba_extents to find where last use= d block id is ?=C2=A0 If all you want to do is shrink the datafile then que= rying user_free_space (ordered by file id and block id) will allow you to f= ind 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 o= f space lower down the file and think that moving a couple of small objects= might be sufficient to clear the way to releasing it.




=C2=A0=C2=A0
Regards
Jonathan Lewis
http://jon= athanlewis.wordpress.com
@jloracle
Fro= m: o= racle-l-bounce@freelists.org [oracle-l-bounce@freelists.org] on behalf of D= eepak Sharma [dmarc-noreply@freelists.org]
Sent: 31 January 2015 05:57
To: orac= le-l@freelists.org
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 contiguou= s space.

Problem is that we have a very large database such that querying dba_e= xtents 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=C2=A0 sometimes fails with a 'snapshot too old' (just giv= es up).

Is there an alternative to using dba_extents?

For example, if the datafile size is 100mb and the last 10= mb is vacant, I want to know the block_id of where that 10mb begins.






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