high water mark query

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

I've come up with a query (below) to identify high water mark of = datafiles and tempfiles. The problem is, it works most of the time but = not _all_ the time for datafiles belonging to "permanent" tablespaces. = I've seen it _not_ work on W2K 9.2.0.6 and Solaris 8.1.7.4, so the = problem is probably not platform-specific.

Essentially, my approach here is to find the block where the last chunk = of free space starts, add the size of that free space, see if (space = before the last free space chunk) + (size of last free space chunk) =3D = (datafile size). If true, i declare that the beginning of the last free = space chunk is the HWM. If not I declare that the file cannot be = downsized.

What am I missing?

Example:
8k blocks, last free chunk starts at block 41097 and has the size of = 81788928, datafile size is 419430400. However, (41097-1)*8192+81788928 = =3D 418447360 and not 419430400 as expected. Is dba_free_space lying to = 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, = 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 =3D 'AVAILABLE'
          UNION ALL
          SELECT *
            FROM dba_temp_files
           WHERE status =3D 'AVAILABLE') d,
         (SELECT fs.tablespace_name, fs.BYTES, fs.file_id,


                 (fs.block_id - 1) * (fs.BYTES / fs.blocks) AS =

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 =3D fb.tablespace_name
             AND fs.file_id =3D fb.file_id
             AND fs.block_id =3D fb.block_id
          UNION ALL
          SELECT fs.tablespace_name, fs.BYTES, fs.file_id,


                 (fs.block_id - 1) * (fs.BYTES / fs.blocks) AS =

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 !=3D 0
                  GROUP BY tablespace_name, file_id) fb
           WHERE fs.tablespace_name =3D fb.tablespace_name
             AND fs.owner !=3D 0
             AND fs.file_id =3D fb.file_id
             AND fs.block_id =3D fb.block_id) pf
   WHERE d.file_id =3D pf.file_id(+)
     AND d.tablespace_name =3D pf.tablespace_name(+)
     AND tbs.tablespace_name =3D d.tablespace_name

ORDER BY tbs.CONTENTS, d.file_id

Thanks,

Max

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

Received on Fri May 20 2005 - 17:44:57 CDT