How to query a 'compressed tablespace (datafile) map'.

From: Raymond <r_h_at_hetnet.nl>
Date: Mon, 5 Nov 2001 21:24:02 +0100
Message-ID: <9s6s3d$7v6$1@news1.xs4all.nl>

Hi there,

The production systems are working without trouble, so I had some time for a small puzzle...
(I am running Oracle V8.1.7.2.0 on Tru64).

This is a well-known query to display all used and free portions of the datafiles of a tablespace:

    select file_id, block_id, blocks, 'U' uf from dba_extents     where tablespace_name = 'TOOLS'
    union all
    select file_id, block_id, blocks, 'F' from dba_free_space     where tablespace_name = 'TOOLS'
    order by file_id, block_id

The result is something like this:

file_id block_id blocks uf (used/free) 4 2 5 U
4 7 5 U
4 12 5 F

Where the 'U' stands for 'Used' and the 'F' stands for 'Free'. Very nice indeed, except that
the result could be compressed so that adjacent pieces of used or free space are summed and
printed as one row only. So like this:

4 2 10 U
4 12 5 F

How can this be done? I build the following query that returns all the rows with an added
'Y' or 'N'. The 'Y' is returned when the chunk of used/free blocks has the
same characteristics
as the previous chunk (the file_id is the same, it is free/used as the previous row and the
startblock of the previous chunk plus it's size is identical to the start block of the current chunk).

select b.file_id,
       b.block_id,
       b.blocks,
       b.uf,
       decode(b.uf, b.prev_uf, decode(b.file_id, b.prev_file_id,
         decode(b.prev_block_id + b.prev_blocks , b.block_id, 'Y' , 'N'),

'N'), 'N') connected

from
 (select a.file_id, a.block_id, a.blocks, a.uf,

         lag(a.file_id)  over (order by a.file_id, a.block_id) prev_file_id,
         lag(a.block_id) over (order by a.file_id, a.block_id)
prev_block_id,
         lag(a.blocks)   over (order by a.file_id, a.block_id) prev_blocks,
         lag(a.uf)       over (order by a.file_id, a.block_id) prev_uf

  from
    (
    select file_id, block_id, blocks, 'U' uf from dba_extents     where tablespace_name = 'TOOLS'
    union all
    select file_id, block_id, blocks, 'F' from dba_free_space     where tablespace_name = 'TOOLS'
    ) a
  ) b
order by 1,2

This query returns something like this:

4 2 5 U N
4 7 5 U Y
4 12 5 F N

indicating that the second row could be compressed in the output with the first row, as shown above.

Question: (how) can this be done??
I hope there isn't a much simpler solution to this puzzle (like a dictionary view
that holds the info I am looking for :) )

Raymond. Received on Mon Nov 05 2001 - 14:24:02 CST