Re: Some questions about Oracle - HELP

You would have to have the manual. I looked in mine and under Database Limits I have the following:
(The documentation is in html and thus doesn't lend it self very well to copy and paste. It is also asuming you know some of the Oracle architecture and meaning of such things as a tablespace. Basically, the limits are so high it would be highly unusual to be able to exceed them.)

Database Limits
Limits exist on several levels in the database. There is usually a hard-coded limit in the database that cannot be exceeded. This value may be further restricted for any given operating system. For more information on the maximum value of such limits, see your operating system-specific Oracle documentation.

Table 5-1 lists datatype limits.

Table 5-2 lists physical database limits

Table 5-3 lists logical database limits.

Table 5-4 lists process/runtime limits.

Table 5-1: Datatype Limits
Datatypes Limit Comments
BFILE
 maximum size:
4 GB

maximum size of file name:
255 characters

maximum size of directory name:
255 characters

maximum number of open BFILEs:
see comments
 The maximum number of BFILEs is limited by the value of SESSION_MAX_OPEN_FILES,
which is itself limited by the maximum number of open files the operating system will allow.

CHAR
 2000 bytes maximum

CHAR VARYING
 4000 bytes

CLOB
 4 GB maximum
 The number of LOB columns per table is limited only by the maximum number of columns per table (i.e., 1000)

Literals
(characters or numbers in SQL or PL/SQL)  4000 characters maximum

LOB
 maximum size of
4 GB -1 byte

LONG
 231-1 bytes (2 GB) maximum
 Only one LONG column allowed per table

NCHAR
 2000 bytes

NCHAR VARYING
 4000 bytes

NUMBER  999...(38 9's) x10125maximum value
 Can be represented to full 38-digit precision (the mantissa).

-999...(38 9's) x10125minimum value
 Can be represented to full 38-digit precision (the mantissa).

Precision
 38 significant digits

RAW
 2000 bytes maximum

VARCHAR
 4000 bytes maximum

VARCHAR2
 4000 bytes maximum

Table 5-2: Physical Database Limits
Item Type of Limit Limit Value
Database Block Size

 minimum
 2048 bytes; must be a multiple of O/S physical block size

maximum
 O/S-dependent ;
never more than 32 KB

Database Blocks

 minimum in initial extent of a segment  2 blocks

maximum per datafile
 platform dependent;
typically 222 blocks

Controlfiles

 number of controlfiles
 1 minimum: 2 or more (on separate devices) strongly recommended

size of a controlfile
 dependent on O/S and database creation options; maximum of 20,000 x (database block size)

Database files
 maximum per tablespace
 O/S 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 init parameter for a particular instance

Database file size
 maximum
 O/S dependent, limited by maximum O/S file size; typically 222 or 4M blocks

MAXEXTENTS
 default value
 derived from tablespace default storage or DB_BLOCK_SIZE

maximum
 unlimited

Redo Log Files
 maximum number of logfiles
 LOG_FILES initialization parameter, or MAXLOGFILES in CREATE DATABASE; controlfile can be resized to allow more entries; ultimately an O/S limit

maximum number of logfiles per group
 unlimited

Redo Log File Size
 minimum size
 50K bytes

maximum size
 O/S limit, typically 2GB

Tablespaces
 maximum number per database
 64K

Number of tablespaces cannot exceed the number of database files, as each tablespace must include at least one file.

Table 5-3: Logical Database Limits
Item Type Limit
GROUP BY clause
 maximum length
 The group-by expression and all of the non-distinct aggregates (e.g., sum, avg) need to fit within a single database block.

Indexes

 maximum per table
 unlimited

total size of indexed column
 40% of the database block size minus some overhead.

Columns
 table
 1000 columns maximum

indexed
(or clustered index)
 32 columns maximum

 bitmapped index
 30 columns maximum

Constraints
 maximum per column
 unlimited

Nested Queries
 maximum number
 255

Partitions
 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 MAX_ROLLBACK_SEGMENTS init parameter

Rows
 maximum number per table
 no limit

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. Consult your PL/SQL or Developer/2000 documentation for details.
The limits typically range from
2000-3000 lines of code.

Trigger Cascade Limit
 maximum value
 O/S dependent, typically 32

Users and Roles
 maximum
 65525 (combined)

Tables

 maximum per clustered table
 32 tables

maximum per database
 unlimited

Table 5-4: Process / Runtime Limits
Item Type Limit
Instances per database
 maximum number of OPS instances per database  O/S dependent

Locks

 row-level
 unlimited

Distributed Lock Manager
 O/S dependent

SGA size
 maximum value
 O/S dependent, typically
2-4 GB for 32-bit O/S,
> 4 GB for 64 bit O/S

Job Queue Processes
 maximum per instance
 36

I/O Slave Processes
 maximum per background process (DBWR, LGWR, etc.)  15

maximum per Backup session
 15

Sessions
 maximum per instance
 32K, limited by PROCESSES and SESSIONS init parameters

LCK Processes
 maximum per instance
 10

MTS Servers
 maximum per instance
 Unlimited within constraints set by PROCESSES and SESSIONS init parameters, for instance.

Dispatchers
 maximum per instance
 Unlimited within constraints set by PROCESSES and SESSIONS init parameters, for instance.

Parallel Query Slaves
 maximum per instance
 Unlimited within constraints set by PROCESSES and SESSIONS init parameters, for instance.

Backup Sessions
 maximum per instance
 Unlimited within constraints set by PROCESSES and SESSIONS init parameters, for instance.

"Leszek Kozlowski" <leszek-kozlowski_at_set.com.pl> wrote in message news:3879CA91.170E9EA5_at_set.com.pl...
> Hi,
>
> I am preparing a comparision of different RDBMs for my boss. I am
> curious about some questions (I can't find answers on Oracle site).
>
> What phisical limitations for database elements does Oracle 8 Server
> have? I.e.:
> maximum count of tables =
> max size of one table =
> max size of one record =
> max count of fields in one record =
> max count of records in table =
> max count of indexes =
> max count of indexes per table =
>
> Under which operating systems one can develop ap[plications in
> Developer/2000?
>
> How good (or bad) is technical support from Oracle, what is the quality
> of technical documantation?
>
>
> Can you help me?
>
>
> Thanks in advance
>
> Leszek Kozlowski.
>
>
>
>
Received on Mon Jan 10 2000 - 08:39:51 CST