Hi Sanjay,
Jonathan Lewis’ post has the most accurate way with DBMS_SPACE:
https://jonathanlewis.wordpress.com/2016/09/13/securefile-space/
I also use this statement, but it 1) relies on stats for NUM_ROWS and 2) shows
total segment usage which becomes very skewed for LOB’s where updates and
deletes are run against the table.
SET feedback ON linesize 350 trimspool ON
COLUMN column_name FORMAT a30
COLUMN owner FORMAT a30
COLUMN table_name FORMAT a30
COLUMN segment_name FORMAT a30
COLUMN tablespace_name FORMAT a30 HEADING "LOB Tablespace"
COLUMN lob_bytes FORMAT a20
COLUMN table_bytes FORMAT a20
COLUMN avg_lob_size FORMAT a20
SELECT dl.owner, dl.table_name, dl.column_name, dt.num_rows, dl.segment_name,
dl.tablespace_name,
to_char(ds_lob.bytes, '999,999,999,999,999') Lob_bytes,
to_char(ds_tab.bytes, '999,999,999,999,999') Table_bytes,
to_char(ds_lob.bytes / dt.num_rows, '999,999,999,999,999') avg_lob_size
FROM dba_tables dt, dba_lobs dl, dba_segments ds_lob, dba_segments ds_tab
WHERE dl.owner = 'ESB_ICE_OWNER'
AND dl.table_name = 'MESSAGE_STORE'
AND dl.owner = dt.owner
AND dl.table_name = dt.table_name
AND dl.segment_name = ds_lob.segment_name
AND ds_tab.owner = dt.owner
AND ds_tab.segment_name = dt.table_name
ORDER BY ds_lob.bytes desc;
-Leo
From: oracle-l-bounce@xxxxxxxxxxxxx <oracle-l-bounce@xxxxxxxxxxxxx> On Behalf
Of Sanjay Mishra
Sent: Sunday, February 10, 2019 9:40 PM
To: Oracle-L Freelists <oracle-l@xxxxxxxxxxxxx>
Subject: Securefile BLOBS storage
I had multiple tables with BLOB column stored as secure file in the multiple
schema. I need to check the Total Space used by these BLOBcolumn as well as Avg
size. Can someone share the script that be used to create the details like for
one full Schema
Tx
Sanjay
________________________________
This e-mail, attachments included, is confidential. It is intended solely for
the addressees. If you are not an intended recipient, any use, copy or
diffusion, even partial of this message is prohibited. Please delete it and
notify the sender immediately. Since the integrity of this message cannot be
guaranteed on the Internet, SODEXO cannot therefore be considered liable for
its content.
Ce message, pieces jointes incluses, est confidentiel. Il est etabli a
l'attention exclusive de ses destinataires. Si vous n'etes pas un destinataire,
toute utilisation, copie ou diffusion, meme partielle de ce message est
interdite. Merci de le detruire et d'en avertir immediatement l'expediteur.
L'integrite de ce message ne pouvant etre garantie sur Internet, SODEXO ne peut
etre tenu responsable de son contenu.