Oracle绝对文件号和相对文件号

大家都知道从Oracle8开始，Oracle开始使用“相对文件号”，使原来一个数据库最多只能有1023个文件，扩展为一个表空间最多可以有1023个文件，每个库最多可以有65534个文件 。
绝对文件号是整个数据库唯一的；相对文件号只在表空间唯一，就是说每个表空间都有自己的相对文件号；相对文件号对于整个数据库来说是不唯一的；
当数据库中数据文件的数量大于1023时，绝对数据文件号会继续递增，而相对文件号会重新从1开始。




SQL> select file_name,file_id,relative_fno,tablespace_name from dba_data_files;


FILE_NAME                                                       FILE_ID RELATIVE_FNO TABLESPACE_NAME
------------------------------------------------------------ ---------- ------------ ---------------
D:\APP\ZHUZHONGYOU\ORADATA\DBSDMBOF\DATAFILE\O1_MF_USERS_B41          4            4 USERS
JWKQG_.DBF


D:\APP\ZHUZHONGYOU\ORADATA\DBSDMBOF\DATAFILE\O1_MF_UNDOTBS1_          3            3 UNDOTBS1
9Z5H0VF2_.DBF


D:\APP\ZHUZHONGYOU\ORADATA\DBSDMBOF\DATAFILE\O1_MF_SYSAUX_9Z          2            2 SYSAUX
5H0VDX_.DBF


D:\APP\ZHUZHONGYOU\ORADATA\DBSDMBOF\DATAFILE\O1_MF_SYSTEM_9Z          1            1 SYSTEM
5H0VBT_.DBF


FILE_NAME                                                       FILE_ID RELATIVE_FNO TABLESPACE_NAME
------------------------------------------------------------ ---------- ------------ ---------------


D:\APP\ZHUZHONGYOU\ORADATA\DBSDMBOF\DATAFILE\O1_MF_EXAMPLE_9          5            5 EXAMPLE
Z5H5BGQ_.DBF


D:\APP\ZHUZHONGYOU\ORADATA\DBSDMBOF\DATAFILE\USERS02.DBF              6            6 USERS
D:\APP\ZHUZHONGYOU\ORADATA\DBSDMBOF\DATAFILE\JACK01.DBF               7            7 JACK


7 rows selected.








  1  begin
  2  for i in 1 .. 1010
  3  loop
  4  execute immediate
  5  'alter tablespace users add datafile ''D:\APP\ZHUZHONGYOU\ORADATA\DBSDMBOF\DATAFILE\USERS02_'||i||'.DBF'' size 1M';
  6  end loop;
  7* end;
SQL> /
begin
*
ERROR at line 1:
ORA-00059: maximum number of DB_FILES exceeded
ORA-06512: at line 4




SQL> show parameter db_file


NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_file_multiblock_read_count        integer     128
db_file_name_convert                 string
db_files                             integer     300


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


System altered.


SQL>shutdown immediate




SQL>startup


select file_name,file_id,relative_fno,tablespace_name from dba_data_files




SQL> begin
  2  for i in 292 .. 1010
  3  loop
  4  execute immediate
  5  'alter tablespace users add datafile ''D:\APP\ZHUZHONGYOU\ORADATA\DBSDMBOF\DATAFILE\USERS02_'||i||'.DBF'' size 1M';


  6  end loop;
  7  end;
  8  /


PL/SQL procedure successfully completed.






SQL> select file_name,file_id,relative_fno,tablespace_name from dba_data_files;


FILE_NAME                                                       FILE_ID RELATIVE_FNO TABLESPACE_NAME
------------------------------------------------------------ ---------- ------------ ---------------
D:\APP\ZHUZHONGYOU\ORADATA\DBSDMBOF\DATAFILE\O1_MF_USERS_B41          4            4 USERS
JWKQG_.DBF


D:\APP\ZHUZHONGYOU\ORADATA\DBSDMBOF\DATAFILE\O1_MF_UNDOTBS1_          3            3 UNDOTBS1
9Z5H0VF2_.DBF


D:\APP\ZHUZHONGYOU\ORADATA\DBSDMBOF\DATAFILE\O1_MF_SYSAUX_9Z          2            2 SYSAUX
5H0VDX_.DBF


D:\APP\ZHUZHONGYOU\ORADATA\DBSDMBOF\DATAFILE\O1_MF_SYSTEM_9Z          1            1 SYSTEM
.
.
.


FILE_NAME                                                                      FILE_ID RELATIVE_FNO TABLESPACE
--------------------------------------------------------------------------- ---------- ------------ ----------
D:\APP\ZHUZHONGYOU\ORADATA\DBSDMBOF\DATAFILE\USERS02_1004.DBF                     1013         1013 USERS
D:\APP\ZHUZHONGYOU\ORADATA\DBSDMBOF\DATAFILE\USERS02_1005.DBF                     1014         1014 USERS
D:\APP\ZHUZHONGYOU\ORADATA\DBSDMBOF\DATAFILE\USERS02_1006.DBF                     1015         1015 USERS
D:\APP\ZHUZHONGYOU\ORADATA\DBSDMBOF\DATAFILE\USERS02_1007.DBF                     1016         1016 USERS
D:\APP\ZHUZHONGYOU\ORADATA\DBSDMBOF\DATAFILE\USERS02_1008.DBF                     1017         1017 USERS
D:\APP\ZHUZHONGYOU\ORADATA\DBSDMBOF\DATAFILE\USERS02_1009.DBF                     1018         1018 USERS
D:\APP\ZHUZHONGYOU\ORADATA\DBSDMBOF\DATAFILE\USERS02_1010.DBF                     1019         1019 USERS


1019 rows selected.






SQL> begin
  2  for i in 1 .. 10
  3  loop
  4  execute immediate
  5  'alter tablespace block_t add datafile ''D:\APP\ZHUZHONGYOU\ORADATA\DBSDMBOF\DATAFILE\BLOCK_T_'||i||'.DBF'' size 1M';
  6  end loop;
  7  end;
  8  /


PL/SQL procedure successfully completed.








SQL> select file_name,file_id,relative_fno,tablespace_name from dba_data_files;


D:\APP\ZHUZHONGYOU\ORADATA\DBSDMBOF\DATAFILE\BLOCK_T_1.DBF                        1020         1020 USERS
D:\APP\ZHUZHONGYOU\ORADATA\DBSDMBOF\DATAFILE\BLOCK_T_2.DBF                        1021         1021 USERS
D:\APP\ZHUZHONGYOU\ORADATA\DBSDMBOF\DATAFILE\BLOCK_T_3.DBF                        1022         1022 USERS
D:\APP\ZHUZHONGYOU\ORADATA\DBSDMBOF\DATAFILE\BLOCK_T_4.DBF                        1023         1023 USERS


FILE_NAME                                                                      FILE_ID RELATIVE_FNO TABLESPACE_NAME
--------------------------------------------------------------------------- ---------- ------------ ---------------
D:\APP\ZHUZHONGYOU\ORADATA\DBSDMBOF\DATAFILE\BLOCK_T_5.DBF                        1024            1 BLOCK_T
D:\APP\ZHUZHONGYOU\ORADATA\DBSDMBOF\DATAFILE\BLOCK_T_6.DBF                        1025            2 BLOCK_T
D:\APP\ZHUZHONGYOU\ORADATA\DBSDMBOF\DATAFILE\BLOCK_T_7.DBF                        1026            3 BLOCK_T
D:\APP\ZHUZHONGYOU\ORADATA\DBSDMBOF\DATAFILE\BLOCK_T_8.DBF                        1027            4 BLOCK_T
D:\APP\ZHUZHONGYOU\ORADATA\DBSDMBOF\DATAFILE\BLOCK_T_9.DBF                        1028            5 BLOCK_T
D:\APP\ZHUZHONGYOU\ORADATA\DBSDMBOF\DATAFILE\BLOCK_T_10.DBF                       1029            6 BLOCK_T

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/28786649/viewspace-1316008/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/28786649/viewspace-1316008/