How Relative File Numbers Are Generated. [ID 262384.1]

How Relative File Numbers Are Generated. [ID 262384.1]
		Modified 13-JUL-2004     Type HOWTO     Status PUBLISHED

Applies To

Oracle Server - Enterprise Edition - Version: 9.2
This problem can occur on any platform.

Goal

This article will provide information about the following:
1) Absolute File Number is unique across Database.
2) Relative File Number is unique within a Tablespace.
3) Relative File Number is NOT unique across database.
4) Maximum of 1023 files per Tablespace.

Fix

How Absolute File Number is generated?

SQL> select tablespace_name from dba_tablespaces;

TABLESPACE_NAME
------------------------------
SYSTEM
RBS
USERS
TEMP
TOOLS
INDX
DRSYS
SOM
BANCHU_TEMP

9 rows selected.

SQL> select file_id,relative_fno from dba_data_files where tablespace_name='SOM';

FILE_ID RELATIVE_FNO
---------- ------------
8 8

SQL> select file_id,relative_fno from dba_data_files where tablespace_name='SYSTEM';

FILE_ID RELATIVE_FNO
---------- ------------
1 1

SQL> select file_id,relative_fno from dba_data_files where tablespace_name='USERS';

FILE_ID RELATIVE_FNO
---------- ------------
3 3
9 9
10 10
11 11
12 12
13 13
14 14
15 15
16 16
17 17
18 18
19 19
20 20
21 21
22 22
23 23
24 24
.. ..
.. ..
.. ..
1020 1020
1021 1021
1022 1022
1023 1023
1024 1

Note: Relative File Number can go to a maximum of 1023 in a Tablespace.


Now, adding 1 more file to "USERS" TS.

SQL> ALTER TABLESPACE USERS ADD DATAFILE 'G:/USER.DBF' SIZE 2K;

Tablespace altered.

SQL> COMMIT;

Commit complete.

SQL> select file_id,relative_fno from dba_data_files where tablespace_name='USERS';

FILE_ID RELATIVE_FNO
---------- ------------
3 3
9 9
10 10
11 11
12 12
13 13
14 14
15 15
16 16
17 17
18 18
19 19
20 20
21 21
22 22
.. ..
.. ..
.. ..
1020 1020
1021 1021
1022 1022
1023 1023
1024 1
1025 2

Please note that a new file with Relative File No 2 is added.


Now adding 1 file to "SOM" TS.


SQL> ALTER TABLESPACE SOM ADD DATAFILE 'G:/SOM.DBF' SIZE 1K;

Tablespace altered.

SQL> select file_id,relative_fno from dba_data_files where tablespace_name='SOM';

FILE_ID RELATIVE_FNO
---------- ------------
8 8
1026 3


Adding 1 more file to "USERS" TS:

SQL> ALTER TABLESPACE USERS ADD DATAFILE 'G:/USER2.DBF' SIZE 2K;

Tablespace altered.

SQL> COMMIT;

SQL> select file_id,relative_fno from dba_data_files where tablespace_name='USERS';

FILE_ID RELATIVE_FNO
---------- ------------
3 3
9 9
10 10
11 11
12 12
13 13
14 14
15 15
16 16
.. ..
.. ..
.. ..
1020 1020
1021 1021
1022 1022
1023 1023
1024 1
1025 2
1027 4

PLEASE NOTE THAT THE NEW FILE IS ADDED WITH RFNO 4 .
IT'S BECAUSE THIS TS ALREADY CONTAINS A FILE WITH RFNO 3.
So,Relative File Number is unique within a TS.


Now adding 1 more file to "SOM" TS.

SQL> ALTER TABLESPACE SOM ADD DATAFILE 'G:/SOMU2.DBF' SIZE 1K;

Tablespace altered.


SQL> select file_id,relative_fno from dba_data_files where tablespace_name='SOM';

FILE_ID RELATIVE_FNO
---------- ------------
8 8
1026 3
1028 5


Adding still 1 more file to "USERS" TS.

SQL> ALTER TABLESPACE USERS ADD DATAFILE 'G:/USER3.DBF' SIZE 2K;

Tablespace altered.

SQL> COMMIT;

Commit complete.

SQL> select file_id,relative_fno from dba_data_files where tablespace_name='USERS';

FILE_ID RELATIVE_FNO
---------- ------------
3 3
9 9
10 10
11 11
12 12
13 13
14 14
15 15
16 16
17 17
18 18
19 19
20 20
.. ..
.. ..
.. ..
1020 1020
1021 1021
1022 1022
1023 1023
1024 1
1025 2
1027 4
1029 6

We may wonder why RFNO 5 is skipped?
We will find this out later.


Adding 3 datafiles to "USERS" TS.

SQL> ALTER TABLESPACE USERS ADD DATAFILE 'G:/USER4.DBF' SIZE 2K;

Tablespace altered.

SQL> ALTER TABLESPACE USERS ADD DATAFILE 'G:/USER5.DBF' SIZE 2K;

Tablespace altered.

SQL> ALTER TABLESPACE USERS ADD DATAFILE 'G:/USER6.DBF' SIZE 2K;

Tablespace altered.

SQL> COMMIT;

Commit complete.

SQL> select file_id,relative_fno from dba_data_files where tablespace_name='USERS';

FILE_ID RELATIVE_FNO
---------- ------------
3 3
9 9
10 10
11 11
12 12
13 13
14 14
15 15
16 16
17 17
18 18
19 19
20 20
21 21
22 22
.. ..
.. ..
.. ..
1020 1020
1021 1021
1022 1022
1023 1023
1024 1
1025 2
1027 4
1029 6
1030 7
1031 8
1032 5

1023 rows selected.

Please note that RFNO 9 is skipped.
This TS already had a file with RFNO 9.
A File is created with RFNO 5(which was earlier skipped).
This is so because there is no unique number left now between 1 and 1023.
5 was skipped earlier because "SOM" TS had 5 as RFNO.
Still "USERS" TS can have 5 because it's Absolute File No. which uniquely identifies a file.
Relative File Number can be same across Tablespaces.


Adding 1 more file to "SOM" TS.

SQL> ALTER TABLESPACE SOM ADD DATAFILE 'G:/SOMU3.DBF' SIZE 2K;

Tablespace altered.

SQL> COMMIT;

Commit complete.

SQL> select file_id,relative_fno from dba_data_files where tablespace_name='SOM';

FILE_ID RELATIVE_FNO
---------- ------------
8 8
1026 3
1028 5
1033 10

It can be seen clearly that though RFNO with 6,7,8,9 can be generated, it skips.
This is because "USERS" TS has these RFNO.
When this TS will exhaust off all the RFNO, then it will use 6,7,8,9.

SQL> ALTER TABLESPACE USERS ADD DATAFILE 'G:/USER7.DBF' SIZE 2K;
ALTER TABLESPACE USERS
*
ERROR at line 1:
ORA-01686: max # files (1023) reached for the tablespace USERS

The above clearly demonstrates that there can be a maximum of 1023 files in a TS.

We can conclude:

1) Absolute File Number is unique across Database.
2) Relative File Number is unique within a Tablespace.
3) Relative File Number is NOT unique across database.
4) Maximum of 1023 files per Tablespace.

---end

Related Products Oracle Database Products > Oracle Database > Oracle Database > Oracle Server - Enterprise Edition