Oracle allows two bytes for row numbers, so theoretically they could go as high as 65535 rows in a block. However, given the minimum row length (11 bytes) and maximum database block size (32K), the pratical limit on the number of rows in a block is in fact only 2970.

For calculation purpose Let us say 3000 rows in a block of 32K (32768).

The size of a datafile for a Oracle tablespace is 4 GB ( 4,294,967,296 bytes).

# of rows in a 4GB datafile = (4294967296/32768)*3000 = 393,216,000 rows.

Assume a tablespace can support 1022 datafiles.

So max # of rows for a table = 393216000 * 1022 = 401,866,752,000 Rows.

So a table can have maximum of 400 billion rows if the row is size 11 bytes.

If I want to store 400 billion rows in a table, then I would not use Oracle, but Teradata.

Max # of partitions in a table = 64000

So a table can have (64000 * 400) billion rows.


[Edited by tamilselvan on 09-24-2001 at 02:32 PM]