CODEWITHSUNDEEP
oracle-databaseoracle11gbloboracle-sqldeveloperoracle10g
fatherazrael
fatherazrael

Reputation: 5957

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

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

enter image description here

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

Upvotes: 0

Views: 3912

Answers (1)

Alex Poole
Alex Poole

Reputation: 191235

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.

Upvotes: 2

Related Questions