BLOB inline or out-of-line storage

From: Chen Zhou <oracle.unknowns_at_gmail.com>
Date: Thu, 18 Feb 2016 14:03:50 -0800
Message-ID: <CAJUY3dTQQUws=Gxo=YLavGW6XgThziv72rPT0Rf13pOdnQAC3Q_at_mail.gmail.com>

Hi, Everyone,
When I was testing moving BLOBs from BasicFile to SecureFile, I found out currently the BLOBs were stored out of line (DISABLE STORAGE IN ROW). It seems to take up large space.

"SELECT SUM(BYTES) FROM DBA_extents WHERE SEGMENT_NAME ='<BLOB NAME>' "
returns 353GB.

"select sum(dbms_lob.getlength('<BLOB COLUMN NAME>')) from <TABLE NAME>"
returns less than 2G!!!

Now the people who designed these tables are long gone, I really don't have anyone to turn to ask for the thoughts behind the storage choice. I am considering using dbms_redefinition to convert the BLOBs to SecureFile, as well as enable storage in row.

Do you see any reason that I might regret the conversion to inline storage someday?

Thank you,
Chen

--
http://www.freelists.org/webpage/oracle-l

Received on Thu Feb 18 2016 - 23:03:50 CET