Shulou(Shulou.com)05/31 Report--

Oracle relative file number (RFN) and absolute file number (AFN) example analysis, I believe that many inexperienced people do not know what to do, so this article summarizes the causes of the problem and solutions, through this article I hope you can solve this problem.

As we all know, from Oracle8, Oracle began to use "relative file number", so that the original database can only have up to 1023 files, expanded to a table space can have up to 1023 files, each library can have up to 65534 files.

The absolute file number is unique to the entire database, and each library can have up to 65534 files.

The relative file number is unique to the table space, that is, each table space has its own relative file number, and the maximum number of files per table space is 1023. The relative file number is not unique for the whole database.

Query:

SQL > select TS#,FILE#,name from v$datafile order by 1 Pol 2

TS# FILE# NAME

-

01/ u01/app/oracle/oradata/DBdb/system01.dbf

1 2 / u01/app/oracle/oradata/DBdb/sysaux01.dbf

2 3 / u01/app/oracle/oradata/DBdb/undotbs01.dbf

4 4 / u01/app/oracle/oradata/DBdb/users01.dbf

4 7 / u01/app/oracle/oradata/DBdb/users02.dbf

4 9 / u01/app/oracle/oradata/DBdb/users03.dbf

6 5 / u01/app/oracle/oradata/DBdb/example01.dbf

7 6 / u01/app/oracle/oradata/DBdb/test.dbf

7 8 / u01/app/oracle/oradata/DBdb/test02.DBF

9 rows selected.

Query v$datafile this view lacks tmp temporary tablespace information and contains all data files under all tablespaces except tmp temporary tablespaces

SQL > select TS#,FILE#,NAME,BYTES/1024/1024 size_m from v$tempfile

TS# FILE# NAME SIZE_M

-

3 1/ u01/app/oracle/oradata/DBdb/temp01.dbf 179

File# absolute file number

SQL > select TS#,NAME,BIGFILE from v$tablespace order by 1

TS# NAME BIG

0 SYSTEM NO

1 SYSAUX NO

2 UNDOTBS1 NO

3 TEMP NO

4 USERS NO

6 EXAMPLE NO

7 TEST NO

7 rows selected.

SQL > select TABLESPACE_NAME,FILE_ID,FILE_NAME,BYTES/1024/1024 size_m,STATUS from dba_data_files order by 2

TABLESPACE_NAME FILE_ID FILE_NAME SIZE_M STATUS

SYSTEM 1/ u01/app/oracle/oradata/DBdb/system01.dbf 2800 AVAILABLE

SYSAUX 2 / u01/app/oracle/oradata/DBdb/sysaux01.dbf 720 AVAILABLE

UNDOTBS1 3 / u01/app/oracle/oradata/DBdb/undotbs01.dbf 2585 AVAILABLE

USERS 4 / u01/app/oracle/oradata/DBdb/users01.dbf 3058.75 AVAILABLE

EXAMPLE 5 / u01/app/oracle/oradata/DBdb/example01.dbf 338.75 AVAILABLE

TEST 6 / u01/app/oracle/oradata/DBdb/test.dbf 10 AVAILABLE

USERS 7 / u01/app/oracle/oradata/DBdb/users02.dbf 10 AVAILABLE

TEST 8 / u01/app/oracle/oradata/DBdb/test02.DBF 10 AVAILABLE

USERS 9 / u01/app/oracle/oradata/DBdb/users03.dbf 10 AVAILABLE

9 rows selected.

The query contains all the data files under the tablespace

SQL > select TABLESPACE_NAME,STATUS,CONTENTS,FORCE_LOGGING,EXTENT_MANAGEMENT,SEGMENT_SPACE_MANAGEMENT,ALLOCATION_TYPE,RETENTION,BIGFILE from dba_tablespaces

TABLESPACE_NAME STATUS CONTENTS FOR EXTENT_MAN SEGMEN ALLOCATIO RETENTION BIG

SYSTEM ONLINE PERMANENT NO LOCAL MANUAL SYSTEM NOT APPLY NO

SYSAUX ONLINE PERMANENT NO LOCAL AUTO SYSTEM NOT APPLY NO

UNDOTBS1 ONLINE UNDO NO LOCAL MANUAL SYSTEM NOGUARANTEE NO

TEMP ONLINE TEMPORARY NO LOCAL MANUAL UNIFORM NOT APPLY NO

USERS ONLINE PERMANENT NO LOCAL AUTO SYSTEM NOT APPLY NO

EXAMPLE ONLINE PERMANENT NO LOCAL AUTO SYSTEM NOT APPLY NO

TEST ONLINE PERMANENT NO LOCAL AUTO SYSTEM NOT APPLY NO

7 rows selected.

The experiment begins:

SQL > create tablespace test_mf datafile'/ u01 size reuse

Tablespace created.

SQL > alter tablespace test_mf add datafile'/ u01ActionAccording to oradata, DBDB size m2.dbf'SQL 88k

Tablespace altered.

SQL > show parameter db_files

NAME TYPE VALUE

-

Db_files integer 2000

SQL >

SQL > begin

For i in 3..1025 loop

Execute immediate'alter tablespace test_mf add datafile'/ u01 size'/ U01 size'| I | 'oracle 88k'

End loop

End

/

Begin

*

ERROR at line 1:

ORA-01686: max # files (1023) reached for the tablespace TEST_MF

ORA-06512: at line 3

SQL > select count (*) from dba_data_files where tablespace_name='TEST_MF'

COUNT (*)

-

1023

You can see that the number of files in the tablespace TEST_MF is 1023, and the maximum number is 1023.

SQL > select count (*) from dba_data_files where tablespace_name='TEST_MF'

COUNT (*)

-

1023

SQL > select ts# from v$tablespace where name='TEST_MF'

TS#

-

nine

-- query the relative file number and absolute file number of tablespace test_mf:

Select file_id,relative_fno,TABLESPACE_NAME,FILE_NAME from dba_data_files where tablespace_name='TEST_MF'

Or

Select file#,rfile#,name from v$datafile where ts#=9

SQL > select file#,rfile#,name from v$datafile where ts#=9

FILE# RFILE# NAME

-

10 10 / u01/app/oracle/oradata/DBdb/m1.dbf

11 11 / u01/app/oracle/oradata/DBdb/m2.dbf

12 12 / u01/app/oracle/oradata/DBdb/m_3

13 13 / u01/app/oracle/oradata/DBdb/m_4

14 14 / u01/app/oracle/oradata/DBdb/m_5

15 15 / u01/app/oracle/oradata/DBdb/m_6

16 16 / u01/app/oracle/oradata/DBdb/m_7

17 17 / u01/app/oracle/oradata/DBdb/m_8

18 18 / u01/app/oracle/oradata/DBdb/m_9

Omit.

FILE# RFILE# NAME

-

1011 1011 / u01/app/oracle/oradata/DBdb/m_1002

1012 1012 / u01/app/oracle/oradata/DBdb/m_1003

1013 1013 / u01/app/oracle/oradata/DBdb/m_1004

1014 1014 / u01/app/oracle/oradata/DBdb/m_1005

1015 1015 / u01/app/oracle/oradata/DBdb/m_1006

1016 1016 / u01/app/oracle/oradata/DBdb/m_1007

1017 1017 / u01/app/oracle/oradata/DBdb/m_1008

1018 1018 / u01/app/oracle/oradata/DBdb/m_1009

1019 1019 / u01/app/oracle/oradata/DBdb/m_1010

1020 1020 / u01/app/oracle/oradata/DBdb/m_1011

1021 1021 / u01/app/oracle/oradata/DBdb/m_1012

FILE# RFILE# NAME

-

1022 1022 / u01/app/oracle/oradata/DBdb/m_1013

1023 1023 / u01/app/oracle/oradata/DBdb/m_1014

1024 1/ u01/app/oracle/oradata/DBdb/m_1015

1025 2 / u01/app/oracle/oradata/DBdb/m_1016

1026 3 / u01/app/oracle/oradata/DBdb/m_1017

1027 4 / u01/app/oracle/oradata/DBdb/m_1018

1028 5 / u01/app/oracle/oradata/DBdb/m_1019

1029 6 / u01/app/oracle/oradata/DBdb/m_1020

1030 7 / u01/app/oracle/oradata/DBdb/m_1021

1031 8 / u01/app/oracle/oradata/DBdb/m_1022

1032 9 / u01/app/oracle/oradata/DBdb/m_1023

1023 rows selected.

SQL >

As can be seen from the above data, when the absolute file number is less than or equal to 1023, the relative file number is the same as the absolute file number. After the relative file number is greater than 1023, the loop starts again from 1.

SQL > create tablespace test_lf datafile'/ u01 size

Tablespace created.

SQL > select ts# from v$tablespace where name='TEST_LF'

TS#

-

ten

SQL > select file#,rfile#,name from v$datafile where ts#=10

FILE# RFILE# NAME

-

1033 10 / u01/app/oracle/oradata/DBdb/TEST_LF.dbf

As can be seen from the above data, the relative file number of a tablespace data file does not start with 1, but continues from the last relative file number used, and the file# number increases from 1032 to 1033, while RFILE# increases from 9 to 10.

-- then add a new tablespace ORA

SQL > create tablespace ORA datafile'/ u01 size

Tablespace created.

SQL >

SQL > alter tablespace ORA add datafile'/ u01 size

Tablespace altered.

SQL > select file_id,relative_fno,TABLESPACE_NAME,FILE_NAME from dba_data_files where tablespace_name='ORA'

FILE_ID RELATIVE_FNO TABLESPACE_NAME FILE_NAME

1034 11 ORA / u01/app/oracle/oradata/DBdb/ora.dbf

1035 12 ORA / u01/app/oracle/oradata/DBdb/ora02.dbf

From the above query, the FILE_ID absolute file number increases progressively in the form of creating a table space or adding data files to the table space, while RELATIVE_FNO (rfile#) relative to the table space, the number of data files in a table space is less than or equal to 1023.

-- continue with the query

SQL > select OBJ#,OWNER# from obj$ where name='EMP'

OBJ# OWNER#

--

87108 83

SQL > select object_id from dba_objects where OWNER='SCOTT' and object_name='EMP' and object_type='TABLE'

OBJECT_ID

-

87108

SQL > select obj#,dataobj#,ts#,file# from tab$ where obj#=87108

OBJ# DATAOBJ# TS# FILE#

--

87108 87108 4 4

SQL > select SEGMENT_NAME,HEADER_FILE,HEADER_BLOCK,RELATIVE_FNO,TABLESPACE_NAME from dba_segments where SEGMENT_NAME='EMP' and SEGMENT_TYPE='TABLE'

SEGMENT_NAME HEADER_FILE HEADER_BLOCK RELATIVE_FNO TABLESPACE_NAME

EMP 4 146 4 USERS

In the DBA_SEGMENTS view, you can find the relative file number and the absolute file number of the segment header. (this view ultimately fetches data from dictionary tables such as file$, seg$, etc.)

After reading the above, have you mastered the method of sample analysis of relative file number (RFN) and absolute file number (AFN) in Oracle? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel, thank you for reading!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.