RE: high water mark query

From: Pakhutkin, Maxim (Max) <maxim.pakhutkin_at_paetec.com>
Date: Fri, 20 May 2005 17:59:43 -0400
Message-ID: <4C4E171C33ADBF43BFFE42978E1708C90E948F@mail1-corp.corp.paetec.com>

Forgot to mention that, for my example, the maximum block_id from = dba_extents for that datafile is less than the maximum block_id from = dba_free_space for that datafile. It's 40969. Which shows to me that = there is no data beyond the last free space chunk that starts at = block_id 41097. And yet the calculation doesn't work for the example = datafile. Again, I would appreciate any help. Thanks.

Max

-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Pakhutkin, Maxim (Max)
Sent: Friday, May 20, 2005 5:40 PM
To: Oracle-L
Subject: high water mark query

I've come up with a query (below) to identify high water mark of =3D datafiles and tempfiles. The problem is, it works most of the time but =
=3D

not _all_ the time for datafiles belonging to "permanent" tablespaces. =
=3D

I've seen it _not_ work on W2K 9.2.0.6 and Solaris 8.1.7.4, so the =3D problem is probably not platform-specific.

Essentially, my approach here is to find the block where the last chunk =
=3D

of free space starts, add the size of that free space, see if (space =3D before the last free space chunk) + (size of last free space chunk) =
=3D3D =3D

(datafile size). If true, i declare that the beginning of the last free =
=3D

space chunk is the HWM. If not I declare that the file cannot be =3D downsized.

What am I missing?

Example:
8k blocks, last free chunk starts at block 41097 and has the size of =3D 81788928, datafile size is 419430400. However, (41097-1)*8192+81788928 =
=3D
=3D3D 418447360 and not 419430400 as expected. Is dba_free_space lying =
to =3D
me by chance?

Here's the query. It's intended for 8i and up.

SELECT tbs.CONTENTS, d.file_name, d.file_id, d.tablespace_name, =3D d.BYTES as DF_SIZE,

         DECODE (tbs.CONTENTS,
                 'TEMPORARY', DECODE (pf.extent_start + pf.BYTES,
                                      d.BYTES, d.BYTES,
                                      pf.extent_start + pf.BYTES
                                     ),
                 DECODE (pf.extent_start + pf.BYTES,
                         d.BYTES, pf.extent_start,
                         d.BYTES
                        )
                ) AS hwm
    FROM dba_tablespaces tbs,
         (SELECT *
            FROM dba_data_files
           WHERE status =3D3D 'AVAILABLE'
          UNION ALL
          SELECT *
            FROM dba_temp_files
           WHERE status =3D3D 'AVAILABLE') d,
         (SELECT fs.tablespace_name, fs.BYTES, fs.file_id,
                 (fs.block_id - 1) * (fs.BYTES / fs.blocks) AS =3D
extent_start
            FROM dba_free_space fs,
                 (SELECT   tablespace_name, file_id,
                           MAX (block_id) AS block_id
                      FROM dba_free_space
                  GROUP BY tablespace_name, file_id) fb
           WHERE fs.tablespace_name =3D3D fb.tablespace_name
             AND fs.file_id =3D3D fb.file_id
             AND fs.block_id =3D3D fb.block_id
          UNION ALL
          SELECT fs.tablespace_name, fs.BYTES, fs.file_id,
                 (fs.block_id - 1) * (fs.BYTES / fs.blocks) AS =3D
extent_start
            FROM v$temp_extent_map fs,
                 (SELECT   tablespace_name, file_id,
                           MAX (block_id) AS block_id
                      FROM v$temp_extent_map
                     WHERE owner !=3D3D 0
                  GROUP BY tablespace_name, file_id) fb
           WHERE fs.tablespace_name =3D3D fb.tablespace_name
             AND fs.owner !=3D3D 0
             AND fs.file_id =3D3D fb.file_id
             AND fs.block_id =3D3D fb.block_id) pf
   WHERE d.file_id =3D3D pf.file_id(+)
     AND d.tablespace_name =3D3D pf.tablespace_name(+)
     AND tbs.tablespace_name =3D3D d.tablespace_name

ORDER BY tbs.CONTENTS, d.file_id

Thanks,

Max

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

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

Received on Fri May 20 2005 - 18:04:25 CDT