Re: num of blocks containing table data

What is with the procedure dbms_space.free_blocks. There's an in parameter freelist_group_id and an out parameter free_blks which returns the number of free blocks per freelist group (use 0 by single server). I didn't tested it exactly. But probably the return is the number of blocks per freelist group. That depends on the value of pctused. And then, you only have an upper and a lower bound for the unused blocks in the table. After all, you can calculate the number of used blocks, not exactly, but exactly enough.

Hope this will help.
Andreas Prusch.

In article <357e4901.6622064_at_www.sigov.si>,   jurij.modic_at_mf.sigov.mail.si (Jurij Modic) wrote:
>
> On Tue, 09 Jun 1998 20:43:33 GMT, tkyte_at_us.oracle.com (Thomas Kyte)
> wrote:
>
> >This might be too picky but...
> >
> >The problem with inspecting the rowid as above is that is probably misses
blocks
> >with chained rows (if you have longs or your rows > blocksize, you definitely
> >will have chained rows) and migrated rows. Since a row can be on more then
one
> >block, the above method will only count blocks that have an initial row
piece on
> >them -- not all blocks that contain data...
>
> You are absolutely right (as usual) about the ROWID method missing the
> chained rows extra blocks. I completely forgot the chained rows.
>
> >I'm pretty sure the only way to get the number of blocks actually containing
> >data is to analyze the table and look at BLOCKS and EMPTY_BLOCKS in the
> >user_tables (or all_tables or dba_tables) views.
>
> Hm, I'm prety sure you are wrong about this one. BLOCKS column in
> USER/ALL/DBA_TABLES contains the information of the maximum number of
> blocks that were *ever* occupied in the particular table, i.e.
> highwatter mark. The same principle applies to the column
> EMPTY_BLOCKS. I checked this with various releases from 7.2.3 to
> 7.3.4, I don't know if the situation is changed in release 8.
>
> Here is an example:
>
> SQL> ANALYZE TABLE test COMPUTE STATISTICS;
>
> Table analyzed.
>
> SQL> SELECT num_rows, blocks, empty_blocks, chain_cnt
> 2 FROM user_tables WHERE table_name = 'TEST';
>
> NUM_ROWS BLOCKS EMPTY_BLOCKS CHAIN_CNT
> --------- --------- ------------ ---------
> 5000 148 46 0
>
> SQL> SELECT COUNT(DISTINCT SUBSTR(rowid,1,8)||SUBSTR(rowid,15,4))
> blocks_occupied
> 2 FROM test;
>
> BLOCKS_OCCUPIED
> ---------------
> 148
>
> SQL> DELETE FROM test WHERE rownum <= 2500;
>
> 2500 rows deleted.
>
> SQL> ANALYZE TABLE test COMPUTE STATISTICS;
>
> Table analyzed.
>
> SQL> SELECT num_rows, blocks, empty_blocks, chain_cnt
> 2 FROM user_tables WHERE table_name = 'TEST';
>
> NUM_ROWS BLOCKS EMPTY_BLOCKS CHAIN_CNT
> --------- --------- ------------ ---------
> 2500 148 46 0
>
> SQL> SELECT COUNT(DISTINCT SUBSTR(rowid,1,8)||SUBSTR(rowid,15,4))
> blocks_occupied
> 2 FROM test;
>
> BLOCKS_OCCUPIED
> ---------------
> 75
>
> Also the DBMS_SPACE.UNUSED_SPACE procedure (as sugested by Jonathan
> Lewis) doesn't help here, because its out parameter TOTAL_BLOCKS
> returns the number of blocks *allocated* for the segment, not the
> number of blocks containing data. The parameter EMPTY_BLOCKS returns
> the same information as USER_TABLES.EMPTY_BLOCKS.
>
> So my question is: does anyone know of a method to determine the
> number of table blocks that actualy contain data, taking into acount
> also the chained and migrated rows?
>
> >
> >Thomas Kyte
> >tkyte_at_us.oracle.com
> >Oracle Government
> >Herndon VA
>
> Regards,
> ============================================================
> Jurij Modic Republic of Slovenia
> jurij.modic_at_mf.sigov.mail.si Ministry of Finance
> ============================================================
> The above opinions are mine and do not represent any official
> standpoints of my employer
>

-----== Posted via Deja News, The Leader in Internet Discussion ==----- http://www.dejanews.com/ Now offering spam-free web-based newsreading Received on Fri Jun 12 1998 - 03:40:16 CDT