Re: Query for used blocks in a table

• Post for FREE via your newsreader at post.usenet.com ****

Lars Erik Petersen wrote:

> 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

Lars, this is not a direct answer to your question but the number of used blocks is available in the dba_tables view. It is updated by any type of analyze including an estimate to the exact number or by dbms_stats. You can also use the dbms_space package to get block space usage information.

HTH -- Mark D Powell --

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=

• Usenet.com - The #1 Usenet Newsgroup Service on The Planet! *** http://www.usenet.com Unlimited Download - 19 Seperate Servers - 90,000 groups - Uncensored -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=