Could you please let me know what is the Data length for the 2nd column_id of "CLOB" data type in the Employee table? I see some blogs where it says maximum data length is : (4GB -1)* (database block size).
I'm new to this data designing.
Table : Employee
**Column_Name ----- Data_Type ------- Nullable ---- Column_Id**
Emp_ID NUMBER No 1
Emp_details CLOB NO 2
Please help me.
To get CLOB size for a given column in a given row, use DBMS_LOB.GETLENGTH function:
select dbms_lob.getlength(emp_details) from employee from emp_id=1;
To get CLOB size for a given column in a given table that is allocated in the tablespace, you need to identify both segments implementing the LOB.
You can compare both size with following query:
select v1.col_size, v2.seg_size from
(select sum(dbms_lob.getlength(emp_details)) as col_size from employee) v1,
(select sum(bytes) as seg_size from user_segments where segment_name in
(
(select segment_name from user_lobs where table_name='EMPLOYEE' and column_name='EMP_DETAILS')
union
(select index_name from user_lobs where table_name='EMPLOYEE' and column_name='EMP_DETAILS')
)
) v2
;
LOBs are not stored in the table, but outside of it in a dedicated structure called LOB segment, using an LOB index. As @pifor explains, you can inspect those structures in the dictionary view user_lobs.
The LOB segment uses blocks of usually 8192 bytes (check the tablespace in user_lobs), so the minimum size allocated for a single LOB is 8K. For 10.000 bytes, you need two 8K blocks and so on.
Please note that if your database is set to Unicode (as most modern Oracle databases are), the size of a CLOB is roughly 2x as expectet, because they are stored in a 16 bit encoding.
This gets a bit better if you compress the LOBs, but your Oracle license needs to cover "Advanced Compression".
For very small LOBs (less than ca 4000 bytes), you can avoid the 8K overhead and store them in the table where all the other columns are (enable storage in row).
