Oracle下絕對檔案号和相對檔案号差別

一：oracle官方給出的描述

Oracle Database assigns each datafile two associated file numbers, an absolute file

number and a relative file number, that are used to uniquely identify it

Absolute：Uniquely identifies a datafile in the database. This file number can be used in many SQL statements that reference datafiles in place of using the file name. The absolute file number can be found in the FILE# column of the V$DATAFILE or V$TEMPFILE view, or in the FILE_ID column of the DBA_DATA_FILES or DBA_TEMP_FILES  view

Relative：Uniquely identifies a datafile within a tablespace. For small and medium size databases, relative file numbers usually have the same value as the absolute file number. However, when the

number of datafiles in a database exceeds a threshold (typically 1023), the relative file number differs from the absolute file number. In a bigfile tablespace, the relative file number is always

1024 (4096 on OS/390 platform).

二：總結如下

總述：oracle資料庫會給每一個新建立的datafile配置設定一個絕對檔案号和相對檔案号，兩者都用于唯一性辨別該資料檔案，隻不過辨別範圍不一樣。

絕對檔案号：

1 絕對檔案号資料庫範圍内唯一辨別一個資料檔案

2 sql語句中常常用絕對檔案号代替該資料檔案名字。

3 絕對檔案号可通過查詢視圖v$datafile|v$tempfile|dba_data_files|dba_temp_files相關字段file#|file_id 獲得

例如：v$datafile 的file#字段

select file#,name,status from v$datafile

     FILE# NAME                                     STATUS

---------- ---------------------------------------- -------

         1 /oracle/CRM2/system1.dbf                 SYSTEM

         2 /oracle/CRM2/zxb.dbf                     ONLINE

         3 /oracle/CRM2/CRM/sysaux01.dbf            ONLINE

         4 /oracle/CRM2/CRM/users01.dbf             ONLINE

         5 /oracle/CRM2/zxa.dbf                     ONLINE

         6 /oracle/CRM2/CRM/test1.dbf               ONLINE

         7 /oracle/CRM2/zxc.dbf                     ONLINE

         8 /oracle/CRM2/CRM/undotbs1.dbf            ONLINE

相當檔案号：

1 相對檔案号在表空間範圍内唯一辨別一個資料檔案。

2 資料庫内資料檔案沒有超過1023個，相對檔案号和絕對檔案号相等，反之則不同。

3 在大表空間中資料檔案相對檔案号總是1024

4 查詢v$datafile|v$tempfile字段RFILE# ,dba_data_files|dba_temp_files字段RELATIVE_FNO等，可查出相對檔案号。

例1如 v$datafile的字段rfile#

SQL> Select file#,rfile#,name,status from v$datafile;

     FILE#     RFILE#  NAME                                     STATUS

---------- ---------- ---------------------------------------- -------

         1          1   /oracle/CRM2/system1.dbf                 SYSTEM

         2          2   /oracle/CRM2/zxb.dbf                     ONLINE

         3          3   /oracle/CRM2/CRM/sysaux01.dbf            ONLINE

         4          4   /oracle/CRM2/CRM/users01.dbf             ONLINE

         5          5   /oracle/CRM2/zxa.dbf                     ONLINE

         6          6   /oracle/CRM2/CRM/test1.dbf               ONLINE

         7          7   /oracle/CRM2/zxc.dbf                     ONLINE

         8          8   /oracle/CRM2/CRM/undotbs1.dbf            ONLINE

8 rows selected.

例2 大表空間絕對檔案号和相對檔案号情況

SQL> select file#,rfile#,name,status from v$datafile;

     FILE#     RFILE#  NAME                                     STATUS

         1          1  /oracle/CRM2/system1.dbf                 SYSTEM

         2          2  /oracle/CRM2/zxb.dbf                     ONLINE

         3          3  /oracle/CRM2/CRM/sysaux01.dbf            ONLINE

         4          4  /oracle/CRM2/CRM/users01.dbf             ONLINE

         5          5  /oracle/CRM2/zxa.dbf                     ONLINE

         6          6  /oracle/CRM2/CRM/test1.dbf               ONLINE

         7          7  /oracle/CRM2/zxc.dbf                      ONLINE

         8          8  /oracle/CRM2/CRM/undotbs1.dbf            ONLINE

         9       1024  /oracle/CRM2/CRM/zxbig.dbf               ONLINE

SQL>select file_id,relative_fno,file_name from dba_data_files order by 1

   FILE_ID RELATIVE_FNO  FILE_NAME

---------- ------------ ----------------------------------------

         1            1  /oracle/CRM2/system1.dbf

         2            2  /oracle/CRM2/zxb.dbf

         3            3  /oracle/CRM2/CRM/sysaux01.dbf

         4            4  /oracle/CRM2/CRM/users01.dbf

         5            5  /oracle/CRM2/zxa.dbf

         6            6  /oracle/CRM2/CRM/test1.dbf

         7            7  /oracle/CRM2/zxc.dbf

         8            8  /oracle/CRM2/CRM/undotbs1.dbf

         9         1024  /oracle/CRM2/CRM/zxbig.dbf

本文轉自 zhangxuwl 51CTO部落格，原文連結：http://blog.51cto.com/jiujian/1068498，如需轉載請自行聯系原作者