RE: Securefile BLOBS storage

From: Glauber, Leo <Leo.Glauber_at_sodexo.com>
Date: Mon, 11 Feb 2019 14:26:14 +0000
Message-ID: <DB7PR02MB4250467115F5C8EF9327E4E39D640_at_DB7PR02MB4250.eurprd02.prod.outlook.com>

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 - 15:26:14 CET