Author:skate
Time:2009-03-05
Author:skate
Time:2009-03-05
昨天和一个朋友聊天,谈到oracle的限制,有点模糊了,于是今天仔细查下,记录在这
昨天和一个朋友聊天,谈到oracle的限制,有点模糊了,于是今天仔细查下,记录在这
oracle 9i表空间的数据文件个数上限是 1022个,系统默认是200个,通过参数db_files来控制
但数据库总共有65533个数据文件,这就看表空间个数和数据文件个数你自己做个选择了
测试如下:
SQL> show parameter db_files;
NAME TYPE
------------------------------------ ----------------------
VALUE
------------------------------
db_files integer
205
SQL> alter system set db_files=2000 scope=spfile;
系统已更改。
SQL> alter system set db_files=4000 scope=spfile;
系统已更改。
这么大也可以啊
SQL> alter system set db_files=10000 scope=spfile;
系统已更改。
SQL> alter system set db_files=100000000 scope=spfile;
系统已更改。
还可以,难道就没有限制吗:继续....
SQL> alter system set db_files=100000000000 scope=spfile;
alter system set db_files=100000000000 scope=spfile
*
ERROR 位于第 1 行:
ORA-02017: 要求整数值
终于报错了,查了下整数的范围是[-2^31, 2^31-1]。即[-2147483648, 2147483647]。
SQL> alter system set db_files=10000000000 scope=spfile;
alter system set db_files=10000000000 scope=spfile
*
ERROR 位于第 1 行:
ORA-02017: 要求整数值
SQL> alter system set db_files=1000000000 scope=spfile;
系统已更改。
SQL> alter system set db_files=2000000000 scope=spfile;
系统已更改。
SQL> alter system set db_files=3000000000 scope=spfile;
alter system set db_files=3000000000 scope=spfile
*
ERROR 位于第 1 行:
ORA-00600: 内部错误代码,参数: [925], [db_files], [-1294967296], [], [], [],
[], []
SQL> alter system set db_files=2000000000 scope=spfile;
系统已更改。
需要重启数据库,才能生效,即:recreate controlfile
关闭数据库
SQL> shutdown immediate;
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
SQL> startup
ORA-01131: DB_FILES system parameter value 2000000000 exceeds limit of 65534
从这里可以得到两个信息:
1. 对于alter system , oracle不验证其值的有效性
2. 得到了数据文件个数的最大值
结果数据无法启动了
我用pfile启动数据库
SQL> startup pfile='C:/oracle/admin/ora9i/pfile/init.ora.98200816645'
ORACLE 例程已经启动。
Total System Global Area 135338868 bytes
Fixed Size 453492 bytes
Variable Size 109051904 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes
数据库装载完毕。
数据库已经打开。
SQL> create spfile from pfile;
create spfile from pfile
*
ERROR 位于第 1 行:
ORA-01078: 处理系统参数失败
LRM-00109: could not open parameter file
'C:/ORACLE/PRODUCT/9.2.0.1/DB_1/DATABASE/INITORA9I.ORA'
SQL> create spfile from pfile;
create spfile from pfile
*
ERROR 位于第 1 行:
ORA-01078: 处理系统参数失败
LRM-00109: could not open parameter file
'C:/ORACLE/PRODUCT/9.2.0.1/DB_1/DATABASE/INITORA9I.ORA'
SQL> create spfile from pfile='C:/oracle/admin/ora9i/pfile/init.ora.98200816645';
文件已创建。
SQL> alter system set db_files=65534 scope=spfile;
系统已更改。
SQL> show parameter db_files
NAME TYPE
------------------------------------ ----------------------
VALUE
------------------------------
db_files integer
200
SQL> shutdown immediate;
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
SQL> startup
ORACLE 例程已经启动。
Total System Global Area 579935836 bytes
Fixed Size 454236 bytes
Variable Size 553648128 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes
数据库装载完毕。
数据库已经打开。
SQL> show parameter db_files;
NAME TYPE
------------------------------------ ----------------------
VALUE
------------------------------
db_files integer
65534
SQL>
A Database Limits
This appendix lists the limits of values associated with database functions and objects. Limits exist on several levels in the database. There is usually a hard-coded limit in the database that cannot be exceeded. This value may be further restricted for any given operating system.
Database limits are divided into four categories:
http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/limits.htm#REFRN004
Datatype Limits
Datatypes | Limit | Comments |
BFILE | Maximum size: 4 GB Maximum size of a file name: 255 characters Maximum size of a directory name: 30 characters Maximum 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 |
|
CHAR VARYING | Maximum size: 4000 bytes |
|
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, 1000Footref 1). |
Literals (characters or numbers in SQL or PL/SQL) | Maximum size: 4000 characters |
|
LONG | Maximum size: 2 GB - 1 | Only one LONG column is allowed per table. |
NCHAR | Maximum size: 2000 bytes |
|
NCHAR VARYING | Maximum size: 4000 bytes |
|
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, 1000Footref 1). |
NUMBER | 999...(38 9's) x10125 maximum value | Can be represented to full 38-digit precision (the mantissa). |
-999...(38 9's) x10125 minimum value | Can be represented to full 38-digit precision (the mantissa). | |
Precision | 38 significant digits |
|
RAW | Maximum size: 2000 bytes |
|
VARCHAR | Maximum size: 4000 bytes |
|
VARCHAR2 | Maximum size: 4000 bytes |
|
Physical Database Limits
Item | Type of Limit | Limit Value |
Database Block Size | Minimum | 2048 bytes; must be a multiple of operating system physical block size |
Maximum | Operating system dependent; never more than 32 KB | |
Database Blocks | Minimum in initial extent of a segment. | 2 blocks |
Maximum per datafile | Platform dependent; typically 222 - 1 blocks | |
Controlfiles | Number of control files | 1 minimum; 2 or more (on separate devices) strongly recommended |
Size of a control file | Dependent on operating system and database creation options; maximum of 20,000 x (database block size) | |
Maximum per tablespace | Operating system dependent; usually 1022 | |
Maximum per database | 65533 May be less on some operating systems Limited 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) |
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; typically 222 or 4 MB blocks |
MAXEXTENTS | Default value | Derived from tablespace default storage or DB_BLOCK_SIZE initialization parameter |
Maximum | Unlimited | |
Redo Log Files | Maximum number of logfiles | Limited by value of MAXLOGFILES parameter in the CREATE DATABASE statement Control file can be resized to allow more entries; ultimately an operating system limit |
Maximum number of logfiles per group | Unlimited | |
Redo Log File Size | Minimum size | 50 KB |
Maximum size | Operating system limit; typically 2 GB | |
Tablespaces | Maximum number per database | 64 K Number 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 ( 232 ) blocks. The maximum size of the single datafile or tempfile is 128 terabytes (TB) for a tablespace with 32K blocks and 32TB for a tablespace with 8K 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 (222) blocks. |
External Tables file | Maximum size | Dependent on the operating system. An external table can be composed of multiple files. |
Logical Database Limits
Item | Type | Limit |
CREATE MATERIALIZED VIEW definition | Maximum size | 64K Bytes |
GROUP BY clause | Maximum length | The GROUP BY expression and all of the nondistinct aggregate functions (for example, SUM, AVG) must fit within a single database block. |
Indexes | Maximum per table | Unlimited |
total size of indexed column | 75% of the database block size minus some overhead | |
Columns | Per table | 1000 columns maximum |
Per index (or clustered index) | 32 columns maximum | |
Per bitmapped index | 30 columns maximum | |
Constraints | Maximum per column | Unlimited |
Subqueries | Maximum levels of subqueries in a SQL statement | Unlimited in the FROM clause of the top-level query 255 subqueries in the WHERE clause |
Partitions | Maximum length of linear partitioning key | 4 KB - overhead |
Maximum number of columns in partition key | 16 columns | |
Maximum number of partitions allowed per table or index | 1024K - 1 | |
Rows | Maximum number per table | Unlimited |
Stored Packages | Maximum size | PL/SQL and Developer/2000 may have limits on the size of stored procedures they can call. The limits typically range from 2000 to 3000 lines of code. See Also: Your PL/SQL or Developer/2000 documentation for details |
Trigger Cascade Limit | Maximum value | Operating system-dependent, typically 32 |
Users and Roles | Maximum | 2,147,483,638 |
Tables | Maximum per clustered table | 32 tables |
Maximum per database | Unlimited |
Process and Runtime Limits
Item | Type | Limit |
Instances per database | Maximum number of cluster database instances per database | Operating system-dependent |
Locks | Row-level | Unlimited |
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, etc.) | 15 |
Maximum per Backup session | 15 | |
Sessions | Maximum per instance | 32 KB; limited by the PROCESSES and SESSIONS initialization parameters |
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 |
Oracle中数据文件大小的限制
Oracle数据文件的大小存在一个内部限制,这个限制是:
每个数据文件最多只能包含2^22-1个数据块。
这个限制也就直接导致了每个数据文件的最大允许大小。
在2K Block_size下,数据文件最大只能达到约8G
在32K的Block_size下,数据文件最大只能达到约16*8G的大小。
这个限制是由于Oracle的Rowid中使用22位来代表Block号,这22位最多只能代表2^22-1个数据块。
为了扩展数据文件的大小,Oracle10g中引入了大文件表空间,在大文件表空间下,Oracle使用32位来代表Block号,也就是说,在新的技术下,大文件表空间下每个文件最多可以容纳4G个Block。
那么也就是说当Block_size为2k时,数据文件可以达到8T 。
当block_size为32K时,数据文件可以达到128T。
上周在做2K block_size测试时,第一次遇到了这个限制:
SQL> alter tablespace eygle add datafile 'f:/eygle02.dbf' size 8192M;
alter tablespace eygle add datafile 'f:/eygle02.dbf' size 8192M
*
ERROR 位于第 1 行:
ORA-01144: 文件大小 (4194304 块) 超出 4194303 块的最大数
缩减一点,最后创建成功:
SQL> alter tablespace eygle add datafile 'f:/eygle02.dbf' size 8191M reuse;
表空间已更改。
已用时间: 00: 44: 42.08
----end---