Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: how to query table size from rowid?
Dan Bikle wrote:
>
> Do any of you have a query which tells me the true size of a
> table from rowid?
>
> I'm looking for something like:
>
> SELECT SOME_FUNCTION(ROWID) FROM EMP;
>
> I know if I "analyze" the table,
> I can get the true size from user_tables.
>
> a demo:
> oooooooooooooooooooooooooooooooo
> SQL> analyze table WWOPS_UNSCH compute statistics;
>
> Table analyzed.
>
> SQL> select NUM_ROWS,BLOCKS,EMPTY_BLOCKS,AVG_ROW_LEN from user_tables
> SQL> where TABLE_NAME='WWOPS_UNSCH';
>
> NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_ROW_LEN
> ---------- ---------- ------------ -----------
> 46 1 3 47
>
> SQL>
> oooooooooooooooooooooooooooooooo
>
> -Dan
> ---------------------------------------------------------------------------
> Daniel B. Bikle/Independent Oracle Consultant
> dbikle_at_alumni.caltech.edu | 415/941-6276 | P.O. BOX 1401 LOS ALTOS CA 94023
> http://www.rahul.net/dbikle
> ---------------------------------------------------------------------------
Try this:
SELECT COUNT(DISTINCT(SUBSTR(ROWID,1,8)||
SUBSTR(ROWID,15,4))) BLOCKS_USED
FROM &H_TABLE_NAME;
-- ************************* Jay Hostetter D. and E. Communications Ephrata, Pa. U.S.A *************************Received on Mon Apr 28 1997 - 00:00:00 CDT