- The Maximum number of columns in a table or view is 1000. If you try to create a table with more than 1000 columns or alter a table to add column(s) beyond 1000, Oracle throws the following error:
ORA-01792: maximum number of columns in a table or view is 1000
All of the columns can be lob. So, the maximum number of lob columns per table is 1000.
- The Maximum number of columns while creating an Index is 32. If you try to create an index with more than 32 columns, Oracle throws the following error:
ORA-01793: maximum number of index columns is 32
- The Maximum number of datafiles that can be added to an Oracle database is 65536. The initialization parameter "db_files" specifies the maximum number of database files that can be opened for database. The default value for this parameter is 200.
- The Maximum number of blocks in a datafile is 4,294,967,296.
- The Maximum datafile size is calculated as:
Maximum datafile size = db_block_size * maximum number of blocks
So, for a database with block size of 16K, the maximum datafile size is 16 * 4294967296 i.e. 64GB.
- The Maximum database size is calculated as:
Maximum database size = maximum datafile size * maximum number of datafile
So, for a database with block size 16K, the maximum database size is 64*65536 i.e. 4exabytes.
- The maximum size of CHAR column is 2000 bytes, VARCAHR2 column is 4000 byte and LOB column is 4 GB.
- The maximum number of tablespace that can be added to an Oracle database is 65536.
- The maximum number of triggers which can be created on a table is Unlimited. Before Oracle 10G, this was restricted to 12. The maximum size of a trigger is 32K.
- The maximum number of job queue processes(Jnnn) that can be spawned concurrently is 1000. The JOB_QUEUE_PROCESSES initialization parameter control this. It also controls weather a coordinated job queue process (CJQ0) is started by the instance. If set to 0, no job queue jobs are executed.