Shulou(Shulou.com)06/01 Report--

A standard Oracle data file can contain up to 4194303 Oracle blocks. So this also means that the upper limit of the size of a single datafile depends on the size of the Oracle block used.

(MOS ID 112011.1)

SQL > SELECT TO_CHAR (4194303 * VALUE, '999999999999') MAX_BYTES, TO_CHAR (TRUNC (4194303 * VALUE / 1024), '999999999') | 'Kb' MAX_KB, TO_CHAR (TRUNC (4194303 * VALUE / 1024 / 1024),' 999999') | 'Mb' MAX_MB FROM v$parameter WHERE name =' db_block_size' 2 3 4 5 6 7 8 MAX_BYTES MAX_KB MAX_MB -34359730176 33554424 Kb 32767 Mb

The maximum of 8k is 32767 Mb. The relationship between DB_BLOCK_SIZE and the maximum value of the file 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 data file below:

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 2702 / u01/oradata/prodb/undotbs01.dbf 605 1 / u01/oradata/prodb/system01.dbf 6305 / 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

The error cannot exceed the limit of 4194303 blocks.

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

The detailed address of ordinary data file data is composed of 18-bit rowid encoded by Base64, in the form of:

OOOOOOFFFBBBBBBRRROOOOOO:6 bit, data object idFFF: 3 bit, relative_fnoBBBBBB:6 bit, data block numberRRR: 3 bit, row number

Query the information for a row in the 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 ABD

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

The conversion from rowid to binary is as follows:

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 number of file in a tablespace = 2 ^ 10 = 1024 (excluding file_id=0 data files, a total of 1023)

Maximum number of block in a data file = 2 ^ 22 = 4M=4194303

The maximum number of rows in a block = 2 ^ 16 = 64K

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

Oracle 10g introduces a large file tablespace, which can break the above file size limit.

In a large file table space, a table space contains only one file, and the 3-bit relative_fno in rowid no longer needs to save the relative file number information, all of which are extended to block,rowid to become OOOOOO.BBBBBBBBB.RRR.

The conversion from rowid to binary is as follows:

32bit obj# + 32bit block# + 16bit row#

Maximum number of block in a data file = 2 ^ 32 = 4G

Therefore, for the same 8K data blocks, the file capacity limit for large file tablespaces is 32TB.

The file number for large file tablespaces starts with 1024.

Large file table spaces do not support UNDO,TEMPORARY and SYSTEM table spaces.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.