Re: Query for used blocks in a table

There is a supplied procedure to get free blocks on a segment:

DBMS_SPACE.FREE_BLOCKS (

              segment_owner IN VARCHAR2,
              segment_name IN VARCHAR2,
              segment_type IN VARCHAR2,
              freelist_group_id IN NUMBER,
              free_blks OUT NUMBER,
              scan_limit IN NUMBER DEFAULT NULL,
              partition_name IN VARCHAR2 DEFAULT NULL);

Then it is easy to calculate used blocks on that segment.

Jan

"Lars Erik Petersen" <lap_at_orkfin.no> wrote in message news:<AJy6d.258$ZG9.170427392_at_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 Thu Sep 30 2004 - 01:43:34 CDT