DATAFILEs with MAXSIZE UNLIMITED ...
SQL> select file_id,maxbytes/1024/1024 from dba_data_files;
FILE_ID MAXBYTES/1024/1024
---------- ------------------
1 32767.9844
2 32767.9844
3 32767.9844
4 32767.9844
the example above come from a 8k tablespace blocksize.
For 4k and 16k tablespace blocksize the maxsize is different indeed :
SQL> alter system set db_16k_cache_size = 100K scope=both;
System altered.
SQL> create tablespace test_bs16
2 datafile 'C:/ORACLE/ORADATA/DEMO102A/TEST_BS16_01.DBF'
3 size 10m
4 autoextend on maxsize unlimited
5 blocksize 16k
6 /
Tablespace created.
SQL> select maxbytes
2 from dba_data_files
3 where file_name = 'C:/ORACLE/ORADATA/DEMO102A/TEST_BS16_01.DBF';
MAXBYTES
----------
6,8719E+10
SQL> alter system set db_4k_cache_size = 100K scope=both;
System altered.
SQL> create tablespace test_bs4
2 datafile 'C:/ORACLE/ORADATA/DEMO102A/TEST_BS4_01.DBF'
3 size 10m
4 autoextend on maxsize unlimited
5 blocksize 4k
6 /
Tablespace created.
SQL> select maxbytes
2 from dba_data_files
3 where file_name = 'C:/ORACLE/ORADATA/DEMO102A/TEST_BS4_01.DBF'
4 /
MAXBYTES
----------
1,7180E+10
SQL> SELECT ((4*1024*1024)-1)*(4*1024) block4K,
2 ((4*1024*1024)-1)*(8*1024) block8K,
3 ((4*1024*1024)-1)*(16*1024) block16K
4 FROM dual;
BLOCK4K BLOCK8K BLOCK16K
---------- ---------- ----------
1.7180E+10 3.4360E+10 6.8719E+10
Assuming you are not using big files, the maximum number of blocks in a data file is 4Mb - 1, so the maximum file size is as shown in the select.
until 9i:
select file_id from dba_data_files where maxblocks=power(2,22)-2;
in 10g:
select file_id from dba_data_files join dba_tablespaces using (tablespace_name) where (bigfile='NO' and maxblocks=power(2,22)-2) or (bigfile='YES' and maxblocks=power(2,32)-3) ;
整理自网络