Originally posted by abhaysk
I am not sure wat is that u wana tell?
Ok, let's see:
Code:
SQL>  SELECT value FROM v$parameter
  2  WHERE name = 'db_block_size';

VALUE
--------------------------------------------
8192

SQL> CREATE TABLE test1
  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.

SQL> INSERT INTO test1 (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.

SQL> /

1 row created.

SQL> /

1 row created.

SQL> /

1 row created.

SQL> /

1 row created.

SQL> COMMIT;

Commit complete.

SQL> SELECT COUNT(*) FROM test1;

  COUNT(*)
----------
         5
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>
It is more than evident that those 5 rows can not fit into just 40 Kbytes.

Well what i meant by the ** "Table Size in MB" ** was that the actual size used by table. I wonder wat is the mistake in knowing actual Size
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.