Oracle 绝对和相对文件编号研究

目录

• 概述
  • 绝对文件编号
  • 相对文件编号
• 实践过程
  • 调整数据文件参数为5000
  • 创建测试表空间
  • 批量创建数据文件
  • 继续验证表空间数据文件
• 测试小结
• 数据库限制
  • 数据类型限制
  • 数据库物理限制
  • 数据库逻辑限制
  • 进程和运行限制

概述

数据库中一些数据文件的绝对文件编号和相对文件编号具有相同的值，但某些数据文件不会。本文档介绍数据库如何分配数据文件的绝对和相对文件编号。

绝对文件编号

绝对文件号在整个数据库中唯一，可以通过V$DATAFILE的FILE#查看，或者DBA_DATA_FILES视图的FILE_ID查看。当数据库新建一个数据文件时，通常会用一个未使用过的编号，但是一个文件如果被删除，它的编号可能被重复利用。文件号最大值为65533 ，通常受到db_files参数限制。

相对文件编号

相对文件号仅仅在表空间内唯一。可以通过V$DATAFILE的RFILE#查看，或者DBA_DATA_FILES视图的RELATIVE_FNO 查看。

当新创建的数据文件，分配的绝对文件号小于1023，那么相对文件号将会和绝对文件号一样，除非这个表空间内已经有相同的相对文件号。

对于从模板复制的数据文件、通过传输表空间功能或者PDB数据文件复制的数据文件，绝对文件编号在目标数据库会被重新分配唯一编号。相对文件编号会使用源数据库上首次创建文件时分配的编号，不再重新分配。因此，这些文件的绝对文件编号和相对文件编号可能不同。

Ex.

     FILE#     RFILE# NAME
---------- ---------- --------------------------------------------------
         1          1 <DIRECTORY>/system01.dbf            <--- (*1)
         3          3 <DIRECTORY>/sysaux01.dbf            <--- (*1)
         4          4 <DIRECTORY>/undotbs01.dbf           <--- (*1)
         5          1 <DIRECTORY>/pdbseed/system01.dbf    <--- (*2)
         6          4 <DIRECTORY>/pdbseed/sysaux01.dbf    <--- (*2)
         7          7 <DIRECTORY>/users01.dbf             <--- (*1)
         8          9 <DIRECTORY>/pdbseed/undotbs01.dbf   <--- (*2)
         9          1 <DIRECTORY>/pdb_xxx/system01.dbf    <--- (*3)
        10          4 <DIRECTORY>/pdb_xxx/sysaux01.dbf    <--- (*3)
        11          9 <DIRECTORY>/pdb_xxx/undotbs01.dbf   <--- (*3)
        12         12 <DIRECTORY>/pdb_xxx/users01.dbf     <--- (*4)
        13         13 <DIRECTORY>/pdb_xxx/example01.dbf   <--- (*4)
       ...

  (*1) these files are newly created in cdb
  (*2) these files in pdb seed are copied from template
  (*3) these files in pdb_xxx are copied from pdb seed
  (*4) these files are newly created in pdb_xxx 

相对文件编号的最大值为1023.因此，如果新创建的数据文件具有超过1023的绝对文件编号，则相对文件编号被分配在表空间中不使用的1和1023之间的数字。

在BigFile表空间中，表空间由单个数据文件组成，因此不需要相对文件编号。 DBA_DATA_FILES和V$DATAFILE 展示1024对于相对文件编号，但实际上没有为BIGFILE表空间中的文件分配相关文件编号。

实践过程

系统环境

​ CentOS Linux release 7.5.1804

数据库环境

​ Oracle 11.2.0.4.0

​ ORACLE_SID=orcl
​ ORACLE_BASE=/u01/app/oracle
​ ORACLE_HOME=/u01/app/oracle/product/11.2.0.4/db

​ ORADATA:/oradata/three

调整数据文件参数为5000

[oracle@zstest ~]$ dba
SQL*Plus: Release 11.2.0.4.0 Production on Thu Aug 5 10:41:53 2021
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> set linesize 200
SQL> show parameter db_files

NAME                                 TYPE                              VALUE
------------------------------------ --------------------------------- ------------------
db_files                             integer                           5000

创建测试表空间

创建表空间zsdba，可以看到，FILE_ID和RELATIVE_FNO编号都为5，编号相同。

[oracle@zstest ~]$ dba
SQL*Plus: Release 11.2.0.4.0 Production on Thu Aug 5 10:24:46 2021
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> create tablespace zsdba datafile '/oradata/three/zsdba0001.dbf' size 88k;
Tablespace created.

SQL> col tablespace_name for a20
SQL> col file_name for a40
SQL> set linesize 200
SQL> select t.tablespace_name,t.file_name,t.file_id,t.relative_fno from dba_data_files t where t.tablespace_name = 'ZSDBA';

TABLESPACE_NAME      FILE_NAME                                   FILE_ID RELATIVE_FNO
-------------------- ---------------------------------------- ---------- ------------
ZSDBA                /oradata/three/zsdba0001.dbf                      5            5

创建文件大小最小为88k，Locally Managed Tablespace，64 Kbytes + 3 blocks for the bitmap blocks（64K + (3*8k)= 88k）。

参考MOS 153869.1

批量创建数据文件

使用脚本为表空间zsdba批量创建1020个数据文件，可以看到，此时FILE_ID在1024时，RELATIVE_FNO从1开始，因为在表空间zsdba中，RELATIVE_FNO编号1-4尚未使用，而FILE_ID则一直往下排列，不会出现重复的现象。

RELATIVE_FNO编号则在SYSTEM表空间中出现过，说明RELATIVE_FNO在整个数据库层面来说，是可以重复的。

declare
   str_sql varchar2(500);  
begin
  
  for i in 2..1020 loop
    begin
    str_sql:='alter tablespace zsdba add datafile '||''''||'/oradata/three/zsdba'||lpad(i,4,0)||'.dbf'||''''||'size 88k';
    --dbms_output.put_line(str_sql);
    execute immediate str_sql;
    exception  
       when others then  
          dbms_output.put_line(sqlcode||'---'||sqlerrm);
    end;
  end loop;
end;
/

SQL> select t.tablespace_name,t.file_name,t.file_id,t.relative_fno from dba_data_files t where t.tablespace_name = 'ZSDBA';

TABLESPACE_NAME      FILE_NAME                                   FILE_ID RELATIVE_FNO
-------------------- ---------------------------------------- ---------- ------------
ZSDBA                /oradata/three/zsdba0001.dbf                      5            5
ZSDBA                /oradata/three/zsdba0002.dbf                      6            6
.
.
.
.
ZSDBA                /oradata/three/zsdba1018.dbf                   1022         1022
ZSDBA                /oradata/three/zsdba1019.dbf                   1023         1023
ZSDBA                /oradata/three/zsdba1020.dbf                   1024            1

1020 rows selected.
SQL> select t.tablespace_name,t.file_name,t.file_id,t.relative_fno from dba_data_files t where t.relative_fno = 1;

TABLESPACE_NAME      FILE_NAME                                   FILE_ID RELATIVE_FNO
-------------------- ---------------------------------------- ---------- ------------
SYSTEM               /oradata/three/system01.dbf                       1            1
ZSDBA                /oradata/three/zsdba1020.dbf                   1024            1

继续验证表空间数据文件

通过批量创建1020个数据库后，发现RELATIVE_FNO在1023后，开始使用之前未使用编号1-4，继续为表空间zsdba创建数据文件。

可以发现在创建1024数据文件是报错，提示一个表空间内最大只能存放1023个数据文件。RELATIVE_FNO文件号从1-1023全部使用完。

[oracle@zstest ~]$ dba
SQL*Plus: Release 11.2.0.4.0 Production on Thu Aug 5 10:46:49 2021
opyright (c) 1982, 2013, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> alter tablespace zsdba add datafile '/oradata/three/zsdba1021.dbf'size 88k;
Tablespace altered.

SQL> alter tablespace zsdba add datafile '/oradata/three/zsdba1022.dbf'size 88k;
Tablespace altered.

SQL> alter tablespace zsdba add datafile '/oradata/three/zsdba1023.dbf'size 88k;
Tablespace altered.

SQL> alter tablespace zsdba add datafile '/oradata/three/zsdba1024.dbf'size 88k;
alter tablespace zsdba add datafile '/oradata/three/zsdba1024.dbf'size 88k
*
ERROR at line 1:
ORA-01686: max # files (1023) reached for the tablespace ZSDBA

SQL> select t.tablespace_name,t.file_name,t.file_id,t.relative_fno from dba_data_files t where t.tablespace_name = 'ZSDBA';

TABLESPACE_NAME      FILE_NAME                                   FILE_ID RELATIVE_FNO
-------------------- ---------------------------------------- ---------- ------------
ZSDBA                /oradata/three/zsdba0001.dbf                      5            5
ZSDBA                /oradata/three/zsdba0002.dbf                      6            6
.
.
.
.
ZSDBA                /oradata/three/zsdba1019.dbf                   1023         1023
ZSDBA                /oradata/three/zsdba1020.dbf                   1024            1
ZSDBA                /oradata/three/zsdba1021.dbf                   1025            2
ZSDBA                /oradata/three/zsdba1022.dbf                   1026            3
ZSDBA                /oradata/three/zsdba1023.dbf                   1027            4

1023 rows selected.

测试小结

从绝对文件编号和相对文件编号的描述，以及实践过程，可以总结如下：

1）绝对文件编号在数据库中是唯一的。

2）相对文件编号在表空间中是唯一的。

3）相对文件编号在数据库中不是唯一的。

4）每个表空间最多1023个文件。

以上内容参考MOS

Absolute File Number vs. Relative File Number (Doc ID 2614262.1)

How Relative File Numbers Are Generated. (Doc ID 262384.1)

不论是绝对文件编号，还是相对文件编号，都在数据库层面有自己的限制，下面给出各个类型的限制列表。

数据库限制

数据库在设计之初，有几个级别的限制，通常无法超过数据库中的硬编码限制。 对于任何给定的操作系统，可以进一步限制该值。

限制分类如下：

• 数据类型限制
• 数据库物理限制
• 数据库逻辑限制
• 进程和运行限制

官方链接 Database Limits

数据类型限制

Datatypes	Limit	Comments
BFILE	Maximum size: 4 GBMaximum size of a file name: 255 charactersMaximum size of a directory name: 30 charactersMaximum number of open BFILEs: see Comments	The maximum number of BFILEs is limited by the value of the SESSION_MAX_OPEN_FILES initialization parameter, which is itself limited by the maximum number of open files the operating system will allow.
BLOB	Maximum size: (4 GB - 1) * DB_BLOCK_SIZE initialization parameter (8 TB to 128 TB)	The number of LOB columns per table is limited only by the maximum number of columns per table (that is, 1000Foot 1).
CHAR	Maximum size: 2000 bytes	None
CHAR VARYING	Maximum size: 4000 bytes	None
CLOB	Maximum size: (4 GB - 1) * DB_BLOCK_SIZE initialization parameter (8 TB to 128 TB)	The number of LOB columns per table is limited only by the maximum number of columns per table (that is, 1000Foot 1).
Literals (characters or numbers in SQL or PL/SQL)	Maximum size: 4000 characters	None
LONG	Maximum size: 2 GB - 1	Only one LONG column is allowed per table.
NCHAR	Maximum size: 2000 bytes	None
NCHAR VARYING	Maximum size: 4000 bytes, or 32767 bytes if the MAX_STRING_SIZE initialization parameter is set to EXTENDEDSee Also: "MAX_STRING_SIZE" initialization parameter for additional details	None
NCLOB	Maximum size: (4 GB - 1) * DB_BLOCK_SIZE initialization parameter (8 TB to 128 TB)	The number of LOB columns per table is limited only by the maximum number of columns per table (that is, 1000Foot 1).
NUMBER	999...(38 9's) x10125 maximum value-999...(38 9's) x10125 minimum value	Can be represented to full 38-digit precision (the mantissa)Can be represented to full 38-digit precision (the mantissa)
Precision	38 significant digits	None
RAW	Maximum size: 2000 bytes, or 32767 bytes if the MAX_STRING_SIZE initialization parameter is set to EXTENDEDSee Also: "MAX_STRING_SIZE" initialization parameter for additional details	None
VARCHAR	Maximum size: 4000 bytes	None
VARCHAR2	Maximum size: 4000 bytes, or 32767 bytes if the MAX_STRING_SIZE initialization parameter is set to EXTENDEDSee Also: "MAX_STRING_SIZE" initialization parameter for additional details	None

数据库物理限制

Item	Type of Limit	Limit Value
Database Block Size	Minimum	2048 bytes; must be a multiple of operating system physical block size
Database Block Size	Maximum	Operating system dependent; never more than 32 KB
Database Blocks	Minimum in initial extent of a segment	2 blocks
Database Blocks	Maximum per datafile	Platform dependent; typically 2^22 - 1 blocks
Controlfiles	Number of control files	1 minimum; 2 or more (on separate devices) strongly recommended
Controlfiles	Size of a control file	Maximum of 201031680 logical blocks
Database files	Maximum per tablespace	Operating system dependent; usually 1022
Database files	Maximum per database	65533May be less on some operating systemsLimited also by size of database blocks and by the DB_FILES initialization parameter for a particular instance
Database extents	Maximum per dictionary managed tablespace	4 GB * physical block size (with K/M modifier); 4 GB (without K/M modifier)
Database extents	Maximum per locally managed (uniform) tablespace	2 GB * physical block size (with K/M modifier); 2 GB (without K/M modifier)
Database file size	Maximum	Operating system dependent. Limited by maximum operating system file size. See the Bigfile Tablespaces and Smallfile (traditional) Tablespaces rows for more information about the maximum database file size in these types of tablespaces.
MAXEXTENTS	Default value	Derived from tablespace default storage or DB_BLOCK_SIZE initialization parameter
MAXEXTENTS	Maximum	Unlimited
Redo Log Files	Maximum number of logfiles	Limited by value of MAXLOGFILES parameter in the CREATE DATABASE statementControl file can be resized to allow more entries; ultimately an operating system limit
Redo Log Files	Maximum number of logfiles per group	Unlimited
Redo Log File Size	Minimum size	4 MB
Redo Log File Size	Maximum Size	Operating system limit; typically 2 TB
Tablespaces	Maximum number per database	64 KNumber of tablespaces cannot exceed the number of database files because each tablespace must include at least one file
Bigfile Tablespaces	Number of blocks	A bigfile tablespace contains only one datafile or tempfile, which can contain up to approximately 4 billion ( 2^32 ) blocks. The maximum size of the single datafile or tempfile is 128 terabytes (TB) for a tablespace with 32 K blocks and 32 TB for a tablespace with 8 K blocks.
Smallfile (traditional) Tablespaces	Number of blocks	A smallfile tablespace is a traditional Oracle tablespace, which can contain 1022 datafiles or tempfiles, each of which can contain up to approximately 4 million (2^22) blocks.
External Tables file	Maximum size	Dependent on the operating system.An external table can be composed of multiple files.

数据库逻辑限制

Item	Type of Limit	Limit Value
Columns	Maximum per table	1000
Columns	Maximum per index (or clustered index)	32
Columns	Maximum per bitmapped index	30
Constraints	Maximum per column	Unlimited
Constraints	Maximum per database	4,294,967,293
Database users	Maximum per database	4,294,967,293
Dictionary-managed database objects	Maximum per database	4,254,950,911 - overhead
Indexes	Maximum per table	Unlimited
Indexes	Total size of indexed column	75% of the database block size minus some overhead
Partitions	Maximum length of linear partitioning key	4 KB - overhead
Partitions	Maximum number of columns in partition key	16 columns
Partitions	Maximum number of partitions allowed per table or index	1024K - 1
Rows	Maximum number per table	Unlimited
Stored Packages	Maximum size	Approximately 6,000,000 lines of code.See Also: Oracle Database PL/SQL Language Reference for details
Subpartitions	Maximum number of subpartitions in a composite partitioned table	1024K - 1
Subqueries	Maximum levels of subqueries in a SQL statement	Unlimited in the FROM clause of the top-level query255 subqueries in the WHERE clause
System Change Numbers (SCNs)	Maximum	281,474,976,710,656, which is 281 trillion SCNs
Tables	Maximum per clustered table	32 tables
Tables	Maximum per database	UnlimitedOracle does not define a limit on the number of tables per database. However, tables are subject to the limit on the maximum number of dictionary-managed database objects allowed per database. See the entry for "Dictionary-managed database objects" in this table.
Trigger Cascade Limit	Maximum value	Operating system-dependent, typically 32
Users and Roles	Maximum	2,147,483,638

进程和运行限制

Item	Type of Limit	Limit Value
Instances per database	Maximum number of cluster database instances per database	Operating system-dependent
Locks	Row-level	Unlimited
Locks	Distributed Lock Manager	Operating system dependent
SGA size	Maximum value	Operating system-dependent; typically 2 to 4 GB for 32-bit operating systems, and > 4 GB for 64-bit operating systems
Advanced Queuing Processes	Maximum per instance	10
Job Queue Processes	Maximum per instance	1000
I/O Slave Processes	Maximum per background process (DBWR, LGWR, and so on)	15
I/O Slave Processes	Maximum per Backup session	15
Sessions	Maximum per instance	216; limited by the PROCESSES and SESSIONS initialization parameters. 216 is 65536.
Global Cache Service Processes	Maximum per instance	10
Shared Servers	Maximum per instance	Unlimited within constraints set by the PROCESSES and SESSIONS initialization parameters, for instance
Dispatchers	Maximum per instance	Unlimited within constraints set by PROCESSES and SESSIONS initialization parameters, for instance
Parallel Execution Slaves	Maximum per instance	Unlimited within constraints set by PROCESSES and SESSIONS initialization parameters, for instance
Backup Sessions	Maximum per instance	Unlimited within constraints set by PROCESSES and SESSIONS initialization parameters, for instance
Services	Maximum per instance	8200