high water mark query

From: "Pakhutkin, Maxim (Max)" <maxim.pakhutkin@xxxxxxxxxx>
To: "Oracle-L" <oracle-l@xxxxxxxxxxxxx>
Date: Fri, 20 May 2005 17:40:15 -0400
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
--
//www.freelists.org/webpage/oracle-l
high water mark query

Other related posts:
