RE: Varied block density for fixed length row tables

select dbms_rowid.relative_fno(rowid), dbms_rowid.rowid_block_number(rowid), count(*) from <table_name>
group by dbms_rowid.relative_fno(rowid), dbms_rowid.rowid_block_number(rowid) order by 1,2;

-Mark
-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Rich Jesse Sent: Wednesday, September 14, 2011 1:42 PM To: oracle-l_at_freelists.org
Subject: Re: Varied block density for fixed length row tables

Hi David,

> Why are you not using the DBMS_ROWID functions?
>  
> select dbms_rowid.rowid_block_number(rowid), count(*) from
> <table_name> group by dbms_rowid.rowid_block_number(rowid)
> order by 1;

I have it stuck in my head that the above returns the block number relative to the datafile, so I would get duplicates with multiple datafiles per tablespace. I have run that (before I even posted), and the empirical evidence seems to back that up.

However, the documentation says it returns "database block number", but I'm not exactly sure what that means. This might call for more research!

Thanks!

Rich

--
http://www.freelists.org/webpage/oracle-l




--
http://www.freelists.org/webpage/oracle-l