Oracle限制

 

Author：skate

Time：2009-03-05

---

昨天和一个朋友聊天，谈到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:

·         Datatype Limits

·         Physical Database Limits

·         Logical Database Limits

·         Process and Runtime Limits

 

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, 1000^Foot 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, 1000^Footref 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, 1000^Footref 1).

NUMBER

999...(38 9's) x10¹²⁵ maximum value

Can be represented to full 38-digit precision (the mantissa).

-999...(38 9's) x10¹²⁵ 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 2²² - 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)

Database files

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 2²² 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 ( 2³² ) 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 (2²²) 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---