Re: How do you determine free space in DB?

Ari Kaplan wrote:
>
> akaplan_at_psycfrnd.interaccess.com (Ari Kaplan) writes:
> #2 should have read:
>
> 2. In Oracle 7.3, you can use the "dbms_space.unused_space" procedure
> to determine the used and unused space within a table.
>
> For your case (Oracle 7.1.4), you can see how many blocks are being
> USED by
> the table:
>
> select count(distinct substr(rowid,15,4)) from TABLE_NAME;
>
> ... SNIP ....
>
> >2. In Oracle 7.3, you can use the "dbms_space.unused_space" procedure
> > to determine the used and unused space within a table.
 

> > For your case (Oracle 7.1.4), you can see how many blocks are being USED by
> > the table:
 

> > select count(distinct substr(rowid,1,8)) from TABLE_NAME;
>
> .... SNIP .....
Actualy, both versions of your SQL statements are incorrect. The first (upper) one counts the number of data files in witch table TABLE_NAME has its extents.

The seccond (lower) one counts distinct block identifiers of this table blocks. Since block identifiers are relative to their data file, there could be two different blocks of the same table having the same id but residing in two different files.

The correct query to determine number of blocks, used by a table is:

SELECT COUNT(DISTINCT SUBSTR(rowid,1,8) || SUBSTR(rowid,15,4)) FROM table_name;

Regards,

-- 
 ===============================================================
 ! Jurij Modic                            Republic of Slovenia !
 !  tel: +386 61 178 55 14                Ministry of Finance  !
 !  fax: +386 61  21 45 84                Zupanciceva 3        !
 !  e-mail: jurij.modic_at_mf.sigov.mail.si  Ljubljana 1000       !
 ===============================================================