Oracle9i Limitation ...
|
Datatypes
|
Datatype
Limits
|
|
VARCHAR2
|
Maximum size is 4000
|
|
NVARCHAR2
|
Maximum size is determined by the
national character set definition, with an upper limit of 4000 bytes
|
|
NUMBER( p,s)
|
The precision p can range from 1 to 38.
The scale s can range from -84 to 127
|
|
LONG
|
up to 2 gigabytes, or 231 -1 bytes
|
|
DATE
|
range from January 1, 4712 BC to
December 31, 9999 AD
|
|
TIMESTAMP(
fractional_seconds_precision)
|
fractional_seconds_precisionis the
number of digits in the fractional part of the SECOND datetime field. Accepted values of
fractional_seconds_precision are 0 to 9. The default is 6
|
|
TIMESTAMP(
fractional_seconds_precision) WITH TIME ZONE
|
fractional_seconds_precisionis the
number of digits in the fractional part of the SECOND
datetime field. Accepted values are 0 to 9. The default
is 6
|
|
TIMESTAMP(
fractional_seconds_precision)WITH LOCAL TIME ZONE
|
same as
TIMESTAMP(fractional_seconds_precision) WITH TIME ZONE
|
|
INTERVAL YEAR(
year_precision) TO MONTH
|
year_precision is the number of digits
in the YEAR datetime field. Accepted values are 0 to 9.
The default is 2
|
|
INTERVAL DAY (day_precision)
TO SECOND (fractional_seconds_precision)
|
day_precision is the maximum number of
digits in the DAY datetime field. Accepted values are 0
to 9. The default is 2
--
fractional_seconds_precision is the
number of digits in the fractional part of the SECOND field. Accepted values are 0 to 9. The default
is 6
|
|
RAW( size)
|
Maximum size is 2000 bytes
|
|
LONG RAW
|
up to 2 gigabytes
|
|
ROWID
|
Base 64 string representing the unique
address of a row in its table
|
|
UROWID [(
size)]
|
Base 64 string representing the logical
address of a row of an index-organized table. The optional size is the size of a column of type
UROWID. The maximum size and default is 4000 bytes.
|
|
CHAR(
size)[BYTE |CHAR]
|
Fixed-length character data of length
size bytes.Maximum size is 2000 bytes. Default and minimum size is 1 byte
|
|
NCHAR( size)
|
Fixed-length character data of length
size characters. Maximum size is determined by the
national character set definition, with an upper limit of 2000 bytes. Default and minimum size is 1
character
|
|
CLOB
|
Maximum size is 4 gigabytes
|
|
NCLOB
|
Maximum size is 4 gigabytes
|
|
BLOB
|
Maximum size is 4 gigabytes
|
|
BFILE
|
Maximum size is 4 gigabytes
|
|
|
|
|
|
Physical
Database Limits
|
|
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 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 logfiles
Limited by value of MAXLOGFILES parameter in the CREATE DATABASE statement. 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 KB Number of tablespaces cannot exceed the number of database files, as each tablespace must
include at leastone file
|
|
|
|
|
|
Logical
Database Limits
|
|
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 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
64 K-1 partitions
|
|
Rollback
Segments
|
>>Maximum
number per database
No limit; limited within a session by MAX_ROLLBACK_SEGMENTS initialization parameter
|
|
Rows
|
>>Maximum
number per table
Unlimited
|
|
SQL Statement
Length
|
>>Maximum
length of statements
64 KB 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
|
|
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
32K; limited by PROCESSES and SESSIONS initialization parameters
|
|
Global Cache
Service Processes
|
>>Maximum
per instance
10
|
|
Shared Servers
|
>>Maximum
per instance
Unlimited within constraints set by PROCESSES and SESSIONS initialization parameters, for instance
|
|
Dispatchers
|
>>Maximum
per instance
Unlimited within constraints set by PROCESSES and SESSIONS initialization parameters, for instance
|
|
Parallel
Execution Slaves
|
>>Maximum
per instance
Unlimited within constraints set by PROCESSES and SESSIONS initialization parameters, for instance
|
|
Backup
Sessions
|
>>Maximum
per instance
Unlimited within constraints set by PROCESSES and SESSIONS initialization parameters, for instance
|