Oracle: How much data is there (GB or MB) stored in a BLOB column in a Table?-CodePudding

I have 3 columns in table which are of blob size.

I would like to calculate how much data is in JPEG PHOTO i.e not whole table size but size of data in particular column using Oracle.

How may i? (I have ADmin user and this table is in other user. Other.Employee table

CodePudding user response：

You can get the size of a particular value with the dbms_lob.getlength function, and then aggregate that across all of the values in the table with:

sum(dbms_lob.getlength(jpegphoto))

You can then divide that number of bytes by [a suitable value] to get mebabytes, mebibytes etc.:

select
  sum(dbms_lob.getlength(jpegphoto)) as b,
  sum(dbms_lob.getlength(jpegphoto)) / 1024 as kib,
  sum(dbms_lob.getlength(jpegphoto)) / (1024*1024) as mib,
  sum(dbms_lob.getlength(jpegphoto)) / (1024*1024*1024) as gib
from employee

fiddle with a couple of really small BLOBs just to demonstrate the principle.