Data Types in Oracle

I thought the topic was trivial, but if you read the chapter 12 of Tom Kyte's Oracle book, you might have a different thought. There are so many nuances for each data type that one might get bitten if one is not careful (or lucky). I summarize below some of them which, I think, are important to me.

VARCHAR2: A VARCHAR2(10) may contains 0~10 bytes of data using the default NLS settings. VARCHAR2 may contain up to 4000 bytes of information. If the NLS settings between the client and server are different, an implicit character set conversion will take place behind the scene. The NLS setting of Oracle client can be found here, while the default character set of Oracle Database can be determined with

SELECT value FROM nls_database_parameters
WHERE parameter = 'NLS_CHARACTERSET'

NVARCHAR2: A NVARCHAR2(10) may contains 0~10 characters of UNICODE formated data. Like VARCHAR2, NVARCHAR2 may contain up to 4000 bytes of information. Text in NVARCHAR2 is stored and managed in the database's national character set (UTF8 or UTF16 since Oracle9i), not the default character set. The national character set of Oracle Database can be determined with

SELECT value FROM nls_database_parameters
WHERE parameter = 'NLS_NCHAR_CHARACTERSET'

LOB: When a table with an internal LOB(eg. CLOB, NCLOB, BLOB) type is created, 2 additional user segments are created as well. One is lobindex; the other is lobsegment. What is stored in the table is a pointer, which can be used against logindex to find the LOB data stored in the lobsegment. So retrieving LOB causes extra disk access, due to logindex lookup. However, for LOB data smaller than 4000 bytes, it can be stored with the row in the table, if STORAGE IN ROW attribute is enabled (by default).

CHUNK is the smallest unit of allocation for LOBs. The default is 8K bytes. So one LOB data can have a least one chunk and two LOB data can never share the same chunk. Also, a small chunk requires many chunks to store large LOB data, and that increases the size of lobindex.

PCTVERSION controls the percentage of allocated lobsegment space that should be used for undo purpose. The default is 10%. If you get an ORA-22924 error while updating a LOB, try increase the amount of space in the lobsegment for versioning of data.

ROWID: the address of a row in the database. It's mutable for the following reasons.

1. Updating the partition key of a row in a partitioned table such that the row must move from one partition to another
2. Using the FLASHBACK table command to restore a database table to a prior point in time
3. Performing MOVE operations and many partition operations such as splitting or merge partitions
4. Using the ALTER TABLE SHRINK SPACE command to perform a segment shrink