I created a table with a SecureFiles CLOB in Oracle 19c. I have a program that populates the CLOB from text files on the file system. After executing the populate routine, I looked at the size of the CLOB segment and it is 2560 Mb. The sum of the file sizes themselves is approximately half that size.
So it appears that the segment size is double the file sizes. I altered the RETENTION parameter on the CREATE TABLE command to use RETENTION MIN 60, to see if this decreases the amount of space used by the CLOB. This had no effect. The segment size was still 2560 Mb.
My question is: I know that the Oracle documentation states that the UNDO for CLOBs is stored in the CLOB segment itself. But why is it double the size of the files? And is there anything I can do to decrease the amount of storage? Is there a way to determine how much of the CLOB segment space is the actual CLOB value versus the UNDO space?
It is not related to your retention, but your characterset.
If you have a variable width characterset (UTF-8 etc) for you database, then we will store clobs in a UCS-2 (UTF-16) format in all cases.
You can see this outside the database as well:
Here I have a standard text file:
C:\>dir c:\temp\file1.txt
Volume in drive C is OS
Volume Serial Number is 66D0-4AB5
Directory of c:\temp
15/01/2020 03:00 PM 1,000,000 file1.txt
I open that file and choose to save it as UTF-16 format, and the resulting file is now:
C:\>dir c:\temp\file1*
Volume in drive C is OS
Volume Serial Number is 66D0-4AB5
Directory of c:\temp
15/01/2020 03:00 PM 1,000,000 file1.txt
15/01/2020 03:09 PM 2,000,000 file1u.txt