Search This Blog

Saturday, September 10, 2011

Oracle Logical Database Limits from 7 to 11gr2 --- worth to know...

Between Oracle 7 to 8 "column" item upgraded.

Column Limits in Oracle 7
LONG columns
index (or cluster index) table expression list view definition16 columns maximum 254 columns maximum 254 columns maximum 254 columns maximum
table1 LONG column per table
Oracle 8 --- Logical Database Limit
item Type Limit 
GROUP BY clause 
Maximum length 
The GROUP BY expression and all of the nondistinct aggregates functions (e.g., SUMAVG) must fit within a single database block. 
Maximum per table 
total size of indexed column 
40% of the database block size minus some overhead. 
Per table 
1000 columns maximum 
Per index (or clustered index) 
32 columns maximum 

Per bitmapped index 
30 columns maximum 
Maximum per column 
Nested Queries 
Maximum number 
Maximum length of linear partitioning key 
4KB - overhead 
Maximum number of columns in partition key 
16 columns 
Maximum number of partitions allowed per table or index 
64K-1 partitions 
Rollback Segments 
Maximum number per database 
No limit; limited within a session by the MAX_ROLLBACK_SEGMENTS initialization parameter 
Maximum number per table 
SQL Statement Length 
Maximum length of statements 
64K 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 per clustered table 
32 tables 
Maximum per database 

Oracle 9ir1 --- Logical Database Limit
In this release the below new items are introduced Or improved.
Maximum levels of subqueries in a SQL statement 
Unlimited in the FROM clause of the top-level query
255 subqueries in the WHERE clause 

Oracle 10gr2 --- Logical Database Limit
In this release the below new items are introduced Or improved.
CREATE MATERIALIZED VIEW definitionMaximum size64K Bytes

The limit on how long a SQL statement can be depends on many factors, including database configuration, disk space, and memory
When an object instance exists in memory, there is no fixed limit on the number of attributes in the object. But the maximum total amount of memory consumed by an object instance is 4 GB.When an object instance is inserted into a table, the attributes are exploded into separate columns in the table, and the Oracle 1000-column limit applies.

Oracle 11gr1 --- Logical Database Limit
NO changes between 10gr2 to 11gr1

Oracle 11gr2 --- Logical Database Limit
NO changes between 11gr1 to 11gr2

NOTE: Please refer oracle official documentation for more information :

No comments: