Oracle Limits
This document was uploaded by user and they confirmed that they have the permission to share it. If you are author or own the copyright of this book, please report to us by using this DMCA report form. Report DMCA
Overview
Download & View Oracle Limits as PDF for free.
More details
- Words: 1,078
- Pages: 9
Physical Database Limits Item Database Block Size
Type of Limit Minimum
Maximum
Database Blocks
Limit Value 2048 bytes; must be a multiple of operating system physical block size Operating system dependent; never more than 32 KB
Minimum in initial extent of a segment.
2 blocks
Maximum per datafile
Platform dependent; typically 222-1 blocks
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 Maximum per database
Operating system dependent; usually 1022
Database extents
Maximum
2 GB, regardless of the maximum file size allowed by the operating system
Database file size
Maximum
Operating system dependent. Limited by maximum operating system file size; typically 222 or 4M Blocks.
MAXEXTENTS
Default value
Derived from tablespace default storage or DB_BLOCK_ SIZE initialization parameter
Controlfiles
Database files
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.
Redo Log Files
Maximum
Unlimited
Maximum number of logfiles
Limited by value of LOG_FILES initialization parameter or 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 Maximum size
50 KB Operating system limit; typically 2 GB
Tablespaces
Maximum number per database
64 K Number of tablespaces cannot exceed the number of database files, as each tablespace must include at least one file
Item GROUP BY clause
Type of Limit Maximum length
Limit Value The GROUP BY expression and all of the nondistinct aggregates functions (for example, SUM, AVG) must fit within a single database block.
Indexes
Maximum per table total size of indexed column
Unlimited 75% of the database block size minus some overhead
Columns
Per table Per index (or clustered index) Per bitmapped index
1000 columns maximum 32 columns maximum 30 columns maximum
Logical Database Limits
Constraints Subqueries
Maximum per column Maximum levels of subqueries in a SQL statement query
Unlimited 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
64 K-1 partitions
Rollback Segments
Maximum number per database No limit; limited within a session by the MAX_ ROLLBACK_SEGMENTS initialization parameter
Rows
Maximum number per table
Unlimited
SQL Statement Length
Maximum length of statements
64 K maximum; particular tools may impose lower limits
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
Tables
Maximum per clustered tabls Maximum per database
2,147,483,638 32 tables Unlimited
Process and Runtime Limits Item Instances per database
Type of Limit Maximum number of OPS instances per database
Limit Value Operating system-dependent
Locks
Row-level
Unlimited
Distributed Lock Manag
er
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
Operating system dependent
Sessions
Maximum per instance
32K; limited by thePROCESSESandSESSIONS initialization parameters
LCK Processes
Maximum per instance
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 byPROCESSES and SESSIONS initialization parameters, for instance
10
Parallel Execution Slaves
Maximum per instance
Unlimited within constraints set byPROCESSES and SESSIONS initialization parameters, for instance
Backup Sessions
Maximum per instance
Unlimited within constraints set byPROCESSES and SESSIONS initialization parameters, for instance
Logical Database Limits Item GROUP BY clause
Type of Limit Maximum length
Indexes
Maximum per table total size of indexed column
Columns
Per table Per index (or clustered index) Per bitmapped index
Constraints Subqueries
Maximum per column Maximum levels of subqueries in a SQL statement query
Partitions
Maximum length of linear partitioning key Maximum number of columns in partition key Maximum number of partitions allowed per table or index
Rollback Segments
Maximum number per database
Rows
Maximum number per table
SQL Statement Length
Maximum length of statements
Stored Packages
Maximum size
Trigger Cascade Limit
Maximum value
Users and Roles
Maximum
Tables
Maximum per clustered tabls Maximum per database
Limit Value The GROUP BY expression and all of the nondistinct aggregates functions (for example, SUM, AVG) must fit within a single database block. Unlimited 75% of the database block size minus some overhead 1000 columns maximum 32 columns maximum 30 columns maximum Unlimited Unlimited in the FROM clause of the top-level query 255 subqueries in the WHERE clause 4 KB - overhead
16 columns
64 K-1 partitions
No limit; limited within a session by the MAX_ ROLLBACK_SEGMENTS initialization parameter Unlimited 64 K maximum; particular tools may impose lower limits 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 Operating system-dependent, typically 32 2,147,483,638 32 tables Unlimited
Process and Runtime Limits Item Instances per database
Type of Limit Maximum number of OPS instances per database
Locks
Row-level Distributed Lock Manag
SGA size
Maximum value
Advanced Queuing Processes
Maximum per instance
Job Queue Processes
Maximum per instance
I/O Slave Processes
Maximum per background process (DBWR, LGWR, etc) Maximum per Backup session
Sessions
Maximum per instance
LCK Processes
Maximum per instance
Shared Servers
Maximum per instance
Dispatchers
Maximum per instance
Parallel Execution Slaves
Maximum per instance
Backup Sessions
Maximum per instance
Limit Value Operating system-dependent
Unlimited er
Operating system dependent
Operating system-dependent; typically 2 to 4 GB for 32-bit operating systems, and > 4 GB for 64-bit operating systems 10
1000 15
15 32K; limited by thePROCESSESandSESSIONS initialization parameters 10 Unlimited within constraints set by the PROCESSES and SESSIONS initialization parameters, for instance Unlimited within constraints set byPROCESSES and SESSIONS initialization parameters, for instance Unlimited within constraints set byPROCESSES and SESSIONS initialization parameters, for instance Unlimited within constraints set byPROCESSES and SESSIONS initialization parameters, for instance
Type of Limit Minimum
Maximum
Database Blocks
Limit Value 2048 bytes; must be a multiple of operating system physical block size Operating system dependent; never more than 32 KB
Minimum in initial extent of a segment.
2 blocks
Maximum per datafile
Platform dependent; typically 222-1 blocks
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 Maximum per database
Operating system dependent; usually 1022
Database extents
Maximum
2 GB, regardless of the maximum file size allowed by the operating system
Database file size
Maximum
Operating system dependent. Limited by maximum operating system file size; typically 222 or 4M Blocks.
MAXEXTENTS
Default value
Derived from tablespace default storage or DB_BLOCK_ SIZE initialization parameter
Controlfiles
Database files
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.
Redo Log Files
Maximum
Unlimited
Maximum number of logfiles
Limited by value of LOG_FILES initialization parameter or 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 Maximum size
50 KB Operating system limit; typically 2 GB
Tablespaces
Maximum number per database
64 K Number of tablespaces cannot exceed the number of database files, as each tablespace must include at least one file
Item GROUP BY clause
Type of Limit Maximum length
Limit Value The GROUP BY expression and all of the nondistinct aggregates functions (for example, SUM, AVG) must fit within a single database block.
Indexes
Maximum per table total size of indexed column
Unlimited 75% of the database block size minus some overhead
Columns
Per table Per index (or clustered index) Per bitmapped index
1000 columns maximum 32 columns maximum 30 columns maximum
Logical Database Limits
Constraints Subqueries
Maximum per column Maximum levels of subqueries in a SQL statement query
Unlimited 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
64 K-1 partitions
Rollback Segments
Maximum number per database No limit; limited within a session by the MAX_ ROLLBACK_SEGMENTS initialization parameter
Rows
Maximum number per table
Unlimited
SQL Statement Length
Maximum length of statements
64 K maximum; particular tools may impose lower limits
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
Tables
Maximum per clustered tabls Maximum per database
2,147,483,638 32 tables Unlimited
Process and Runtime Limits Item Instances per database
Type of Limit Maximum number of OPS instances per database
Limit Value Operating system-dependent
Locks
Row-level
Unlimited
Distributed Lock Manag
er
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
Operating system dependent
Sessions
Maximum per instance
32K; limited by thePROCESSESandSESSIONS initialization parameters
LCK Processes
Maximum per instance
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 byPROCESSES and SESSIONS initialization parameters, for instance
10
Parallel Execution Slaves
Maximum per instance
Unlimited within constraints set byPROCESSES and SESSIONS initialization parameters, for instance
Backup Sessions
Maximum per instance
Unlimited within constraints set byPROCESSES and SESSIONS initialization parameters, for instance
Logical Database Limits Item GROUP BY clause
Type of Limit Maximum length
Indexes
Maximum per table total size of indexed column
Columns
Per table Per index (or clustered index) Per bitmapped index
Constraints Subqueries
Maximum per column Maximum levels of subqueries in a SQL statement query
Partitions
Maximum length of linear partitioning key Maximum number of columns in partition key Maximum number of partitions allowed per table or index
Rollback Segments
Maximum number per database
Rows
Maximum number per table
SQL Statement Length
Maximum length of statements
Stored Packages
Maximum size
Trigger Cascade Limit
Maximum value
Users and Roles
Maximum
Tables
Maximum per clustered tabls Maximum per database
Limit Value The GROUP BY expression and all of the nondistinct aggregates functions (for example, SUM, AVG) must fit within a single database block. Unlimited 75% of the database block size minus some overhead 1000 columns maximum 32 columns maximum 30 columns maximum Unlimited Unlimited in the FROM clause of the top-level query 255 subqueries in the WHERE clause 4 KB - overhead
16 columns
64 K-1 partitions
No limit; limited within a session by the MAX_ ROLLBACK_SEGMENTS initialization parameter Unlimited 64 K maximum; particular tools may impose lower limits 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 Operating system-dependent, typically 32 2,147,483,638 32 tables Unlimited
Process and Runtime Limits Item Instances per database
Type of Limit Maximum number of OPS instances per database
Locks
Row-level Distributed Lock Manag
SGA size
Maximum value
Advanced Queuing Processes
Maximum per instance
Job Queue Processes
Maximum per instance
I/O Slave Processes
Maximum per background process (DBWR, LGWR, etc) Maximum per Backup session
Sessions
Maximum per instance
LCK Processes
Maximum per instance
Shared Servers
Maximum per instance
Dispatchers
Maximum per instance
Parallel Execution Slaves
Maximum per instance
Backup Sessions
Maximum per instance
Limit Value Operating system-dependent
Unlimited er
Operating system dependent
Operating system-dependent; typically 2 to 4 GB for 32-bit operating systems, and > 4 GB for 64-bit operating systems 10
1000 15
15 32K; limited by thePROCESSESandSESSIONS initialization parameters 10 Unlimited within constraints set by the PROCESSES and SESSIONS initialization parameters, for instance Unlimited within constraints set byPROCESSES and SESSIONS initialization parameters, for instance Unlimited within constraints set byPROCESSES and SESSIONS initialization parameters, for instance Unlimited within constraints set byPROCESSES and SESSIONS initialization parameters, for instance
Related Documents
Oracle Limits
October 2019 38
Oracle Database Limits
May 2020 14
Limits
April 2020 50
Limits, Continuity - Trigonometric Limits
May 2020 47
Oracle
June 2020 26
Oracle
October 2019 44More Documents from ""
Oracle V$views
October 2019 30
Equip7.xlsx
June 2020 11
Use Explain Plan And Tkprof To Tune Your Applications
October 2019 22
Natural Pain Killer.pdf
June 2020 10
Oracle Limits
October 2019 38