Oracle Scratchpad

September 13, 2016

Securefile space

Filed under: fragmentation,Infrastructure,LOBs,Oracle,Troubleshooting — Jonathan Lewis @ 7:29 am BST Sep 13,2016

Here’s a little script I hacked together a couple of years ago from a clone of a script I’d been using for checking space usage in the older types of segments. Oracle Corp. eventually put together a routine to peer inside securefile LOBs:

rem     Script:         dbms_space_use_sf.sql
rem     Author:         Jonathan Lewis
rem     Dated:          Dec 2013
rem     Purpose:        
rem     Last tested 
rem     Not tested
rem     Not relevant
rem     Notes:
rem     See also dbms_space_use.sql
rem     11g introduced securefiles lobs and two overloads of 
rem     dbms_space_usage to report space used by their segments
rem     Valid values for suoption are:
rem             SPACEUSAGE_EXACT (16): Computes space usage exhaustively
rem             SPACEUSAGE_FAST  (17): Retrieves values from in-memory statistics
rem     Valid segment types:
rem             LOB
rem             LOB PARTITION
rem             LOB SUBPARTITION
rem     This version allows for partitioned objects. You could delete
rem     lines about parameter &4 and partition names to eliminate the
rem     complaints about substitution variables.

define m_seg_owner      = &1
define m_seg_name       = &2
define m_seg_type       = '&3'
define m_part_name      = &4

define m_segment_owner  = &m_seg_owner
define m_segment_name   = &m_seg_name
define m_segment_type   = '&m_seg_type'
define m_partition_name = &m_part_name

set linesize 180
set pagesize 60

set trimspool on
set tab off

set verify off
set feedback off
set serveroutput on

spool dbms_space_use_sf

prompt  ============
prompt  Secure files
prompt  ============

        wrong_ssm       exception;
        pragma exception_init(wrong_ssm, -10614);

        m_segment_size_blocks   number(12,0);
        m_segment_size_bytes    number(12,0);
        m_used_blocks           number(12,0);
        m_used_bytes            number(12,0);
        m_expired_blocks        number(12,0);
        m_expired_bytes         number(12,0);
        m_unexpired_blocks      number(12,0);
        m_unexpired_bytes       number(12,0);

                suoption                => dbms_space.spaceusage_exact, 
--              suoption                => dbms_space.spaceusage_fast,
                segment_size_blocks     => m_segment_size_blocks,
                segment_size_bytes      => m_segment_size_bytes,
                used_blocks             => m_used_blocks,
                used_bytes              => m_used_bytes,
                expired_blocks          => m_expired_blocks,
                expired_bytes           => m_expired_bytes,
                unexpired_blocks        => m_unexpired_blocks,
                unexpired_bytes         => m_unexpired_bytes,
                partition_name          => upper('&m_partition_name')

        dbms_output.put_line(' Segment Blocks:   ' || to_char(m_segment_size_blocks,'999,999,990') || ' Bytes: ' || to_char(m_segment_size_bytes,'999,999,999,990')); 
        dbms_output.put_line(' Used Blocks:      ' || to_char(m_used_blocks,'999,999,990')         || ' Bytes: ' || to_char(m_used_bytes,'999,999,999,990')); 
        dbms_output.put_line(' Expired Blocks:   ' || to_char(m_expired_blocks,'999,999,990')      || ' Bytes: ' || to_char(m_expired_bytes,'999,999,999,990')); 
        dbms_output.put_line(' Unexpired Blocks: ' || to_char(m_unexpired_blocks,'999,999,990')    || ' Bytes: ' || to_char(m_unexpired_bytes,'999,999,999,990')); 

        when wrong_ssm then
                dbms_output.put_line('Segment not ASSM');

prompt  ===============
prompt  Generic details
prompt  ===============

        m_total_blocks                  number;
        m_total_bytes                   number;
        m_unused_blocks                 number;
        m_unused_bytes                  number;
        m_last_used_extent_file_id      number;
        m_last_used_extent_block_id     number;
        m_last_used_block               number;
                segment_owner                   => upper('&m_segment_owner'),
                segment_name                    => upper('&m_segment_name'),
                segment_type                    => upper('&m_segment_type'),
                total_blocks                    => m_total_blocks,
                total_bytes                     => m_total_bytes, 
                unused_blocks                   => m_unused_blocks,  
                unused_bytes                    => m_unused_bytes,
                last_used_extent_file_id        => m_last_used_extent_file_id, 
                last_used_extent_block_id       => m_last_used_extent_block_id,
                last_used_block                 => m_last_used_block,
                partition_name                  => upper('&m_partition_name')

        dbms_output.put_line('Segment Total blocks: ' || to_char(m_total_blocks,'999,999,990'));
        dbms_output.put_line('Object Unused blocks: ' || to_char(m_unused_blocks,'999,999,990'));


spool off

Sample of output (from a slightly older version of the code, with the “partition” prompts deleted):

Secure files

 Segment Blocks:    168960 Bytes: 1384120320
 Used Blocks:       151165 Bytes: 1238343680
 Expired Blocks     17795 Bytes: 145776640
 Unexpired Blocks   0 Bytes: 0

Generic details

Segment Total blocks: 168960
Object Unused blocks: 0

And a sample where I created a table (n1 number, b1 blob) with the blob storage set to disable storage in row, inserted one row with a blob of 100 bytes, then updated (with commit) in a loop 1,000 times, increasing the length of the blob by one byte each time:

Secure files

 Segment Blocks:          1,152 Bytes:        9,437,184
 Used Blocks:                82 Bytes:          671,744
 Expired Blocks:          1,070 Bytes:        8,765,440
 Unexpired Blocks:            0 Bytes:                0

Generic details

Segment Total blocks:        1,152
Object Unused blocks:            0

As you can see my “one” blob has exploded the segment to over 1,000 blocks because Oracle accumulated 1,000 old versions of the blob which could not be overwritten. The 82 used blocks is mostly space management metadata.


  1. […] factor for an object. Segment Space: A script to report on space usage within generai segment types Securefile Space: A variant on the space reporting script to report space usage inside Securefile […]

    Pingback by Simple scripts | Oracle Scratchpad — September 15, 2016 @ 9:18 am BST Sep 15,2016 | Reply

  2. […] published a sample of one of the securefile options here, and the generic ASSM code that we need is part of the script here. Our estimate (excluding the 1% […]

    Pingback by LOB space | Oracle Scratchpad — July 20, 2022 @ 5:07 pm BST Jul 20,2022 | Reply

RSS feed for comments on this post. TrackBack URI

Comments and related questions are welcome.

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Website Powered by

%d bloggers like this: