How Relative File Numbers Are Generated. [ID 262384.1]
来源:互联网 发布:eyeone软件 编辑:程序博客网 时间:2024/05/29 20:01
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
- How Relative File Numbers Are Generated. [ID 262384.1]
- How floating point numbers are represented
- cisco qos--How Are Conversation Numbers Allocated?
- How Btree Indexes Are Maintained (Doc ID 30405.1)
- HOW SHIPPING DATES ARE CALCULATED. (文档 ID 1076040.1)
- Find Out How Many File Descriptors Are Being Used
- How to Interpret the oraparam.ini File [ID 237097.1]
- How to Dump Redo Log File Information [ID 1031381.6]
- projecteuler No.145 How many reversible numbers are there below one-billion?
- PE 145 How many reversible numbers are there below one-billion? (暴力)
- How does ASM work with RAID where striping and mirroring are already built-in [ID 330398.1]
- How to Get Table’s Generated Ids
- Why are numbers beautiful?
- How to reference JSF managed beans which are provided in a JAR file?
- How to pass SOAP headers into python SUDS that are not defined in WSDL file
- automated save and generated infopath file name
- how old are 香港!
- How Classes are Found
- 整合iis+tomcat
- 在 UIWebView中搜索并高亮度显示文本
- tomcat #{..} is not allowed in template text
- C# join DataTable (support Left/Right/Full join)转贴
- window7安装Oracle10g全过程
- How Relative File Numbers Are Generated. [ID 262384.1]
- mysql触发器(trigger)操作memcache
- 第一个java网络程序,有客户端和服务器端,用socket实现
- 在使用CompareValidator对于日期进行验证的时候,最好多一个对于日期格式的验证
- 第 1 部分: Ajax 入门简介
- 自定义控件CS类中注册JS脚本并调用JS事件
- Paul Graham:未来的互联网创业(上)
- Hibernate学习笔记(3):session的get、load、delete方法和Query的简单使用
- C++ 虚函数表解析