- 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.
This is another Oracle inbuilt function. As a developer, I have never used it but I found it quite interesting and useful. The dump function returns a varchar2 value which looks something like:
Typ=<Datatype Code> Len=<Lenght of the expression>: <Internal Representation>
This is the code for the Oracle built-in datatypes. These codes are used internally by Oracle. I don't know any other function / table except "dump" function that can return this code. For details of these datatypes codes, please refer:
Length of the expression (in bytes):
This is the length of the input expression.
Internal Representation of the input expression:
This is the notation in which you want to see the tokenized input string (comma delimited).
Syntax: dump (expression[, display_format [, start_position [, length]]] )
The exact definition of this function is (extracted from standard package)
function DUMP( e varchar2 character set any_cs,
df pls_integer := null,
sp pls_integer := null,
len pls_integer := null)
All of the above except "expression" are optional parameters. Lets discuss them one by one:
This is the input string passed to the function. Usually this is a varchar2 string but number or a date can also be passed. This is an overloaded function.
This parameters determines the representation (format) of the return value. It can have the following values:
8 octal notation
10 decimal notation
16 hexadecimal notation
17 single characters
1008 octal notation with the character set name
1010 decimal notation with the character set name
1016 hexadecimal notation with the character set name
1017 single characters with the character set name
Default display format is Decimal without characterset information (10).
The starting point from where you want to display the string. Default starting position is 1.
The lenght of the string you want to display the string upto, starting from [start_position] or from 1. Default lenght is complete length of the expression.
Lets see how it works through some examples:
DUMP('STRING') Typ=96 Len=6: 83,84,82,73,78,71 (Default display format, Decimal)
DUMP('STRING', 8 ) Typ=96 Len=6: 123,124,122,111,116,107
DUMP('STRING', 10) Typ=96 Len=6: 83,84,82,73,78,71
DUMP('STRING', 16) Typ=96 Len=6: 53,54,52,49,4e,47
DUMP('STRING', 17) Typ=96 Len=6: S,T,R,I,N,G
Another way of Tokenizing the string, hmmm
DUMP('STRING', 1008 )
Typ=96 Len=6 CharacterSet=WE8ISO8859P1: 123,124,122,111,116,107
Let's see how start_position/length works:
DUMP('STRING', 17, 3) Typ=96 Len=6: R,I,N,G
DUMP('STRING', 17, 3, 3 ) Typ=96 Len=6: R,I,N
DUMP('STRING', 3) Typ=96 Len=6: 83,84,82,73,78,71
Because this is positional notation, the value 3 is getting assigned to display_format and thus getting ignored as this value is undefined.
The function returns the string "NULL" for null value passed.
As I mentioned before, this is overloaded function and can take number/date as parameter.
DUMP(10001, 17) Typ=2 Len=4: c3,^B,^A,^B
DUMP(sysdate, 17) Typ=13 Len=8: ^G,da,^H,^L,^R,^O,2,^@
I am not able to comprehend these outputs. If someone understands it, please share.