Lob sample Codes


Secure file Lob space 


set linesize 32000
set pagesize 1000
set long 2000000000
set longchunksize 1000
set head off;
set verify off;
set termout off;
 
column u new_value us noprint;
column n new_value ns noprint;
 
select name n from v$database;
select user u from dual;
set sqlprompt &ns:&us>

set head on
set echo on
set termout on
set trimspool on

set timing on
set autotrace on
set serveroutput on size 1000000

spool &ns.lobspace.log

declare 
  -- parameters to update:
  
  tabowner varchar2(30) := 'MYOWNER';
  tabname varchar2(30) := 'MYTABLE';
  lobcolumn varchar2(30) := 'MYLOBCOLUMN';
  inlinecutoff number := 3964;
  usableperblock number := 8132;

  -- rest of variables

  query varchar2(1000);
  
  TYPE LenCurTyp  IS REF CURSOR;
  len_cursor LenCurTyp;
  lob_size number;
  lobsegment varchar2(30);
  lobsegsize number;

  unformatted_blocks NUMBER;
  unformatted_bytes  NUMBER;
  fs1_blocks         NUMBER;
  fs1_bytes          NUMBER;
  fs2_blocks         NUMBER;
  fs2_bytes          NUMBER;
  fs3_blocks         NUMBER;
  fs3_bytes          NUMBER;
  fs4_blocks         NUMBER;
  fs4_bytes          NUMBER;
  full_blocks        NUMBER;
  full_bytes         NUMBER;
  
  row_count number := 0;
  inline_count number := 0;
  lobseg_count number := 0;
  roundedupsize number;
  totalinlobsegsize number := 0;

begin

  -- get lob segment name
  
  select segment_name into lobsegment
  from dba_lobs 
  where 
  owner = tabowner and
  table_name=tabname;
    
  -- get size of lob segment
  
  select bytes into lobsegsize 
  from dba_segments 
  where
  owner = tabowner and
  segment_name=lobsegment;
  
  -- get number of full blocks in lob segment
  
  DBMS_SPACE.SPACE_USAGE(tabowner, lobsegment, 'LOB',
  unformatted_blocks      ,
  unformatted_bytes       ,
  fs1_blocks              ,
  fs1_bytes               ,
  fs2_blocks              ,
  fs2_bytes               ,
  fs3_blocks              ,
  fs3_bytes               ,
  fs4_blocks              ,
  fs4_bytes               ,
  full_blocks             ,
  full_bytes        
  ); 
  
  -- loop through rows getting lob sizes
  
  query := 'select dbms_lob.getlength('||lobcolumn||') len from '||tabowner||'.'||tabname;

  OPEN len_cursor FOR query;

  LOOP
    FETCH len_cursor INTO lob_size;
    EXIT WHEN len_cursor%NOTFOUND;
    
    row_count := row_count + 1;
    
    if lob_size is null then
      lob_size := 0;
    end if;
    
    if lob_size <= inlinecutoff then
      inline_count := inline_count + 1;
    else
      lobseg_count := lobseg_count + 1;
      
      roundedupsize := usableperblock * ceil(lob_size/usableperblock);
    
      totalinlobsegsize := totalinlobsegsize + roundedupsize;
    end if;
        
  END LOOP;

  CLOSE len_cursor;

  -- final output

  dbms_output.put_line('--------------------------------------');
  dbms_output.put_line('Table owner = '||tabowner);
  dbms_output.put_line('Table name = '||tabname);
  dbms_output.put_line('LOB column name = '||lobcolumn);
  dbms_output.put_line('--------------------------------------');
  dbms_output.put_line('Number of rows in table = '||to_char(row_count));
  dbms_output.put_line('Number of rows with lob in table row = '||to_char(inline_count));
  dbms_output.put_line('Number of rows with lob in lob segment = '||to_char(lobseg_count));
  dbms_output.put_line('Total lob segment size = '||to_char(lobsegsize/(1024*1024))||' megabytes');
  dbms_output.put_line('Total size of full lob segment blocks = '||to_char((full_blocks*usableperblock)/(1024*1024))||' megabytes');
  dbms_output.put_line('Total lob space used in lob segment = '||to_char(totalinlobsegsize/(1024*1024))||' megabytes');
  dbms_output.put_line('--------------------------------------');
  dbms_output.put_line('Percentage of full blocks used = '||to_char(trunc((100*totalinlobsegsize)/(full_blocks*usableperblock)))||'%');

end;
/

spool off









CREATE TABLESPACE "LOBTEST"
LOGGING
DATAFILE 'E:\APPS\ORACLE\ORADATA\ORA92DB\LOBTEST.ORA' SIZE
10M REUSE EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT
AUTO;



create table t ( x blob );
declare
l_blob blob;
l_size number := 31191-7;
begin
insert into t values ( empty_blob() ) returning x into l_blob;
for i in 1 .. 15
loop
dbms_lob.writeappend( l_blob, l_size, utl_raw.cast_to_raw(rpad('*',l_size,'*')) );
l_size := 31191;
end loop;
commit;
end;
/




  CREATE TABLE "LOB_TAB" ("THEBLOB" BLOB)
 TABLESPACE "LOBTEST"
  PCTFREE 10 PCTUSED 0 INITRANS 1 MAXTRANS 255
 STORAGE ( INITIAL 64K NEXT 0K MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0)
  LOGGING LOB("THEBLOB") STORE AS
 ( TABLESPACE "LOBTEST" STORAGE ( INITIAL 64K NEXT 0K MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0)
 ENABLE STORAGE IN ROW NOCACHE CHUNK 8192 PCTVERSION 10);
 


 declare
  l_blob blob;
  l_src blob;
  l_bytes number;
 begin
 select x into l_src from t;
 for i in 1 .. 15
  loop
  execute immediate 'truncate table lob_tab';
  insert into lob_tab (theblob)
  values ( empty_blob() )
  returning theblob into l_blob;
   dbms_lob.copy( l_blob, l_src, i * 32765 );
 commit;
   l_bytes := 0;
  for x in
 ( select segment_name || ' extent id ' ||
  extent_id || ' bytes ' || bytes data, bytes
  from dba_extents
  where tablespace_name = 'LOBTEST'
  and segment_type = 'LOBSEGMENT'
  order by segment_name, segment_type, extent_id )
  loop
  dbms_output.put_line( x.data );
  l_bytes := l_bytes + x.bytes;
  end loop;
  dbms_output.put_line( 'l_blob = ' || dbms_lob.getlength(l_blob) ||
  ' bytes, allocated = ' || l_bytes ||
  ' pct used = ' ||
  round(dbms_lob.getlength(l_blob)/l_bytes * 100,2 ) );
 dbms_output.put_line( rpad('-',70,'-') );
  end loop;
 end;
  /





  begin
 for i in 1 .. 7
 loop
  insert into lob_tab(theBlob) select x from t;
 end loop;
  commit;
  end;
  /






Test Program


-- Create table and SP
CREATE TABLE test_lob (id NUMBER, file_name VARCHAR2(45), image BLOB, timestamp  DATE);
CREATE SEQUENCE test_lob_seq;
CREATE OR REPLACE DIRECTORY images_dir AS <some directory path having an image file>;
CREATE OR REPLACE PROCEDURE Load_BLOB_From_File_Image
AS
    dest_loc    BLOB;
    file_name   TEST_LOB.FILE_NAME%TYPE  := 'iDevelopment_info_logo_2.tif';
    src_loc     BFILE := BFILENAME('IMAGES_DIR', file_name);
BEGIN
    INSERT INTO test_lob (id, file_name, image, timestamp)
        VALUES (test_lob_seq.nextval, file_name, empty_blob(), sysdate)
        RETURNING image INTO dest_loc;
    DBMS_LOB.OPEN(src_loc, DBMS_LOB.LOB_READONLY);
    DBMS_LOB.OPEN(dest_loc, DBMS_LOB.LOB_READWRITE);
    DBMS_LOB.LOADFROMFILE(
          dest_lob => dest_loc
        , src_lob  => src_loc
        , amount   => DBMS_LOB.getLength(src_loc));
    DBMS_LOB.CLOSE(dest_loc);
    DBMS_LOB.CLOSE(src_loc);
    COMMIT;
END;



-- Insert 1000 rows
BEGIN
    FOR i IN 1 .. 1000
    LOOP
        Load_BLOB_From_File_Image();
    END LOOP;
END;






-- find usage size
SELECT table_name, column_name, segment_name, a.bytes FROM dba_segments a JOIN dba_lobs b USING (owner, segment_name) WHERE b.table_name = 'TEST_LOB';




-- Insert 1000 rows
BEGIN
    FOR i IN 1 .. 1000
    LOOP
        Load_BLOB_From_File_Image();
    END LOOP;
END;




- Explicit shrink
ALTER TABLE test_lob MODIFY LOB (image) (SHRINK SPACE);



-- find usage size
SELECT table_name, column_name, segment_name, a.bytes FROM dba_segments a JOIN dba_lobs b USING (owner, segment_name) WHERE b.table_name = 'TEST_LOB';






Bug 26729494: LOB Segment Consuming Excessive Space Due To Expired Blocks After Applied DBBP 12.1.0.2.170418 (Doc ID 2478732.1)



References:
https://asktom.oracle.com/ords/asktom.search?tag=reclaimreuse-lob-space

https://abdul-hafeez-kalsekar-tuning.blogspot.com/2024/02/lob-space-not-released-after-delete-doc.html
 

Comments

Post a Comment

Popular posts from this blog

How To Purge Optimizer Statistics Advisor Old Records From 12.2 Onwards (Doc ID 2660128.1)

  In this Document Goal Solution References APPLIES TO: Oracle Database - Enterprise Edition - Version 12.2.0.1 and later Oracle Cloud Infrastructure - Database Service - Version N/A and later Information in this document applies to any platform. GOAL Goal of this document is to provide the method to purge the old records of Optimizer Statistics Advisor Task namely AUTO_STATS_ADVISOR_TASK that consumes huge SYSAUX space. Huge no.of old records retained in WRI$_ADV_OBJECTS for AUTO_STATS_ADVISOR_TASK or INDIVIDUAL_STATS_ADVISOR_TASK incurs heavy SYSAUX space. AUTO_STATS_ADVISOR_TASK is meant for Automatic Statistics Advisor task while INDIVIDUAL_STATS_ADVISOR_TASK is for Manual Statistics Advisor task.  DBA_ADVISOR_PARAMETERS displays all advisor task parameters and their current values in the database. There is a parameter name called EXECUTION_DAYS_TO_EXPIRE. This parameter is set in no.of days. Executions older than the value(no.of days) set would be purged automaticall...
Read more

Oracle session snapper

-- use dbms_session.sleep instead of dbms_lock on oracle 18c or higher -- last column  is sid  -- scipt_name   stat_to_collect   secods_of_snap  how_many_snap   sessions_to_snap -- @snapper all 100 1 lgdr@* -- @snapper all 100 1 sqlid@* -- @snapper all 190 1 all -- @snapper all 190 1 584 -- @snapper all 30 3 584 -- @snapper all 5 1 869 -- @snapper all 30 1 584 -- @snapper ash 5 1 144 -- @snapper ash=sql_id+event+wait_class+blocking_session+p2+p3 5 1 144 -- @snapper ash 5 1 156 -- @snapper ash 190  1 all -- @snapper ash 5 1 all -- @snapper ash=sid+event+wait_class 5 1 all -- @snapper ash=sid+event+wait_class,ash1=plsql_object_id+plsql_subprogram_id+sql_id 5 1 all -- @snapper ash=sid+event+wait_class,ash1=plsql_object_id+plsql_subprogram_id+sql_id,ash2=program+module+action 5 1 all -- @snapper ash=event+wait_class,stats,gather=ts,tinclude=CPU,sinclude=parse 5 1 all -- @snapper ash=sid+event+wait_class,ash1=sid+sqlid+module,stats,gather...
Read more

Oracle Materialized View In-Depth and Materialized View refresh issues in 19c

   We    had  situation demanding  tuning of  MV  refresh and  options  like  TUNE_MVIEW  and Atomic_Refresh was explored . Hence  Documenting materialized view sample commands  and views in this  blog .     After migration if we are  facing slowness  in  Materialized View refresh , we can explore to re-create  Materialized View   Since  main topic for this blog   was MV issues  in 19c ,  posting that topic   first in line below  We also   have Real-Time Materialized Views  to explore .  To Note :  1) We can  create Mv  on ON PREBUILT TABLE  2) We can create Index on Mv  3) We can  gather stats on mv  just like other table .  4) Mv can be REFRESH_MODE ON COMMIT / REFRESH ON DEMAND. 5) Mv  REFRESH_METHOD can be  Complete / FAST or FORCE . A FORCE attempts a FAST...
Read more