Query for used blocks in a table

From: Lars Erik Petersen <lap_at_orkfin.no>
Date: Wed, 29 Sep 2004 13:25:52 GMT
Message-ID: <AJy6d.258$ZG9.170427392@news.telia.no>

Hi!

I'm running the following query to list the tables in question:

SELECT a.owner, a.segment_name
FROM dba_segments a, dba_tables b
WHERE a.owner ='SCDAT'

AND a.segment_type = 'TABLE'
AND a.segment_name = b.table_name
AND a.owner = b.owner

GROUP BY a.owner, a.segment_name

For each of the tables returned by the query, I want to do another query:

SELECT COUNT(DISTINCT(SUBSTR(ROWID,1,8) || SUBSTR(ROWID,15,4))) FROM {table}

(where {table} is the table name as returned by the query above)

I doubt I can do this in a single query, but could someone please give a hint as to how I could do it in a PL/SQL script?

What I originally wanted was to use this query in OEM and create a report
(report definitions) to see only used blocks in an table, and not allocated.
Is this possible?

Regards,
Lars Erik Petersen Received on Wed Sep 29 2004 - 08:25:52 CDT