Oracle Database Limits(Physical/Logical) for both 10g and 11g

Limitatins for Oracle 10g:

Database limits are divided into four categories:

• Datatype Limits
• Physical Database Limits
• Logical Database Limits
• Process and Runtime Limits
  
  
  
  
  
  
  Data Type Limits:
  
  

  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 theSESSION_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_SIZEinitialization 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_SIZEinitialization 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_SIZEinitialization 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

  ^Footnote 1 The absolute maximum number of columns in a table is 1000. However, when you create an object table (or a relational table with columns of object, nested table, varray, or REF type), Oracle maps the columns of the user-defined types to relational columns, creating in effect hidden columns that count toward the 1000-column limit. For details on how Oracle calculates the total number of columns in such a table,
  
  
  Physical Database Limits:
  
  

  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 of20,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 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 DATABASEstatement 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:
  
  

  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 3000lines 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:
  
  

  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 SESSIONSinitialization parameters, for instance
  Dispatchers	Maximum per instance	Unlimited within constraints set by PROCESSES and SESSIONSinitialization parameters, for instance
  Parallel Execution Slaves	Maximum per instance	Unlimited within constraints set by PROCESSES and SESSIONSinitialization parameters, for instance
  Backup Sessions	Maximum per instance	Unlimited within constraints set by PROCESSES and SESSIONSinitialization parameters, for instance