dba-凯发app官方网站

oracle 从8i开始就对文件数量控制就引进了relative的概念，这个概念很好理解，但是查询v$datafile,dba_data_files等视图，这个概念又变的不太好理解了。

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

   file_id file_name                                                              tablespace_name      relative_fno
---------- ---------------------------------------------------------------------- -------------------- ------------
         1 /ora10g/data01/nero/system01.dbf                                   system                          1
         2 /ora10g/data01/nero/undotbs01.dbf                                  undotbs1                        2
         3 /ora10g/data01/nero/sysaux01.dbf                                   sysaux                          3
         4 /ora10g/data01/nero/users01.dbf                                    users                           4

按照一般的思维，每个tablespace的第一个文件的relative_fno应该为1才是，而这里file_id跟relative_fno却是相同的递增。

进一步实验：
首先给users表空间增加1022个数据文件（不存在0号文件，单个表空间数据文件上限是1023。这是oracle设计的时候就决定了，比如data block中rdba的前10位是作为r_fno，1023也就是上限了）。

sql> create or replace procedure insert_datafile_full                              
2 as
3 str varchar2(200);
4 begin
5 for i in 2..1022 loop
6 str:='alter tablespace users add datafile ''/ora10g/data01/nero/users0'||i||'.dbf'' size 1m reuse';
7 execute immediate str;
8 end loop;
9 end;
10 /

procedure created.

sql> exec insert_datafile_full;

pl/sql procedure successfully completed.

sql> alter tablespace users add datafile '/ora10g/data01/nero/users01023.dbf' size 1m reuse;

tablespace altered.

sql> alter tablespace users add datafile '/ora10g/data01/nero/users01024.dbf' size 1m reuse;
alter tablespace users add datafile '/ora10g/data01/nero/users01024.dbf' size 1m reuse
*
error at line 1:
ora-01686: max # files (1023) reached for the tablespace users

sql> select file_id,file_name,tablespace_name,relative_fno from dba_data_files where file_id<>relative_fno;

   file_id file_name                                                              tablespace_name      relative_fno
---------- ---------------------------------------------------------------------- -------------------- ------------
      1024 /ora10g/data01/nero/users01021.dbf                                 users                           1
      1025 /ora10g/data01/nero/users01022.dbf                                 users                           2
      1026 /ora10g/data01/nero/users01023.dbf                                 users                           3

sql> select file_id,file_name,tablespace_name,relative_fno from dba_data_files where file_id>1020;

   file_id file_name                                                              tablespace_name      relative_fno
---------- ---------------------------------------------------------------------- -------------------- ------------
      1021 /ora10g/data01/nero/users01018.dbf                                 users                        1021
      1022 /ora10g/data01/nero/users01019.dbf                                 users                        1022
      1023 /ora10g/data01/nero/users01020.dbf                                 users                        1023
      1024 /ora10g/data01/nero/users01021.dbf                                 users                           1
      1025 /ora10g/data01/nero/users01022.dbf                                 users                           2
      1026 /ora10g/data01/nero/users01023.dbf                                 users                           3

6 rows selected.

初见眉目了，r_fno似乎是1-1023的一个带cycle的sequence。

sql> select count(distinct relative_fno) from dba_data_files where tablespace_name='users';

count(distinctrelative_fno)
---------------------------
                       1023

sql> create tablespace ss datafile '/ora10g/data01/nero/ss01.dbf' size 1m reuse;

tablespace created.

sql> select file_id,file_name,tablespace_name,relative_fno from dba_data_files where tablespace_name='ss';

   file_id file_name                                                              tablespace_name      relative_fno
---------- ---------------------------------------------------------------------- -------------------- ------------
      1027 /ora10g/data01/nero/ss01.dbf                                       ss                              4

sql> create tablespace tt datafile '/ora10g/data01/nero/tt01.dbf' size 1m reuse;

tablespace created.

sql> select file_id,file_name,tablespace_name,relative_fno from dba_data_files where tablespace_name in ('ss','tt');

   file_id file_name                                                              tablespace_name      relative_fno
---------- ---------------------------------------------------------------------- -------------------- ------------
      1027 /ora10g/data01/nero/ss01.dbf                                       ss                              4
      1028 /ora10g/data01/nero/tt01.dbf                                       tt                              5

sql> alter tablespace ss add datafile '/ora10g/data01/nero/ss02.dbf' size 1m reuse;

tablespace altered.

sql> select file_id,file_name,tablespace_name,relative_fno from dba_data_files where tablespace_name in ('ss','tt');

   file_id file_name                                                              tablespace_name      relative_fno
---------- ---------------------------------------------------------------------- -------------------- ------------
      1027 /ora10g/data01/nero/ss01.dbf                                       ss                              4
      1028 /ora10g/data01/nero/tt01.dbf                                       tt                              5
      1029 /ora10g/data01/nero/ss02.dbf                                       ss                              6

sql> alter tablespace users drop datafile '/ora10g/data01/nero/users01020.dbf';

tablespace altered.

sql> alter tablespace users add datafile '/ora10g/data01/nero/users01020.dbf' size 1m reuse;

tablespace altered.

sql> select file_id,file_name,tablespace_name,relative_fno from dba_data_files where file_id>1020 order by file_id;

   file_id file_name                                                              tablespace_name      relative_fno
---------- ---------------------------------------------------------------------- -------------------- ------------
      1021 /ora10g/data01/nero/users01018.dbf                                 users                        1021
      1022 /ora10g/data01/nero/users01019.dbf                                 users                        1022
      1023 /ora10g/data01/nero/users01020.dbf                                 users                        1023
      1024 /ora10g/data01/nero/users01021.dbf                                 users                           1
      1025 /ora10g/data01/nero/users01022.dbf                                 users                           2
      1026 /ora10g/data01/nero/users01023.dbf                                 users                           3
      1027 /ora10g/data01/nero/ss01.dbf                                       ss                              4
      1028 /ora10g/data01/nero/tt01.dbf                                       tt                              5
      1029 /ora10g/data01/nero/ss02.dbf                                       ss                              6

9 rows selected.

sql> alter tablespace users drop datafile '/ora10g/data01/nero/users01020.dbf';

tablespace altered.

sql> select file_id,file_name,tablespace_name,relative_fno from dba_data_files where file_id>1020 order by file_id;

   file_id file_name                                                              tablespace_name      relative_fno
---------- ---------------------------------------------------------------------- -------------------- ------------
      1021 /ora10g/data01/nero/users01018.dbf                                 users                        1021
      1022 /ora10g/data01/nero/users01019.dbf                                 users                        1022
      1024 /ora10g/data01/nero/users01021.dbf                                 users                           1
      1025 /ora10g/data01/nero/users01022.dbf                                 users                           2
      1026 /ora10g/data01/nero/users01023.dbf                                 users                           3
      1027 /ora10g/data01/nero/ss01.dbf                                       ss                              4
      1028 /ora10g/data01/nero/tt01.dbf                                       tt                              5
      1029 /ora10g/data01/nero/ss02.dbf                                       ss                              6

8 rows selected.

sql> alter tablespace ss add datafile '/ora10g/data01/nero/ss03.dbf' size 1m reuse;

tablespace altered.

sql> select file_id,file_name,tablespace_name,relative_fno from dba_data_files where file_id>1020 order by file_id;

   file_id file_name                                                              tablespace_name      relative_fno
---------- ---------------------------------------------------------------------- -------------------- ------------
      1021 /ora10g/data01/nero/users01018.dbf                                 users                        1021
      1022 /ora10g/data01/nero/users01019.dbf                                 users                        1022
      1023 /ora10g/data01/nero/ss03.dbf                                       ss                           1023
      1024 /ora10g/data01/nero/users01021.dbf                                 users                           1
      1025 /ora10g/data01/nero/users01022.dbf                                 users                           2
      1026 /ora10g/data01/nero/users01023.dbf                                 users                           3
      1027 /ora10g/data01/nero/ss01.dbf                                       ss                              4
      1028 /ora10g/data01/nero/tt01.dbf                                       tt                              5
      1029 /ora10g/data01/nero/ss02.dbf                                       ss                              6

9 rows selected.

sql> alter tablespace users add datafile '/ora10g/data01/nero/users01020.dbf' size 1m reuse;

tablespace altered.

sql> select file_id,file_name,tablespace_name,relative_fno from dba_data_files where file_id>1020 order by file_id;

   file_id file_name                                                              tablespace_name      relative_fno
---------- ---------------------------------------------------------------------- -------------------- ------------
      1021 /ora10g/data01/nero/users01018.dbf                                 users                        1021
      1022 /ora10g/data01/nero/users01019.dbf                                 users                        1022
      1023 /ora10g/data01/nero/ss03.dbf                                       ss                           1023
      1024 /ora10g/data01/nero/users01021.dbf                                 users                           1
      1025 /ora10g/data01/nero/users01022.dbf                                 users                           2
      1026 /ora10g/data01/nero/users01023.dbf                                 users                           3
      1027 /ora10g/data01/nero/ss01.dbf                                       ss                              4
      1028 /ora10g/data01/nero/tt01.dbf                                       tt                              5
      1029 /ora10g/data01/nero/ss02.dbf                                       ss                              6
      1030 /ora10g/data01/nero/users01020.dbf                                 users                        1023

10 rows selected.

到这里可以猜测，relative file_no 应该是类似于一个1-1023的全局的带cycle的循环 递增的’sequence’，我们增加datafile的时候会用’seq’.nextval来填充该r_fno,如果nextval与该 tablespace的数据文件的relative file_no冲突，就会尝试下个nextval，以此循环，如果发现1-1023都冲突，说明该表空间数据文件数量达到上限了。

ref: http://hi.baidu.com/kywinder/blog/item/4d2bd3d13d97890e3bf3cfc1.html