Originally posted by jmodic
Ok, let's see:
So what we have now is a table with 5 rows in it, each row holding 5 columns, each of them filled with 4000 bytes. So each row should occupy about 20 Kbytes, all of them together abot 100 Kbytes. And what does your query return?
Code:
SQL> SELECT 
  2    COUNT(DISTINCT SUBSTR(ROWID, 1, 15))*8196/1024 
  3    AS "Table Size in KBytes" 
  4  FROM test1;

Table Size in KBytes
--------------------
          40,0195313

SQL>
Ok you got so many KBs?????? ( for jus 5 rows, confused here )...

Ok lets see what i get..

Code:
US18_DEV_DBA> SELECT value FROM v$parameter
  2  WHERE name = 'db_block_size';

VALUE
------------------------------------------------------------------------
4096

US18_DEV_DBA> 
US18_DEV_DBA> CREATE TABLE test_2
  2  (c1 VARCHAR2(4000),
  3  c2 VARCHAR2(4000),
  4  c3 VARCHAR2(4000),
  5  c4 VARCHAR2(4000),
  6  c5 VARCHAR2(4000))
  7  PCTFREE 0;

Table created.

US18_DEV_DBA> 
US18_DEV_DBA> INSERT INTO test_2 (c1,c2,c3,c4,c5) VALUES
  2  (RPAD('x',4000,'x'),
  3  RPAD('x',4000,'x'),
  4  RPAD('x',4000,'x'),
  5  RPAD('x',4000,'x'),
  6  RPAD('x',4000,'x'));

1 row created.

US18_DEV_DBA> /

1 row created.

US18_DEV_DBA> /

1 row created.

US18_DEV_DBA> /

1 row created.

US18_DEV_DBA> /

1 row created.

US18_DEV_DBA> COMMIT;

Commit complete.

US18_DEV_DBA> 
US18_DEV_DBA> SELECT COUNT(*) FROM test_2;

            COUNT(*)
--------------------
                   5

US18_DEV_DBA> SELECT 
  2  COUNT(DISTINCT SUBSTR(ROWID, 1, 15))*4096/1024 
  3  AS "Table Size in KBytes" 
  4  FROM test_2;

Table Size in KBytes
--------------------
                  20


US18_DEV_DBA> ed
Wrote file afiedt.buf

  1  SELECT
  2  COUNT(DISTINCT SUBSTR(ROWID, 1, 15)) AS "No Of Blocks Used By Table"
  3* FROM test_2
US18_DEV_DBA> /

No Of Blocks Used By Table
--------------------------
                         5
The one which should have taken min of 100KB is well within 20KB??
Thus we need to understand a bit more on how the space is used up....

Originally posted by jmodic
Another problematic term - "actual size used by table". What is the "actual size used by table"? Sum of all table blocks that contain at least one row? Doesn't make sence to me, as it tells you nothing about how full those blocks actually are. Your query returns the same result no matter if each block contains only one tiny record of few bytes (and each block still has more than 95% free space left for new records or for updates of existing one, for example) or if each block is 100% fully packed with data, not able to accept a single byte more.
Its upto one how one thinks, if you want the effeciency of the utilazation of blocks then you gotta take an AVG & interpret then
But i dont think the original poster wanted that, if yes then we can giv him that query as well !!

Abhay.