Oracle 绝对和相对文件编号研究
概述
数据库中一些数据文件的绝对文件编号和相对文件编号具有相同的值，但某些数据文件不会。本文档介绍数据库如何分配数据文件的绝对和相对文件编号。
绝对文件编号
绝对文件号在整个数据库中唯一，可以通过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 |
作者：bicewow —— bicewow
出处：http://www.cnblogs.com/bicewow/
本文版权归作者和博客园共有，欢迎转载，但未经作者同意必须保留此段声明，且在文章页面明显位置给出原文连接，否则保留追究法律责任的权利。
