DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Database Limits

Threaded View

  1. #1
    Join Date
    Nov 2002
    New Delhi, INDIA

    Database Limits

    For those who find it difficult to find in Reference Manuals

    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              2 blocks
                            extent of
                            a segment.
                            Maximum per datafile         	Platform dependent; 
                                                            typically 222-1 blocks
    Controlfiles            Number of control files      	1 minimum; 2 or more (on separate devices) strongly
                            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                         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
                            Maximum                         Unlimited
    Redo Log Files          Maximum number of               Limited by value of LOG_FILES initialization parameter
                            logfiles                        or MAXLOGFILES parameter in the CREATE DATABASE
                                                            Control file can be resized to allow more entries;
                                                            ultimately an operating system limit
                            Maximum number of               Unlimited
                            logfiles per group
    Redo Log File Size      Minimum size                    50 KB
                            Maximum size                    Operating system limit; typically 2 GB
    Tablespaces             Maximum number per              64 K
                            database                        Number of tablespaces cannot exceed the number of
                                                            database files, as each tablespace must include at least
                                                            one file
    Logical Database Limits
    Item                    Type of Limit                   Limit Value
    GROUP BY clause         Maximum length                  The GROUP BY expression and all of the
                                                            nondistinct aggregates functions (for example,
                                                            SUM, AVG) must fit within a single database
    Indexes                 Maximum per table               Unlimited
                            total size of indexed column    75% of the database block size minus some
    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    Unlimited in the FROM clause of the top-level
                            in a SQL statement              query
                                                            255 subqueries in the WHERE clause
    Partitions              Maximum length of linear        4 KB - overhead
                            partitioning key
                            Maximum number of columns       16 columns
                            in partition key
                            Maximum number of partitions    64 K-1 partitions
                            allowed per table or index
    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                          2,147,483,638
    Tables                 Maximum per clustered table      32 tables
                           Maximum per database             Unlimited
    Process and Runtime Limits
    Item                    Type of Limit                   Limit Value
    Instances per database 	  Maximum number of OPS           Operating system-dependent
                               instances per database
    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           Maximum per instance            10
    Job Queue Processes        Maximum per instance            1000
    I/O Slave Processes        Maximum per background          15
                               process (DBWR, LGWR, etc.)
                               Maximum per Backup session      15
    Sessions                   Maximum per instance            32K; limited by thePROCESSESandSESSIONS
                                                               initialization parameters
    LCK 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 byPROCESSES
                                                               and SESSIONS initialization parameters, for
    Parallel                   Maximum per instance             Unlimited within constraints set byPROCESSES
    Execution Slaves                                            and SESSIONS initialization parameters, for
    Backup Sessions            Maximum per instance             Unlimited within constraints set byPROCESSES
                                                                and SESSIONS initialization parameters, for

    Last edited by adewri; 02-28-2003 at 12:53 PM.
    "There is a difference between knowing the path and walking the path."

    Amar's Blog  Get Firefox!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Click Here to Expand Forum to Full Width