Tuesday, July 22, 2008

Char, Varchar2, Long etc Datatype Limits in Oracle

1)BFILE:
Maximum size: 4 GB-1 which is power(2,32)-1 bytes.
Maximum size of the file name: 255 characters
Maximum size of the directory name: 30 characters
Maximum number of open BFILEs: Limited by the value of the SESSION_MAX_OPEN_FILES initialization parameter, which itself is limited by the maximum number of open files the OS will allow.

2)BLOB:
Maximum size: (4 GB - 1) * DB_BLOCK_SIZE initialization parameter/ LOB Chunk size.

So, if db_block_size=8024K then maximum size=32T
SQL> select 4*1024*1024*1024*8*1024/1024/1024/1024/1024 from dual;
4*1024*1024*1024*8*1024/1024/1024/1024/1024
-------------------------------------------
32

As database block size vary from 2K to 32K so BLOB size can vary from 8TB to 128TB.
(8 TB to 128 TB)

Number of LOB columns per table: Limited by the maximum number of columns per table where maximum can be 1000.

3)CHAR:
Maximum size: 2000 bytes
Minimum and Default Size: 1 byte

4)CHAR VARYING
Maximum size:
4000 bytes

5)CLOB
Same as BLOB in the range of 4T to 128T.

6)Literals (characters or numbers in SQL or PL/SQL)
Maximum size:
4000 characters

7)LONG
Maximum size:
2 GB - 1

8)NCHAR
Maximum size:
2000 bytes

9)NCHAR VARYING
Maximum size:
4000 bytes

10)NCLOB
Same as BLOB in the range of 4T to 128T

11)NUMBER
Maximum size:
999...(in this way 38 9s) * power(10,125)
Minimum size: -999...(in this way 38 9s) *power(10,125)

12)RAW
Maximum size:
2000 bytes

13)VARCHAR
Maximum size:
4000 bytes
Minimum size: 1 byte.

14)VARCHAR2
Maximum size:
4000 bytes
Minimum size: 1 byte.

No comments:

Post a Comment