Physical Database Limits(11g)
| 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 222 - 1 blocks |
| Controlfiles | Number of control files | 1 minimum; 2 or more (on separate devices) strongly recommended |
| Controlfiles | 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 |
| Database files | Maximum per database | 65533 May be less on some operating systems Limited also by size of database blocks and by the |
| 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; typically 222 or 4 MB blocks |
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 GB |
| 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 ( 232 ) 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 (222) blocks. |
| External Tables file | Maximum size | Dependent on the operating system. An external table can be composed of multiple files. |
Datatype Limits
Datatypes Limit Comments BFILEMaximum 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. BLOBMaximum 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). CHARMaximum size: 2000 bytes None CHAR VARYINGMaximum size: 4000 bytes None CLOBMaximum 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). Literals (characters or numbers in SQL or PL/SQL – more) Maximum size: 4000 characters None LONGMaximum size: 2 GB – 1 Only one LONG column is allowed per table. NCHARMaximum size: 2000 bytes None NCHAR VARYINGMaximum size: 4000 bytes None NCLOBMaximum 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). NUMBER999…(38 9’s) x10125 maximum value -999…(38 9’s) x10125minimum 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 RAWMaximum size: 2000 bytes None VARCHARMaximum size: 4000 bytes None VARCHAR2Maximum size: 4000 bytes None
Logical Database Limits
Item Type of Limit Limit Value GROUP BY clauseMaximum 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 Indexes Total size of indexed column 75% of the database block size minus some overhead Columns Per table 1000 columns maximum Columns Per index (or clustered index) 32 columns maximum Columns 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 – overheadPartitions 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 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. Trigger Cascade Limit Maximum value Operating system-dependent, typically 32 Users and Roles Maximum 2,147,483,638Tables Maximum per clustered table 32 tables Tables Maximum per database Unlimited
Physical Database Limits
Item Type of Limit Limit Value Database Block Size Minimum 2048 bytes; must be a multiple of operating system physical block sizeDatabase 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 222 – 1 blocks Controlfiles Number of control files 1 minimum; 2 or more (on separate devices) strongly recommendedControlfiles 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 Database files Maximum per database 65533 May be less on some operating systems Limited also by size of database blocks and by theDB_FILES initialization parameter for a particular instance Database extents (more) 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; typically 222 or 4 MB blocks MAXEXTENTSDefault value Derived from tablespace default storage orDB_BLOCK_SIZE initialization parameter MAXEXTENTSMaximum Unlimited Redo Log Files Maximum number of logfiles Limited by value of MAXLOGFILES parameter in theCREATE DATABASE statement Control 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 MBRedo Log File Size 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 fileBigfile 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 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 (222) blocks. External Tables file Maximum size Dependent on the operating system.An external table can be composed of multiple files.
Process and Runtime Limits
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 10Job Queue Processes Maximum per instance 1000I/O Slave Processes Maximum per background process (DBWR, LGWR, etc.) 15 I/O Slave Processes Maximum per Backup session 15 Sessions Maximum per instance 32 KB; limited by the PROCESSES and SESSIONSinitialization parametersGlobal Cache Service Processes Maximum per instance 10Shared Servers Maximum per instance Unlimited within constraints set by thePROCESSES and SESSIONS initialization parameters, for instance Dispatchers Maximum per instance Unlimited within constraints set by PROCESSESand SESSIONS initialization parameters, for instance Parallel Execution Slaves Maximum per instance Unlimited within constraints set by PROCESSESand SESSIONS initialization parameters, for instance Backup Sessions Maximum per instance Unlimited within constraints set by PROCESSESand SESSIONS initialization parameters, for instance
PL/SQL Compiler Limits
Item Limit bind variables passed to a program unit 32768 exception handlers in a program unit 65536 fields in a record 65536 levels of block nesting 255 levels of record nesting 32 levels of subquery nesting 254 levels of label nesting 98 levels of nested collections no predefined limit magnitude of a PLS_INTEGER or BINARY_INTEGERvalue -2147483648..2147483647 number of formal parameters in an explicit cursor, function, or procedure 65536 objects referenced by a program unit 65536 precision of a FLOAT value (binary digits) 126 precision of a NUMBER value (decimal digits) 38 precision of a REAL value (binary digits) 63 size of an identifier (characters) 30 size of a string literal (bytes) 32767 size of a CHAR value (bytes) 32767 size of a LONG value (bytes) 32760 size of a LONG RAW value (bytes) 32760 size of a RAW value (bytes) 32767 size of a VARCHAR2 value (bytes) 32767 size of an NCHAR value (bytes) 32767 size of an NVARCHAR2 value (bytes) 32767 size of a BFILE value (bytes) 4G * value of DB_BLOCK_SIZE parameter size of a BLOB value (bytes) 4G * value of DB_BLOCK_SIZE parameter size of a CLOB value (bytes) 4G * value of DB_BLOCK_SIZE parameter size of an NCLOB value (bytes) 4G * value of DB_BLOCK_SIZE parameter
BFILEBFILEs 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.BLOBDB_BLOCK_SIZE initialization parameter (8 TB to 128 TB)CHARCHAR VARYINGCLOBDB_BLOCK_SIZE initialization parameter (8 TB to 128 TB)LONGLONG column is allowed per table.NCHARNCHAR VARYINGNCLOBDB_BLOCK_SIZE initialization parameter (8 TB to 128 TB)NUMBERRAWVARCHARVARCHAR2GROUP BY clauseGROUP BY expression and all of the nondistinct aggregate functions (for example,SUM, AVG) must fit within a single database block.FROM clause of the top-level query 255 subqueries in the WHERE clause4 KB – overhead2000 to 3000 lines of code.322,147,483,6382048 bytes; must be a multiple of operating system physical block size32 KB1 minimum; 2 or more (on separate devices) strongly recommended20,000 x (database block size)1022DB_FILES initialization parameter for a particular instance4 GB * physical block size (with K/M modifier); 4 GB (without K/M modifier)2 GB * physical block size (with K/M modifier); 2 GB (without K/M modifier)4 MB blocksMAXEXTENTSDB_BLOCK_SIZE initialization parameterMAXEXTENTSMAXLOGFILES parameter in theCREATE DATABASE statement Control file can be resized to allow more entries; ultimately an operating system limit4 MB2 GB64 K Number of tablespaces cannot exceed the number of database files because each tablespace must include at least one file2 to 4 GB for 32-bit operating systems, and > 4 GB for 64-bit operating systems10100032 KB; limited by the PROCESSES and SESSIONSinitialization parameters10PROCESSES and SESSIONS initialization parameters, for instancePROCESSESand SESSIONS initialization parameters, for instancePROCESSESand SESSIONS initialization parameters, for instancePROCESSESand SESSIONS initialization parameters, for instancePLS_INTEGER or BINARY_INTEGERvalueFLOAT value (binary digits)NUMBER value (decimal digits)REAL value (binary digits)CHAR value (bytes)LONG value (bytes)LONG RAW value (bytes)RAW value (bytes)VARCHAR2 value (bytes)NCHAR value (bytes)NVARCHAR2 value (bytes)BFILE value (bytes)BLOB value (bytes)CLOB value (bytes)NCLOB value (bytes)
No comments:
Post a Comment