Tuesday, February 18, 2025

Blog List

Posted by at

One Oracle CLOB Space Usage Test

In this Blog, we will make LOB space usage test to show the used_blocks, expired_blocks, unexpired_blocks.
And in case of LOB "buffer busy waits" and "latch: cache buffers chains", we list the type of LOB data blocks in buffer cache.

Note: Tested in Oraclw 19.25 with following UNDO parameters: 
  temp_undo_enabled   boolean     TRUE
  undo_management     string      AUTO
  undo_retention      integer     3600


1. Test Run


Run following test to insert 1000 rows (TEST_SEQ is cycle with maxvalue 1000), each row has one CLOB of 1MB, so total is about 1000MB. 

-- see appended Test Code
truncate table test_tab_lob;
set serveroutput on size unlimited 
alter sequence test_seq_1000 restart start with 1;
exec check_space_securefile_2('K', 'TEST_TAB_LOB', 'MYLOB');
-- 10000 inserts for total 1000 rows, each row is repeatedly overwritten.
exec test_lob_proc(1, 1000, 1e4); 
exec check_space_securefile_2('K', 'TEST_TAB_LOB', 'MYLOB');
select round(sum(dbms_lob.getlength(mylob))/1024/1024, 2) LOB_MB, round(sum(length(mylob))/1024/1024, 2) MB
      ,count(id), min(id), max(id), min(ts), max(ts) from test_tab_lob;
The output looks like: 

SQL > exec test_lob_proc(1, 1000, 1e4);
  Each mylob Length = 1056000, SUM Size (MB) =10000
  SUM Size (MB) =10000, Real Contained Size (MB, due to TEST_SEQ_1000.Max Limit) = 1000    
  
  Elapsed: 00:08:02.09

SQL > exec check_space_securefile_2('K', 'TEST_TAB_LOB', 'MYLOB');
  ===========================================================================
  segment_size_blocks = 1301880
  used_blocks         = 145718 (11.19 %)
  expired_blocks      = 1156162 (88.81 %)
  unexpired_blocks    = 0 (0 %)
  ===========================================================================
  Segment Blocks/Bytes   = 1301880 / 10665000960 (10170.94 MB)
  Unused Blocks/Bytes    = 0 / 0 (0 %)
  Used Blocks/Bytes      = 145718 / 1193721856 (11.19 %)
  Expired Blocks/Bytes   = 1156162 / 9471279104 (88.81 %)
  Unexpired Blocks/Bytes = 0 / 0 (0 %)
  ===========================================================================
  NON Data Blocks  = 1156162 (88.81 %)
  NON_data_blocks_2 (= segment_size_blocks - used_blocks) = 1156162
  ===========================================================================
  LOBSEGMENT DBA_EXTENTS storage size Blocks  = 1301880, CNT = 280, MIN_Blocks = 120, MAX_Blocks = 8192
  LOBSEGMENT DBA_EXTENTS storage size Byets   = 10665000960 (10170.94 MB)
  LOBSEGMENT DBA_SEGMENTS storage size Blocks = 1301880
  LOBSEGMENT DBA_SEGMENTS storage size Byets  = 10665000960 (10170.94 MB)

SQL > select round(sum(dbms_lob.getlength(mylob))/1024/1024, 2) LOB_MB, round(sum(length(mylob))/1024/1024, 2) MB
             ,count(id), min(id), max(id), min(ts), max(ts) from test_tab_lob;

   LOB_MB         MB  COUNT(ID)  MIN(ID)  MAX(ID) MIN(TS)               MAX(TS)
  ------- ---------- ---------- -------- -------- --------------------  --------------------
  1007.08    1007.08       1000        1     1000 17-FEB-2025 11:14:27  17-FEB-2025 11:15:15
Above output shows that for 1007.08 MB CLOB data, the segment consumes about 10170.94 MB, of which 88.81% is used for NON Data Blocks, and they are expired_blocks.

By the way, previous Blog: "LOB ORA-22924: snapshot too old and Fix" (http://ksun-oracle.blogspot.com/2019/04/lob-ora-22924-snapshot-too-old-and-fix.html) showed the special behaviour of LOB snapshot too old.

We can also run a parallel job sessions to show 'buffer busy waits' / 'latch: cache buffers chains' and the contention data block CLASS#.

Run job test by: 

--exec clearup_test;
truncate table test_tab_lob;
alter sequence test_seq_1000 restart start with 1;
exec check_space_securefile_2('K', 'TEST_TAB_LOB', 'MYLOB');
exec test_lob_proc_job(16, 1000, 1e3);

-- Wait till all Jobs finished
select count(*) from dba_scheduler_jobs where job_name like '%TEST_JOB%';

-- Show space usage
exec check_space_securefile_2('K', 'TEST_TAB_LOB', 'MYLOB');
And run monitoring query during test to watch the wait event: 

select program, event, module, action, event, p1, p2, p3, p1text, p2text, p3text, t.* 
from v$active_session_history t
where sample_time > sysdate -10/1440 and action like 'TEST_JOB%' 
  and event in ('buffer busy waits', 'latch: cache buffers chains')
order by t.sample_time desc;
It shows that most of 'buffer busy waits' / 'latch: cache buffers chains' are on LOB metadata CLASS# 8 (1st level bmb) and 6 (free list).

With following query, we can display the type of data blocks in buffer cache: 

with sq as  (select /*+ materialize */ segment_name, index_name from dba_lobs where owner='K' and table_name='TEST_TAB_LOB'),
     obj as (select /*+ materialize */ object_name||', '||object_type LOB_INFO, object_id, data_object_id 
             from dba_objects t, sq where object_name = 'TEST_TAB_LOB' or object_name = sq.segment_name or object_name = sq.index_name)
select LOB_INFO, objd,  class#, status, dirty, file#, count(block#), count(distinct block#)  from v$bh, obj
where objd =  obj.data_object_id
group by LOB_INFO, objd,  class#, status, dirty, file# order by 1, 6 desc, 2, 3, 4, 5 desc; 
  
  LOB_INFO                                OBJD     CLASS# STATUS     D      FILE# COUNT(BLOCK#) COUNT(DISTINCTBLOCK#)
  --------------------------------- ---------- ---------- ---------- - ---------- ------------- ---------------------
  SYS_IL0005743559C00005$$, INDEX      5743561          1 xcur       N       1625             1                     1
  SYS_IL0005743559C00005$$, INDEX      5743561          4 xcur       N       1625             1                     1
  SYS_IL0005743559C00005$$, INDEX      5743561          8 xcur       N       1625             1                     1
  SYS_LOB0005743559C00005$$, LOB       5743564          4 cr         N       1625             5                     1
  SYS_LOB0005743559C00005$$, LOB       5743564          4 xcur       Y       1625             1                     1
  SYS_LOB0005743559C00005$$, LOB       5743564          6 cr         N       1625           205                    46
  SYS_LOB0005743559C00005$$, LOB       5743564          6 xcur       Y       1625            56                    56
  SYS_LOB0005743559C00005$$, LOB       5743564          8 cr         N       1625            34                     9
  SYS_LOB0005743559C00005$$, LOB       5743564          8 xcur       Y       1625             9                     9
  SYS_LOB0005743559C00005$$, LOB       5743564          9 cr         N       1625           424                   106
  SYS_LOB0005743559C00005$$, LOB       5743564          9 xcur       Y       1625           126                   126
  SYS_LOB0005743559C00005$$, LOB       5743564          9 xcur       N       1625            59                    59
  SYS_LOB0005743559C00005$$, LOB       5743564         10 cr         N       1625           115                    26
  SYS_LOB0005743559C00005$$, LOB       5743564         10 xcur       Y       1625            50                    50
  SYS_LOB0005743559C00005$$, LOB       5743564         12 xcur       N       1625          1271                  1271
  TEST_TAB_LOB, TABLE                  5743565          1 cr         N       1625           114                    28
  TEST_TAB_LOB, TABLE                  5743565          1 xcur       Y       1625            28                    28
  TEST_TAB_LOB, TABLE                  5743565          4 cr         N       1625             1                     1
  TEST_TAB_LOB, TABLE                  5743565          4 xcur       Y       1625             1                     1
  TEST_TAB_LOB, TABLE                  5743565          8 xcur       Y       1625             2                     2
  TEST_TAB_LOB, TABLE                  5743565          9 xcur       Y       1625             1                     1
  
  21 rows selected.
The above output shows that majority blocks are from CLASS# 12 (bitmap index block) and CLASS# 9 (2nd level bmb), and hardly see blocks of CLASS# 1 (data block), because Oracle uses "direct path" for LOB data (not IN ROW short LOB).

We can also dump the segment header and bitmap blocks to reveal space distribution. 

with sq as (select /*+ materialize */ segment_name, index_name from dba_lobs where owner='K' and table_name='TEST_TAB_LOB')
select t.segment_name, tablespace_name, header_file, relative_fno, header_block, bytes, blocks, extents from dba_segments t, sq 
where t.segment_name = sq.segment_name or t.segment_name = sq.index_name;

  SEGMENT_NAME               TABLESPACE_NAME  HEADER_FILE RELATIVE_FNO HEADER_BLOCK      BYTES     BLOCKS    EXTENTS
  -------------------------- ---------------- ----------- ------------ ------------ ---------- ---------- ----------
  SYS_LOB0005743559C00005$$  TEST_TBS                1625         1024       262153 1.0661E+10    1301376        276
  SYS_IL0005743559C00005$$   TEST_TBS                1625         1024       262274      65536          8          1


alter session set max_dump_file_size = UNLIMITED;
alter session set tracefile_identifier = 'TEST_TAB_LOB_SEGMENT_DUMP_3';
EXECUTE DBMS_SPACE_ADMIN.SEGMENT_DUMP('TEST_TBS', 1024, 262153); 

-- Dump DDL
select dbms_metadata.get_ddl('TABLE', 'TEST_TAB_LOB', 'KS') from dual;
or dump single bmb block: 

select * from  v$bh where objd=5743591 and class#=8 and status='xcur';
  -- 1625	262159	8	xcur
alter session set max_dump_file_size = UNLIMITED;
alter session set tracefile_identifier = 'bmb_1_262159_dump';
alter system dump datafile 1625 block 262159;
In above test, the CLOB retention_type is DEFAULT: 

select t.column_name, t.retention_type, retention_value, s.segment_name, s.tablespace_name, s.header_file, s.relative_fno, s.header_block--, t.*
from dba_lobs t, dba_segments s where t.owner='K' and t.table_name='TEST_TAB_LOB' and t.segment_name = s.segment_name;

  COLUMN_NAME  RETENTION_TYPE  RETENTION_VALUE SEGMENT_NAME               TABLESPACE_NAME   HEADER_FILE RELATIVE_FNO HEADER_BLOCK
  ------------ --------------- --------------- -------------------------- ----------------- ----------- ------------ ------------
  MYLOB        DEFAULT                         SYS_LOB0005743559C00005$$  TEST_TBS                 1625         1024       262153
We can modify its retention_type as NONE by: 

alter table TEST_TAB_LOB modify lob(mylob) (retention none);

SQL > select t.column_name, t.retention_type, retention_value, s.segment_name, s.tablespace_name, s.header_file, s.relative_fno, s.header_block--, t.*
        from dba_lobs t, dba_segments s where t.owner='K' and t.table_name='TEST_TAB_LOB' and t.segment_name = s.segment_name;

  COLUMN_NAME  RETENTION_TYPE  RETENTION_VALUE SEGMENT_NAME               TABLESPACE_NAME   HEADER_FILE RELATIVE_FNO HEADER_BLOCK
  ------------ --------------- --------------- -------------------------- ----------------- ----------- ------------ ------------
  MYLOB        NONE                            SYS_LOB0005743559C00005$$  TEST_TBS                 1625         1024       262153
and then repeat above test: 

SQL > exec test_lob_proc(1, 1000, 1e4);

  Each mylob Length = 1056000, SUM Size (MB) =10000
  SUM Size (MB) =10000, Real Contained Size (MB, due to TEST_SEQ_1000.Max Limit) = 1000
  
  Elapsed: 00:08:02.12

SQL > exec check_space_securefile_2('K', 'TEST_TAB_LOB', 'MYLOB');

  ===========================================================================
  segment_size_blocks = 163960
  used_blocks         = 144372 (88.05 %)
  expired_blocks      = 19588 (11.95 %)
  unexpired_blocks    = 0 (0 %)
  ===========================================================================
  Segment Blocks/Bytes   = 163960 / 1343160320 (1280.94 MB)
  Unused Blocks/Bytes    = 0 / 0 (0 %)
  Used Blocks/Bytes      = 144372 / 1182695424 (88.05 %)
  Expired Blocks/Bytes   = 19588 / 160464896 (11.95 %)
  Unexpired Blocks/Bytes = 0 / 0 (0 %)
  ===========================================================================
  NON Data Blocks  = 19588 (11.95 %)
  NON_data_blocks_2 (= segment_size_blocks - used_blocks) = 19588
  ===========================================================================
  LOBSEGMENT DBA_EXTENTS storage size Blocks  = 163960, CNT = 140, MIN_Blocks = 120, MAX_Blocks = 8192
  LOBSEGMENT DBA_EXTENTS storage size Byets   = 1343160320 (1280.94 MB)
  LOBSEGMENT DBA_SEGMENTS storage size Blocks = 163960
  LOBSEGMENT DBA_SEGMENTS storage size Byets  = 1343160320 (1280.94 MB)

SQL > select round(sum(dbms_lob.getlength(mylob))/1024/1024, 2) LOB_MB, round(sum(length(mylob))/1024/1024, 2) MB
            ,count(id), min(id), max(id), min(ts), max(ts) from test_tab_lob;

   LOB_MB         MB  COUNT(ID)    MIN(ID)    MAX(ID) MIN(TS)               MAX(TS)
  ------- ---------- ---------- ---------- ---------- --------------------- --------------------
  1007.08    1007.08       1000          1       1000 17-FEB-2025 11:41:04  17-FEB-2025 11:41:52
The above output shows that when RETENTION_TYPE=NONE, only 1280.94 MB is used and expired_blocks is 11.95%, compared to previous RETENTION_TYPE=DEFAULT, 10170.94 MB is used and expired_blocks is 88.81%.

Strangely, once RETENTION_TYPE is set to NONE, setting back to DEFAULT gets error: 

SQL > alter table TEST_TAB_LOB modify lob(mylob) (retention default);
  alter table TEST_TAB_LOB modify lob(mylob) (retention default)
                                                      *
  ERROR at line 1:
  ORA-22853: invalid LOB storage option specification
Here is one hacking workaround to set it back to DEFAULT: 

select header_file, relative_fno, header_block, t.* from dba_segments t where segment_name = 'SYS_LOB0005742851C00004$$';
  --1625	137
                   
select lists
      ,decode(s.lists, 0, 'NONE', 1, 'AUTO',
                       2, 'MIN',  3, 'MAX',
                       4, 'DEFAULT', 'INVALID') ora_retention_type
      ,(select retention_type from  dba_lobs t where table_name='TEST_TAB_LOB') dba_retention_type
      ,s.* 
from sys.seg$ s where file# = 1024 and block# = 262153;   -- lists stores RETENTION

  --4	DEFAULT	DEFAULT	1024	262153
  --0	NONE	  NONE	  1024	262153

update sys.seg$ set lists= 4  where file# = 1024 and block# = 262153;   -- set RETENTION_TYPE to DEFAULT as DBA
commit;
In this Blog, we tested CLOB with "SECUREFILE/COMPRESS/NOCACHE", the similar behavior is also observed with "BASICFILE/NO COMPRESS/CACHE READS".


2. Test Setup 



drop tablespace test_tbs including contents and datafiles;

create bigfile tablespace test_tbs datafile '/oratestdb/oradata/testdb/test_tbs.dbf' size 10G online;

drop table test_tab_lob cascade constraints;

create table test_tab_lob(id number, cnt number, ts timestamp with local time zone default localtimestamp, lob_state varchar2(20), mylob clob) 
  segment creation immediate tablespace test_tbs
  lob (mylob) store as securefile (
   tablespace test_tbs enable storage in row chunk 8192 retention 
   nocache logging compress medium keep_duplicates
   storage(initial 65536 next 131072 minextents 1 maxextents 2147483645
   pctincrease 0 freelists 1 freelist groups 1
   buffer_pool default flash_cache default cell_flash_cache default))  enable row movement;

drop sequence test_seq_1000;

create sequence test_seq_1000 start with 1 maxvalue 1000 minvalue 1 cycle cache 10 global;


3. Test Code 



create or replace procedure test_lob_proc(p_job_nr number, p_kb_cnt number, p_loop_cnt number) as 
  l_mylob     clob;
  l_src_mylob clob;
  l_lob_32kb  clob := lpad('abc', 32000, 'x');
  l_seq       number;
  l_ts_text   varchar2(50);
begin
  l_ts_text   := to_char(localtimestamp, 'YYYY*MON*DD-HH24:MI:SS.FF9');
  l_lob_32kb := dbms_random.string('p', 4000);
  
  --CLOB stored in AL16UTF16 (wo bytes) in AL32UTF8 database. Use dbms_random.string due to CLOB compress
  for i in 1..7 loop
    dbms_lob.append(dest_lob => l_lob_32kb, src_lob => dbms_random.string('p', 4000));  
  end loop;
  l_src_mylob := l_lob_32kb;
  if p_kb_cnt >= 32 then
	  for i in 1..round(p_kb_cnt/32)+1 loop
	    dbms_lob.append(l_src_mylob, l_lob_32kb);
	  end loop;
	else
	  l_src_mylob := dbms_lob.substr(l_lob_32kb, p_kb_cnt*1000, 1);
	end if;
  dbms_output.put_line('Each mylob Length = '||dbms_lob.getlength(l_src_mylob) ||', SUM Size (MB) ='|| (p_loop_cnt*p_kb_cnt/1000));
  dbms_output.put_line('SUM Size (MB) ='|| (p_loop_cnt*p_kb_cnt/1000) ||', Real Contained Size (MB, due to TEST_SEQ_1000.Max Limit) = '|| (1000*p_kb_cnt/1000));
  
  for k in 1..p_loop_cnt loop
	  l_seq := test_seq_1000.nextval;

		merge 
		  into  test_tab_lob l
		  using (select l_seq id, 1 cnt, localtimestamp ts, 'pending' lob_state, empty_clob() mylob from dual) v
		  on (l.id = v.id)
		  when matched then
		    update set cnt = v.cnt + 1, mylob = v.mylob
		  when not matched then
		    insert (id, cnt, ts, mylob) values (l_seq, 1, localtimestamp, v.mylob);
		
		--select mylob into l_mylob from test_tab_lob where id = l_seq;
		--dbms_lob.append(dest_lob => l_mylob, src_lob => l_src_mylob);
    
    -- @TODO First commit more important table scalar columns (fast by LGWR and DBWR) to make them visible, and then mise-a-jour LOB column.
    commit;
    update test_tab_lob set lob_state = 'updated', mylob = l_src_mylob where id = l_seq;
    
	  commit;
	  
	  if k <= 10 then 
		  dbms_output.put_line('Create id = '||l_seq||', mylob Length = '||dbms_lob.getlength(l_src_mylob));
		end if;
    if k = 10 then 
		  dbms_output.put_line('Create id: ........ only print first 10 id');
		end if;
	end loop;
end;
/

-- exec test_lob_proc(1, 1000*3, 10);     -- insert 10 rows with mylob length=3MB 

-------------------- create test jobs ----------------------

create or replace procedure test_lob_proc_job(p_job_cnt number, p_kb_cnt number, p_loop_cnt number) as
  l_job_name varchar2(50);
begin
  for i in 1..p_job_cnt loop
    l_job_name := 'TEST_JOB_ENQ_'||i;
    dbms_scheduler.create_job (
      job_name        => l_job_name,
      job_type        => 'PLSQL_BLOCK',
      job_action      => 
        'begin 
           test_lob_proc('||i||', '||p_kb_cnt||', '||p_loop_cnt||');
        end;',    
      start_date      => systimestamp,
      --repeat_interval => 'systimestamp',
      auto_drop       => true,
      enabled         => true);
  end loop;
end;
/


4. LOB Space Usage 



------------------------------------ SECUREFILE LOB space usage ------------------------------------
--   MOS: How to Determine what storage is used in a LOBSEGMENT and should it be shrunk / reorganized? (Doc ID 1453350.1)

CREATE OR REPLACE PROCEDURE check_space_securefile (u_name in varchar2, v_segname varchar2) IS
  l_segment_size_blocks NUMBER;
  l_segment_size_bytes NUMBER;
  l_used_blocks NUMBER;
  l_used_bytes NUMBER;
  l_expired_blocks NUMBER;
  l_expired_bytes NUMBER;
  l_unexpired_blocks NUMBER;
  l_unexpired_bytes NUMBER;
  l_unused_blocks NUMBER;
  l_unused_bytes NUMBER;
  l_non_data_blocks NUMBER;
  l_non_data_bytes NUMBER;
  l_non_data_blocks_2 NUMBER;
  BEGIN
    DBMS_SPACE.SPACE_USAGE( segment_owner =>u_name,                        --segment_owner           IN    VARCHAR2,                 
                            segment_name => v_segname,                     --segment_name            IN    VARCHAR2,              
                            segment_type => 'LOB',                         --segment_type            IN    VARCHAR2,      
                            suoption     => DBMS_SPACE.SPACEUSAGE_EXACT,   -- or DBMS_SPACE.SPACEUSAGE_FAST
                            segment_size_blocks => l_segment_size_blocks,  --segment_size_blocks     OUT   NUMBER,                
                            segment_size_bytes => l_segment_size_bytes,    --segment_size_bytes      OUT   NUMBER,                
                            used_blocks => l_used_blocks,                  --used_blocks             OUT   NUMBER,                
                            used_bytes => l_used_bytes,                    --used_bytes              OUT   NUMBER,                
                            expired_blocks => l_expired_blocks,            --expired_blocks          OUT   NUMBER,                
                            expired_bytes => l_expired_bytes,              --expired_bytes           OUT   NUMBER,                
                            unexpired_blocks => l_unexpired_blocks,        --unexpired_blocks        OUT   NUMBER,                
                            unexpired_bytes => l_unexpired_bytes           --unexpired_bytes         OUT   NUMBER,                
                                                                           --partition_name          IN    VARCHAR2 DEFAULT NULL
                           );
    DBMS_OUTPUT.PUT_LINE('===========================================================================');
    DBMS_OUTPUT.PUT_LINE('segment_size_blocks = '||l_segment_size_blocks);
    DBMS_OUTPUT.PUT_LINE('used_blocks         = '||l_used_blocks     ||' ('||(round(100*l_used_blocks/l_segment_size_blocks, 2))||' %)');
    DBMS_OUTPUT.PUT_LINE('expired_blocks      = '||l_expired_blocks  ||' ('||(round(100*l_expired_blocks/l_segment_size_blocks, 2))||' %)');
    DBMS_OUTPUT.PUT_LINE('unexpired_blocks    = '||l_unexpired_blocks||' ('||(round(100*l_unexpired_blocks/l_segment_size_blocks, 2))||' %)');
    DBMS_OUTPUT.PUT_LINE('===========================================================================');
    
    l_unused_blocks := l_segment_size_blocks - (l_used_blocks + l_expired_blocks + l_unexpired_blocks);
    l_unused_bytes  := l_segment_size_bytes - (l_used_bytes + l_expired_bytes + l_unexpired_bytes);

    l_non_data_blocks   := l_unused_blocks + l_expired_blocks + l_unexpired_blocks;
    l_non_data_blocks_2 := l_segment_size_blocks - l_used_blocks;
    
    DBMS_OUTPUT.ENABLE;
    DBMS_OUTPUT.PUT_LINE('Segment Blocks/Bytes   = '||l_segment_size_blocks||' / '||l_segment_size_bytes||' ('||round(l_segment_size_bytes/1024/1024, 2) ||' MB)');
    DBMS_OUTPUT.PUT_LINE('Unused Blocks/Bytes    = '||l_unused_blocks      ||' / '||l_unused_bytes      ||' ('||(round(100*l_unused_blocks/l_segment_size_blocks, 2))||' %)');
    DBMS_OUTPUT.PUT_LINE('Used Blocks/Bytes      = '||l_used_blocks        ||' / '||l_used_bytes        ||' ('||(round(100*l_used_blocks/l_segment_size_blocks, 2))||' %)');
    DBMS_OUTPUT.PUT_LINE('Expired Blocks/Bytes   = '||l_expired_blocks     ||' / '||l_expired_bytes     ||' ('||(round(100*l_expired_blocks/l_segment_size_blocks, 2))||' %)');
    DBMS_OUTPUT.PUT_LINE('Unexpired Blocks/Bytes = '||l_unexpired_blocks   ||' / '||l_unexpired_bytes   ||' ('||(round(100*l_unexpired_blocks/l_segment_size_blocks, 2))||' %)');
    DBMS_OUTPUT.PUT_LINE('===========================================================================');
    DBMS_OUTPUT.PUT_LINE('NON Data Blocks  = '||l_non_data_blocks||' ('||(round(100*l_non_data_blocks/l_segment_size_blocks, 2))||' %)');
    DBMS_OUTPUT.PUT_LINE('NON_data_blocks_2 (= segment_size_blocks - used_blocks) = '||l_non_data_blocks_2);
    DBMS_OUTPUT.PUT_LINE('===========================================================================');
    
    -- Determine the storage size of the LOBSEGMENT
    for c in (SELECT round(SUM(BYTES)/8192) blocks, SUM(BYTES) bytes, count(*) cnt, min(blocks) MIN_Blocks, max(blocks) MAX_Blocks FROM DBA_EXTENTS WHERE SEGMENT_NAME = v_segname) loop
      DBMS_OUTPUT.PUT_LINE('LOBSEGMENT DBA_EXTENTS storage size Blocks  = '||c.blocks||', CNT = ' ||c.cnt  ||', MIN_Blocks = '||c.MIN_Blocks ||', MAX_Blocks = '||c.MAX_Blocks);
      DBMS_OUTPUT.PUT_LINE('LOBSEGMENT DBA_EXTENTS storage size Byets   = '||c.bytes ||' ('||round(c.bytes/1024/1024, 2) ||' MB)');
    end loop;
    
    for c in (SELECT round(SUM(BYTES)/8192) blocks, SUM(BYTES) bytes FROM DBA_SEGMENTS WHERE SEGMENT_NAME = v_segname) loop
      DBMS_OUTPUT.PUT_LINE('LOBSEGMENT DBA_SEGMENTS storage size Blocks = '||c.blocks);
      DBMS_OUTPUT.PUT_LINE('LOBSEGMENT DBA_SEGMENTS storage size Byets  = '||c.bytes ||' ('||round(c.bytes/1024/1024, 2) ||' MB)');
    end loop;
  END;
/

CREATE OR REPLACE PROCEDURE check_space_securefile_2 (v_owner_name in varchar2, v_table_name varchar2, v_column_name varchar2) IS
  l_lob_segment_name varchar2(100);
begin
  select segment_name into l_lob_segment_name from dba_lobs where owner=v_owner_name and table_name=v_table_name and column_name=v_column_name;
  check_space_securefile(u_name=>v_owner_name, v_segname=>l_lob_segment_name);
end;
/
Posted by at
Subscribe to: Posts (Atom)