Oracle Database 11g Limitations

Eveyone wants know what's limitation of the oralce 11g database

Here is some information for you to chew.

CHAR
Maximum size: 2000 bytes

CHAR VARYING
Maximum size: 4000 bytes

CLOB
Maximum size: (4 GB - 1) * DB_BLOCK_SIZE initialization 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,10001).

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.

DATE
Minimum: -4712 (January 1 4712 BC)

NCHAR
Maximum size: 2000 bytes

NCHAR VARYING
Maximum size: 4000 bytes

NCLOB
Maximum size: (4 GB - 1) * DB_BLOCK_SIZE initialization 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,10001).

NUMBER
999...(38 9's) x10125 maximum value.Can be represented to full 38-digit precision (the mantissa)
-999...(38 9's) x10125 minimumvalue.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

Columns
Type Of Limit : Per table
Limit Value : 1000 columns maximum
Type Of Limit : Per index (or clustered index)
Limit Value : 32 columns maximum
Type Of Limit : Per bitmapped index
Limit Value : 30 columns maximum

BFILE
Maximum size: 4 GB
Maximum size of a file name:255 characters
Maximum File Name 30 characters
Maximum number of open BFILEs: The maximum number of BFILEs is limited by the value of the SESSION_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_SIZE initialization 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,10001).



Constraints
Type Of Limit : Maximum per column
Limit Value : Unlimited

Subqueries
Type Of Limit : Maximum levels of subqueries in a SQL statement
Limit Value : Unlimited in the FROM clause of the top-level query ;255 subqueries in the WHERE clause


Partitions
Type Of Limit : Maximum length of linear partitioning key
Limit Value : 4 KB - overhead
Type Of Limit : Maximum number of columns in partition key
Limit Value : 16 columns
Type Of Limit : Maximum number of partitions allowed per table or index
Limit Value : 1024K - 1


Rows
Type Of Limit : Maximum number per table
Limit Value : Unlimited

Stored Packages
Type Of Limit : Maximum size
Limit Value : 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.

Trigger Cascade Limit
Type Of Limit : Maximum value
Limit Value : Operating system-dependent, typically 32

Users and Roles
Type Of Limit : Maximum
Limit Value : 2,147,483,638

Tables
Type Of Limit :Maximum per clustered table
Limit Value : 32 tables
Type Of Limit : Maximum per database
Limit Value : Unlimited


Locks
Type Of Limit : Row-level
Limit Value : Unlimited

Locks
Type Of Limit : Distributed Lock Manager
Limit Value : Operating system dependent

Advanced Queuing Processes
Type Of Limit : Maximum per instance
Limit Value : 10

Job Queue Processes
Type Of Limit : Maximum per instance
Limit Value : 1000

I/O Slave Processes
Type Of Limit : Maximum per background process (DBWR, LGWR, etc.)
Limit Value : 15

I/O Slave Processes
Type Of Limit : Maximum per Backup session
Limit Value : 15

Sessions
Type Of Limit : Maximum per instance
Limit Value : 32 KB; limited by the PROCESSES and SESSIONS initialization parameters

Global Cache Service Processes
Type Of Limit : Maximum per instance
Limit Value : 10

Shared Servers
Type Of Limit : Maximum per instance
Limit Value : Unlimited within constraints set by the PROCESSES and SESSIONS initialization parameters, for instance.

Dispatchers
Type Of Limit : Maximum per instance
Limit Value : Unlimited within constraints set by PROCESSES and SESSIONS initialization parameters, for instance.

Parallel Execution Slaves
Type Of Limit : Maximum per instance
Limit Value : Unlimited within constraints set by ROCESSES and SESSIONS initialization parameters, for instance.

Backup Sessions
Type Of Limit : Maximum per instance
Limit Value : Unlimited within constraints set by PROCESSES and SESSIONS initialization parameters, for instance.

GROUP BY clause
Type Of Limit : Maximum length
Limit Value : The GROUP BY expression and all of the nondistinct aggregate functions (for example, SUM, AVG) must fit within a single database block.

Indexes
Type Of Limit : Maximum per table
Limit Value : Unlimited
Type Of Limit : Total size of indexed column
Limit Value : 75% of the database block size minus some overhead


Database Blocks
Type Of Limit : Minimum in initial extent of a segment
Limit Value : 2 blocks
Type Of Limit :Maximum per datafile
Limit Value: Platform dependent; typically 222 - 1 blocks

Controlfiles
Limit Type : Number of control files
Limit Value : 1 minimum; 2 or more (on separate devices) strongly recommended
Limit Type : Size of a control file
Limit Value : Dependent on operating system and database creation options; maximum of 20,000 x (database block size)

Database files
Limit Type : Maximum per tablespace
Limit Value : Operating system dependent; usually 1022
Limit Type: Maximum per database
Limit Value : 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
Type OF Limit : Maximum per dictionary managed tablespace
Limit Value : 4 GB * physical block size (with K/M modifier); 4 GB (without K/M modifier)
Type Of Limit : Maximum per locally managed (uniform) tablespace
Limit Value : 2 GB * physical block size (with K/M modifier); 2 GB (without K/M modifier)

Database file size
Type Of Limit : Maximum
Limit Value : Operating system dependent. Limited by maximum operating system file size; typically 222 or 4 MB blocks

MAXEXTENTS
Type Of Limit : Default value
Limit Value : Derived from tablespace default storage or DB_BLOCK_SIZE initialization parameter
Type Of Limit : Maximum
Limit Value : Unlimited

Redo Log Files
Type Of Limit : Maximum number of logfiles
Limit Value : 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
Type Of Limit : Maximum number of logfiles per group
Limit Value : Unlimited

Redo Log File Size
Type Of Limit : Minimum size
Limit Value : 4 MB
Type Of Limit : Maximum Size
Limit Value : Operating system limit; typically 2 GB

Tablespaces
Type Of Limit : Maximum number per database
Limit Value : 64 K;Number of tablespaces cannot exceed the number of database files because each tablespace must include at least one file

Bigfile Tablespaces
Type Of Limit : Number of blocks
Limit Value : 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 32 K blocks and 32 TB for a tablespace with 8 K blocks.

Smallfile (traditional) Tablespaces
Type Of Limit : Number of blocks
Limit Value : 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
Type Of Limit : Maximum size
Limit Value : Dependent on the operating system.An external table can be composed of multiple files.

Database Analyzer: DB comparison

Database cost more and making right decision for our requirements is very important. Databases continue to expand, and costs for database software, storage systems and servers to support them continue to increase. Overall growth in data and storage volumes is routinely in the range of 30 to 50 percent per year, and may be higher in individual organizations.

Organizations must deal not only with data growth, but also with increasing database complexity, more
frequent changes and updates, and the need to deliver new applications to increasingly diverse user groups.

This blog examines the option of databases in the industry and the features.

There are several database vendors in the industry and oracle, DB2 and SQL Server are the major database market share holders. We can't rule out Sybase and Teradata also in the list.  There are open source database MySQL and Ingres are famous among them.

Let's look at what's latest stable version of our five top database vendors.

============================================
Database Vendor                 Latest Stable Version

Oracle                                 Oracle 11g R2

DB2                                    DB2 9.7

SQL SERVER                    SQL SERVER 2008 (V10.0)

Sybase                                Sybase 11.0

Teradata                              TeraData V12

============================================


What are the operating systems our top 5 supports:

Oracle 11g supports --- Windows, Linux(highly installed and developed), Unix and Mac O/S X

DB2 Supports --- Windows, Linux and Unix. No MAC.

SQL SERVER --- Windows ONLY

Sybase --- Windows, Mac OS X, Linux and Unix

TeraData --- Windows, Linux and Unix.



Oracle database 11g also runs on OpenVMS, HP/UX and AIX. Mac OS X is limited to 10gR2.

All our top 5 satisfies the fundamental features.

I always like to know the limitations of the product. Every product has its own limitations. Let's look at our top 5 DB's limitations.

Oracle

Max DB size Unlimited (4 GB * block size per tablespace)
Max table size 4 GB * block size (with BIGFILE tablespace)
Max row size 8kB
Max columns per row 1000
Max Blob/Clob size unlimited
Max CHAR size 4000 B
Max NUMBER size 126 bits
Min DATE value -4712 (January 1 4712 BC)
Max DATE value 9999
Max column name size 30

DB2

Max DB size 512 TB (512 TiB)
Max table size 512 TB
Max row size 32,677 B
Max columns per row 1012
Max Blob/Clob size 2 GB
Max CHAR size 32 KB (32 KiB)
Max NUMBER size 64 bits
Min DATE value 0001
Max DATE value 9999
Max column name size 128

SQL SERVER

Max DB size 524,258 TB (32,767 files * 16 TB max file size)
Max table size 524,258 TB
Max row size unlimited
Max columns per row 30000
Max Blob/Clob size 2 GB
Max CHAR size 2 GB
Max NUMBER size 126 bits
Min DATE value 0001
Max DATE value 9999
Max column name size 128

TERADATA

Max DB size unlimited
Max table size unlimited
Max row size 64 KB wo/lobs (64 GB w/lobs)
Max columns per row 2048
Max Blob/Clob size 2 GB
Max CHAR size 10,000
Max NUMBER size 64 bits
Min DATE value Not defined
Max DATE value 9999
Max column name size Not defined.

SYBASE

Max DB size 104 TB (13 files, each file up to 8 TB (32k pages))
Max table size Limited by file size
Max row size Limited by file size
Max columns per row 45000
Max Blob/Clob size 2 GB
Max CHAR size 2 GB
Max NUMBER size 64 bits
Min DATE value 0001
Max DATE value 9999
Max column name size Not defined.

Conclusion:

ORACLE 11g has rich functionality and stable for business. Oracle 11g early adapters are high because it provides wonderful tool to find out your performance and behavior head of time. Some of the cool features are database vault, Bind variable fix, OEM Adviser for partition table and partition key.

DB2 catching up with oracle functionality and IBM huge customer base is helping lot for DB2 installation. I like the IBM data compression and it's huge storage saving for company. The level of compression that may be realized is increased in DB2 9.7 with the addition of new index and temporary space compression capabilities, while a number of functions improve performance.

SQL SERVER is good if you are windows shop. Definitely you can save some money if you shop at one vendor. Its really good for small and medium businesses. SQL SERVER's some of the cool features PowerShell,Policy-Based Management,Filtered indexes,Advanced sparse, columns,Multithreaded partition access,Column-prefix,compression,Module signing using certificates and SQL Server Data Services.

SYBASE database is easy to maintain and has all the fundamental features. If you like SQL SERVER and you want to run it in Linux or Unix then your option is SYBASE.

TERADATA handles huge volume of data and wonderful full table scan facility. If you are running or growing data warehouse system with complex BI reports then you should take a look at Teredata. Now Teradata is attached with big company names.

I will cover the cool features in detail in my future blogs.