Limitations of Oracle File size

A standard Oracle data file can contain up to 4,194,303 Oracle data blocks. So this also means that the maximum size of a single data file depends on the Oracle block size used.

(MOS ID 112011.1)

SQL> SELECT TO_CHAR (4194303 * VALUE, ‘999,999,999,999‘)           MAX_BYTES,       TO_CHAR (TRUNC (4194303 * VALUE / 1024), ‘999,999,999‘) || ‘ Kb‘           MAX_KB,       TO_CHAR (TRUNC (4194303 * VALUE / 1024 / 1024), ‘999,999‘) || ‘ Mb‘           MAX_MB  FROM v$parameter WHERE name = ‘db_block_size‘;  2    3    4    5    6    7    8  MAX_BYTES                    MAX_KB                        MAX_MB------------------------------------------------ --------------------------------------------- ---------------------------------  34,359,730,176                   33,554,424 Kb                 32,767 Mb

8k maximum is 32,767 Mb. The correspondence between the db_block_size and the maximum file size is as follows:

 DB_BLOCK_SIZE    File Max Mb ~~~~~~~~~~~~~     ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~    2048             8191 M    4096            16383 M    8192            32767 M    16384           65535 M

Try to modify the size of the following data file:

SQL> select file_id,file_name,bytes/1024/1024 mb from dba_data_files;      FILE_ID FILE_NAME                         MB---------- --------------------------------------------- ----------     4 /u01/oradata/prodb/users01.dbf            106.25     3 /u01/oradata/prodb/sysaux01.dbf          270     2 /u01/oradata/prodb/undotbs01.dbf         605     1 /u01/oradata/prodb/system01.dbf          630     5 /u01/oradata/prodb/example01.dbf         100     6 /u01/oradata/prodb/test_01.dbf           2006 rows selected.SQL> alter database datafile 6 resize 33G;alter database datafile 6 resize 33G*ERROR at line 1:ORA-01144: File size (4325376 blocks) exceeds maximum of 4194303 blocks

Error, cannot exceed 4194303 blocks limit.

Where do the 4194303 blocks in the standard data file come from?

The detailed address of the normal data file data is composed of BASE64 encoded 18-bit ROWID, in the following form:

OOOOOOFFFBBBBBBRRROOOOOO：6位,data object idFFF：   3位,relative_fnoBBBBBB：6位,data block numberRRR：   3位,row number

To query the information for a row in a table:

SELECT SUBSTR (ROWID, 1, 6)  "object",        SUBSTR (ROWID, 7, 3)  "file",       SUBSTR (ROWID, 10, 6) "block",       SUBSTR (ROWID, 16, 3) "row"  FROM hr.employees WHERE ROWNUM = 1;       "object" "file" "block" "row"AAAMg6   AAF    AAAABY  

You can also use Dbms_rowid:

SELECT DBMS_ROWID.rowid_object (ROWID)       obj#,       DBMS_ROWID.rowid_relative_fno (ROWID) rfile#,       DBMS_ROWID.rowid_block_number (ROWID) block#,       DBMS_ROWID.rowid_row_number (ROWID)   row#,  FROM hr.employees WHERE ROWNUM = 1;

rowID converted to 2 binary number:
32bit obj# + 10bit file# + 22bit block# + 16bit row#

From this we can draw the following conclusions:
Maximum number of obj in a library =2^32=4g
Maximum file number of a tablespace =2^10=1024 (excluding file_id=0 data files, total 1023)
Maximum block number of a data file =2^22=4m=4194303
The maximum number of data rows in a block =2^16=64k

Restrictions please refer to: https://docs.oracle.com/cd/B19306_01/server.102/b14237/limits.htm#REFRN004

Oracle 10g introduces large file table spaces that can exceed the file size limit above.
In a large file tablespace, a table space contains only one file, and the 3-bit relative_fno in rowID no longer needs to save relative file number information, extending all the Block,rowid to OOOOOO.BBBBBBBBB.RRR.
rowID converted to 2 binary number:
32bit obj# + 32bit block# + 16bit row#
Maximum block number of a data file =2^32=4g
Therefore, for the same 8K data block, the file capacity of the large file table space is capped at 32TB.

The file number of the large file tablespace starts with 1024.
Large file table spaces do not support undo,temporary and system table spaces.

Limitations of Oracle File size