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

Thread: Database Limits

  1. #1
    Join Date
    Nov 2002
    Location
    New Delhi, INDIA
    Posts
    1,796

    Database Limits

    For those who find it difficult to find in Reference Manuals

    Physical Database Limits
    Code:
    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
                                                            recommended
    
                            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
                                                            statement
    
                                                            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
    Code:
    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
                                                            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    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
    Code:
    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
    Processes
    
    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
                                                               instance
    
    Parallel                   Maximum per instance             Unlimited within constraints set byPROCESSES
    Execution Slaves                                            and SESSIONS initialization parameters, for
                                                                instance
    
    Backup Sessions            Maximum per instance             Unlimited within constraints set byPROCESSES
                                                                and SESSIONS initialization parameters, for
                                                                instance
    HTH

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

    Amar's Blog  Get Firefox!

  2. #2
    Join Date
    Mar 2006
    Posts
    3

    Smile Minimum size of redo is not 64k

    SQL> alter database add logfile group 3 ('/oracle9i/app/oracle/admin/db06/Log/lg5.rdo','/oracle9i/app/oracle/admin/db06/Backup/lg6.rdo') size 64k;

    Database altered.

    SQL> ed
    Wrote file afiedt.buf

    1* alter database add logfile group 4 ('/oracle9i/app/oracle/admin/db06/Log/lg7.rdo','/oracle9i/app/oracle/admin/db06/Backup/lg8.rdo') size 49k
    SQL> /

    Database altered.

    SQL> ed
    Wrote file afiedt.buf

    1* alter database add logfile group 5 ('/oracle9i/app/oracle/admin/db06/Log/lg9.rdo','/oracle9i/app/oracle/admin/db06/Backup/lg10.rdo') size 24k
    SQL> /

    Database altered.
    SQL> ed
    Wrote file afiedt.buf

    1* alter database add logfile group 5 ('/oracle9i/app/oracle/admin/db06/Log/lg16.rdo','/oracle9i/app/oracle/admin/db06/Backup/lg15.rdo') size 2k
    SQL> /

    Database altered.

  3. #3
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,334
    and?

  4. #4
    Join Date
    Aug 2006
    Posts
    1

    Maximum Oracle 9i SQL statement length

    I have read in the Oracle Ref Manual that the limit is 64Kb but other tools may change this limit. Does that mean it is configurable? I am trying to determine in a production environment if we are hitting this limit. Do JDBC drivers allow it to be configured on AIX and Windows if so how?

    Stephen

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