Re: Securefile BLOBS storage

 Thanks Leo. let me check it. using dbms_lob was giving some wrong results.  Sanjay

    On Monday, February 11, 2019, 9:28:16 AM EST, Glauber, Leo <Leo.Glauber_at_sodexo.com> wrote:  

 #yiv6054491879 #yiv6054491879 -- _filtered #yiv6054491879 {panose-1:2 4 5 3 5 4 6 3 2 4;} _filtered #yiv6054491879 {font-family:Calibri;panose-1:2 15 5 2 2 2 4 3 2 4;}#yiv6054491879 #yiv6054491879 p.yiv6054491879MsoNormal, #yiv6054491879 li.yiv6054491879MsoNormal, #yiv6054491879 div.yiv6054491879MsoNormal {margin:0in;margin-bottom:.0001pt;font-size:11.0pt;font-family:sans-serif;}#yiv6054491879 a:link, #yiv6054491879 span.yiv6054491879MsoHyperlink {color:blue;text-decoration:underline;}#yiv6054491879 a:visited, #yiv6054491879 span.yiv6054491879MsoHyperlinkFollowed {color:purple;text-decoration:underline;}#yiv6054491879 p.yiv6054491879msonormal0, #yiv6054491879 li.yiv6054491879msonormal0, #yiv6054491879 div.yiv6054491879msonormal0 {margin-right:0in;margin-left:0in;font-size:11.0pt;font-family:sans-serif;}#yiv6054491879 span.yiv6054491879EmailStyle18 {font-family:sans-serif;color:#1F497D;font-weight:normal;font-style:normal;text-decoration:none none;}#yiv6054491879 .yiv6054491879MsoChpDefault {font-size:10.0pt;} _filtered #yiv6054491879 {margin:1.0in 1.0in 1.0in 1.0in;}#yiv6054491879 div.yiv6054491879WordSection1 {}#yiv6054491879 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_at_freelists.org <oracle-l-bounce_at_freelists.org>On Behalf Of Sanjay Mishra Sent: Sunday, February 10, 2019 9:40 PM
To: Oracle-L Freelists <oracle-l_at_freelists.org> 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.   

--

http://www.freelists.org/webpage/oracle-l Received on Mon Feb 11 2019 - 17:53:57 CET