一個表空間最多有多少個資料檔案和file#與rfile#的關係

是由rowid決定的

create tablespace tools datafile 'd:tools01.dbf' size 1M;

declare  
v_str varchar2(200);
begin
for i in 2..1024 loop
v_str:='alter tablespace tools add datafile '||'''D:tools'||i||'.DBF'''||' size 1m';
execute immediate v_str;
end loop;
end;
/
  

ORA-01686: max # files (1023) reached for the tablespace TOOLS
ORA-06512: at line 7


SQL> select count(*) from v$datafile where TS#=4;


  COUNT(*)
----------
      1023

SQL>

file#與rfile#的關係

rfile#相對資料檔案號的值只能由1到1023    oracle rowid 10bit rfile#

file# 資料檔案號一直往上遞增的,整個資料庫大概可以達到65533個檔案

SQL> select file#,rfile#,name,ts# from v$datafile where TS#=4 or TS#=8;
 
     FILE#     RFILE# NAME                                                                                    TS#
---------- ---------- -------------------------------------------------------------------------------- ----------
         4          4 D:\TOOLS01.DBF                                                                            4
         6          6 D:\TOOLS2.DBF                                                                             4
         7          7 D:\TOOLS3.DBF                                                                             4
         8          8 D:\TOOLS4.DBF                                                                             4
         9          9 D:\TOOLS5.DBF                                                                             4
        10         10 D:\TOOLS6.DBF                                                                             4
        11         11 D:\TOOLS7.DBF                                                                             4
        12         12 D:\TOOLS8.DBF                                                                             4
        13         13 D:\TOOLS9.DBF                                                                             4
        14         14 D:\TOOLS10.DBF                                                                            4
        15         15 D:\TOOLS11.DBF                                                                            4
        16         16 D:\TOOLS12.DBF                                                                            4
        17         17 D:\TOOLS13.DBF                                                                            4
        18         18 D:\TOOLS14.DBF                                                                            4
        19         19 D:\TOOLS15.DBF                                                                            4
        20         20 D:\TOOLS16.DBF                                                                            4
        21         21 D:\TOOLS17.DBF                                                                            4
        22         22 D:\TOOLS18.DBF                                                                            4
        23         23 D:\TOOLS19.DBF                                                                            4
        24         24 D:\TOOLS20.DBF                                                                            4
 
     FILE#     RFILE# NAME                                                                                    TS#
---------- ---------- -------------------------------------------------------------------------------- 
       104        104 D:\TOOLS100.DBF                                                                           4
       105        105 D:\TOOLS101.DBF                                                                           4
       106        106 D:\TOOLS102.DBF                                                                           4
       107        107 D:\TOOLS103.DBF                                                                           4
       108        108 D:\TOOLS104.DBF                                                                           4
...........................

      1021       1021 D:\TOOLS1017.DBF                                                                          4
      1022       1022 D:\TOOLS1018.DBF                                                                          4
      1023       1023 D:\TOOLS1019.DBF                                                                          4
      1024          1 D:\TOOLS1020.DBF                                                                          4
      1025          2 D:\TOOLS1021.DBF                                                                          4
      1026          3 D:\TOOLS1022.DBF                                                                          4
      1027          5 D:\TOOLS1023.DBF                                                                          4
      1028          5 D:\PAULS01.DBF                                                                            8
      1029          6 D:\PAULS2.DBF                                                                             8
      1030          7 D:\PAULS3.DBF                                                                             8
      1031          8 D:\PAULS4.DBF                                                                             8
      1032          9 D:\PAULS5.DBF                                                                             8
 
     FILE#     RFILE# NAME                                                                                    TS#
---------- ---------- -------------------------------------------------------------------------------- ----------
      1033         10 D:\PAULS6.DBF                                                                             8
      1034         11 D:\PAULS7.DBF                                                                             8
      1035         12 D:\PAULS8.DBF                                                                             8
      1036         13 D:\PAULS9.DBF                                                                             8
      1037         14 D:\PAULS10.DBF                                                                          8

可以看到rfile#的值由1-1023之間 並且對應一個表空間

file#的值一直是遞增的 相當於序列號 對應整個資料庫

SQL> alter system set db_files=100000 scope=spfile;

System altered.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORA-01131: DB_FILES system parameter value 100000 exceeds limit of 65534

可以看到資料檔案最多為65533