Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Sam.

Asked: March 24, 2017 - 1:06 am UTC

Last updated: November 18, 2019 - 12:11 pm UTC

Version: 11gR2 Enterprise

Viewed 50K+ times! This question is

You Asked

Hi,

We have a table with a BLOB column, general observation - about 50% to 60% of total DB space is occupied by this table.
In order to reduce overall DB space, we run purge program which will selectively delete older entries. However we observed that space is not free and new inserts consuming more space.

In my understanding, Once LOB space is allocated it does not reclaim unless table is dropped or truncated. After deleting rows with LOB, subsequent insert will reuse the space free by delete. However we do not see that is happening.

We have millions of entries in this table and purge will delete not more than 50% in a single run. We are daily adding new entries. We have INSERT, SELECT and DELETE operation happening in this table.

To simulate this, we have tried a test program and found that Oracle is not reusing the space. Explicit SHRINK SPACE can reclaim space.

Questions -
1. In which case Oracle will reuse space released by deleting rows having LOB?
2. Is there any recommendation to how to reclaim/reuse LOB space?
3. Is there any recommendation to perform periodic SHRINK SPACE?
4. Will ​​Automatic Segment Advisor maintenance task will reclaim this space?
5. Which oracle edition does not include ​​Automatic Segment Advisor automated task?

Thanks a lot.
-Sam

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';

TABLE_NAME COLUMN_NAME SEGMENT_NAME BYTES
------------ -------------- ---------------------------- ----------
TEST_LOB IMAGE SYS_LOB0000152544C00003$$ 25165824


-- Delete half rows 
DELETE FROM test_lob where MOD(ID,2)=0;


-- 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';

TABLE_NAME COLUMN_NAME SEGMENT_NAME BYTES
------------ -------------- ---------------------------- ----------
TEST_LOB IMAGE SYS_LOB0000152544C00003$$ 25165824

-- 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';

TABLE_NAME COLUMN_NAME SEGMENT_NAME BYTES
------------ -------------- ---------------------------- ----------
TEST_LOB IMAGE SYS_LOB0000152544C00003$$ 50331648

-- 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';

TABLE_NAME COLUMN_NAME SEGMENT_NAME BYTES
------------ -------------- ---------------------------- ----------
TEST_LOB IMAGE SYS_LOB0000152544C00003$$ 37617664

and Connor said...

Thanks for the awesome test case.

If you're planning heavy duty transactional activity on lobs, then you really want to be moving to securefiles. Basicfile are "old school" :-)

By default, securefiles will do a balancing act between space growth and space reuse. EXample


SQL> select segment_name, bytes from dba_segments where tablespace_name = 'DEMO';

no rows selected

SQL>
SQL> CREATE TABLE test_lob (id NUMBER, file_name VARCHAR2(45), image BLOB, timestamp  DATE)
  2  lob ( image ) store as securefile
  3  tablespace demo;

Table created.

SQL>
SQL> CREATE SEQUENCE test_lob_seq;

Sequence created.

SQL>
SQL> CREATE OR REPLACE DIRECTORY images_dir AS 'c:\temp';

Directory created.

SQL>
SQL> CREATE OR REPLACE PROCEDURE Load_BLOB_From_File_Image
  2  AS
  3      dest_loc    BLOB;
  4      file_name   TEST_LOB.FILE_NAME%TYPE  := '12c_itnerface.jpg';
  5      src_loc     BFILE := BFILENAME('IMAGES_DIR', file_name);
  6  BEGIN
  7      INSERT INTO test_lob (id, file_name, image, timestamp)
  8          VALUES (test_lob_seq.nextval, file_name, empty_blob(), sysdate)
  9          RETURNING image INTO dest_loc;
 10      DBMS_LOB.OPEN(src_loc, DBMS_LOB.LOB_READONLY);
 11      DBMS_LOB.OPEN(dest_loc, DBMS_LOB.LOB_READWRITE);
 12      DBMS_LOB.LOADFROMFILE(
 13            dest_lob => dest_loc
 14          , src_lob  => src_loc
 15          , amount   => DBMS_LOB.getLength(src_loc));
 16      DBMS_LOB.CLOSE(dest_loc);
 17      DBMS_LOB.CLOSE(src_loc);
 18      COMMIT;
 19  END;
 20  /

Procedure created.

SQL>
SQL> BEGIN
  2      FOR i IN 1 .. 200
  3      LOOP
  4          Load_BLOB_From_File_Image();
  5      END LOOP;
  6  END;
  7  /

PL/SQL procedure successfully completed.

SQL>
SQL> select segment_name, bytes from dba_segments where tablespace_name = 'DEMO';

SEGMENT_NAME                        BYTES
------------------------------ ----------
TEST_LOB                            65536
SYS_IL0000081936C00003$$            65536
SYS_LOB0000081936C00003$$       126025728

3 rows selected.

SQL>
SQL> DELETE FROM test_lob where MOD(ID,2)=0;

100 rows deleted.

SQL> commit;

Commit complete.

SQL>
SQL>
SQL> BEGIN
  2      FOR i IN 1 .. 200
  3      LOOP
  4          Load_BLOB_From_File_Image();
  5      END LOOP;
  6  END;
  7  /

PL/SQL procedure successfully completed.

SQL>
SQL> select segment_name, bytes from dba_segments where tablespace_name = 'DEMO';

SEGMENT_NAME                        BYTES
------------------------------ ----------
TEST_LOB                            65536
SYS_IL0000081936C00003$$            65536
SYS_LOB0000081936C00003$$       201523200

3 rows selected.

SQL>
SQL>


So I had 125M of data, so with half removed, I'd expect to grow out to around 188M and I grew to 200M instead. So I had reasonable but not complete reuse, because I'm holding on to some of the older chunks for read consistency.

But I can also get to choose how "brutal" I want to be - I can decide "Hey, I dont have any read consistecy issues here, I want full reuse", by explicitly stating the retention

SQL> select segment_name, bytes from dba_segments where tablespace_name = 'DEMO';

no rows selected

SQL>
SQL> CREATE TABLE test_lob (id NUMBER, file_name VARCHAR2(45), image BLOB, timestamp  DATE)
  2  lob ( image ) store as securefile ( retention none)  <<<=====
  3  tablespace demo;

Table created.

SQL>
SQL> CREATE SEQUENCE test_lob_seq;

Sequence created.

SQL>
SQL> CREATE OR REPLACE DIRECTORY images_dir AS 'c:\temp';

Directory created.

SQL>
SQL> CREATE OR REPLACE PROCEDURE Load_BLOB_From_File_Image
  2  AS
  3      dest_loc    BLOB;
  4      file_name   TEST_LOB.FILE_NAME%TYPE  := '12c_itnerface.jpg';
  5      src_loc     BFILE := BFILENAME('IMAGES_DIR', file_name);
  6  BEGIN
  7      INSERT INTO test_lob (id, file_name, image, timestamp)
  8          VALUES (test_lob_seq.nextval, file_name, empty_blob(), sysdate)
  9          RETURNING image INTO dest_loc;
 10      DBMS_LOB.OPEN(src_loc, DBMS_LOB.LOB_READONLY);
 11      DBMS_LOB.OPEN(dest_loc, DBMS_LOB.LOB_READWRITE);
 12      DBMS_LOB.LOADFROMFILE(
 13            dest_lob => dest_loc
 14          , src_lob  => src_loc
 15          , amount   => DBMS_LOB.getLength(src_loc));
 16      DBMS_LOB.CLOSE(dest_loc);
 17      DBMS_LOB.CLOSE(src_loc);
 18      COMMIT;
 19  END;
 20  /

Procedure created.

SQL>
SQL> BEGIN
  2      FOR i IN 1 .. 200
  3      LOOP
  4          Load_BLOB_From_File_Image();
  5      END LOOP;
  6  END;
  7  /

PL/SQL procedure successfully completed.

SQL>
SQL> select segment_name, bytes from dba_segments where tablespace_name = 'DEMO';

SEGMENT_NAME                        BYTES
------------------------------ ----------
TEST_LOB                            65536
SYS_IL0000081940C00003$$            65536
SYS_LOB0000081940C00003$$       126025728

3 rows selected.

SQL>
SQL> DELETE FROM test_lob where MOD(ID,2)=0;

100 rows deleted.

SQL> commit;

Commit complete.

SQL>
SQL>
SQL> BEGIN
  2      FOR i IN 1 .. 200
  3      LOOP
  4          Load_BLOB_From_File_Image();
  5      END LOOP;
  6  END;
  7  /

PL/SQL procedure successfully completed.

SQL>
SQL> select segment_name, bytes from dba_segments where tablespace_name = 'DEMO';

SEGMENT_NAME                        BYTES
------------------------------ ----------
TEST_LOB                            65536
SYS_IL0000081940C00003$$            65536
SYS_LOB0000081940C00003$$       184745984


So you can choose whatever suits your needs best.

Rating

  (6 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

General recommendations

Sam, March 29, 2017 - 12:17 am UTC

Thanks for your answer. Will surely do some tests and reading on securefiles storage. I have couple of more doubts for how to move ahead with this for short and long term -

1. In my test case I am not using securefiles, that is how my current application works. Doubts on this case -
1a. Will space generated by delete never going to reuse?
1b. In user_lobs retention is 900? What does this 900 means? (seconds/minutes) Would space will be reused after this retention interval?

2. Are there any general recommendations on LOB columns where purging will remove many entries periodically? I read somewhere,
2a. Use separate table space for each LOB column for LOB DATA, LOB INDEX.
2b. Use DISABLE STORAGE IN ROW because it is going to be more than 4000 char.

Thanks,
-Sam
Connor McDonald
March 29, 2017 - 1:28 am UTC

1. In my test case I am not using securefiles, that is how my current application works. Doubts on this case

You can migrate to securefiles easily.

https://connormcdonald.wordpress.com/2015/07/03/continuous-delivery-moving-to-securefile/

1a. Will space generated by delete never going to reuse?
1b. In user_lobs retention is 900? What does this 900 means? (seconds/minutes) Would space will be reused after this retention interval?

That is seconds. It is after this point at which deleted chunks will become eligible for reuse


2. Are there any general recommendations on LOB columns where purging will remove many entries periodically? I read somewhere,

2a. Use separate table space for each LOB column for LOB DATA, LOB INDEX.

That doesnt make any difference. You *might* opt to do that for other reasons, eg, you want to put that lobs on different (cheaper) media etc etc, but tablespace location wont change purge behaviour.

2b. Use DISABLE STORAGE IN ROW because it is going to be more than 4000 char.

If its more than 4000, we'll store it outside the row *anyway*. But if you *know* that every (or almost) every lob will be larger than 4k, then 'disable storage in row' would make sense.

Finding the space that can be reclaimed

Rahul Gupta, September 05, 2018 - 7:39 am UTC

There is a table with a BLOB column (storage AS BASICFILE) created in ASSM tablespace. There have been several bulk delete operations on this table in the past.

CREATE TABLE ABC
  (
    ID NUMBER(19,0) PRIMARY KEY,
    CONTENT BLOB NOT NULL,
    DATECREATED TIMESTAMP (6)
  )
  TABLESPACE ASSM
LOB (CONTENT) STORE AS BASICFILE
PARTITION BY RANGE (DATECREATED)
(PARTITION P1 VALUES LESS THAN (TIMESTAMP' 2018-02-01 00:00:00') SEGMENT CREATION IMMEDIATE);


Is there a way to find out how much space can be reclaimed on the BLOB column after converting it from BASICFILE to SECUREFILE?

I am using this script to estimate the space that can be reclaimed. 

--Create a table to store lob partition usage
CREATE TABLE dd_stats
  (
    lob_part_name         VARCHAR2(100),
    unformatted_bytes NUMBER,
    fs1_bytes        NUMBER,
    fs2_bytes        NUMBER ,
    fs3_bytes        NUMBER,
    fs4_bytes        NUMBER,
    full_bytes       NUMBER
  );

/*Get the usage data for all LOB Partitions and store this data in the table created above.*/
DECLARE
  v_unformatted_blocks NUMBER;
  v_unformatted_bytes  NUMBER;
  v_fs1_blocks         NUMBER;
  v_fs1_bytes          NUMBER;
  v_fs2_blocks         NUMBER;
  v_fs2_bytes          NUMBER;
  v_fs3_blocks         NUMBER;
  v_fs3_bytes          NUMBER;
  v_fs4_blocks         NUMBER;
  v_fs4_bytes          NUMBER;
  v_full_blocks        NUMBER;
  v_full_bytes         NUMBER;
  CURSOR c1
  IS
    SELECT lob_name,lob_partition_name partition_name
    FROM user_lob_partitions
    WHERE table_name='ABC';--Table definition in my initial question.
BEGIN
DELETE FROM dd_stats;
  FOR i IN c1
  LOOP
    dbms_space.space_usage (USER, i.lob_name, 'LOB PARTITION', v_unformatted_blocks, v_unformatted_bytes, v_fs1_blocks, v_fs1_bytes, v_fs2_blocks, v_fs2_bytes, v_fs3_blocks, v_fs3_bytes, v_fs4_blocks, v_fs4_bytes, v_full_blocks, v_full_bytes,i.partition_name);
    INSERT
    INTO dd_stats VALUES
      (
        i.partition_name,
        v_unformatted_bytes,
        v_fs1_bytes,
        v_fs2_bytes,
        v_fs3_bytes,
        v_fs4_bytes,
        v_full_bytes
      );
    COMMIT;
  END LOOP;
END;
/


--Query the table
select * FROM dd_stats;

The table ABC is a partitioned table with BLOB column having ~100 million records.

The output of the below query is 0. Does it mean that there is no space to be freed up? 

SELECT (SUM(fs1_bytes)+SUM(fs2_bytes)+SUM(fs3_bytes)+SUM(fs4_bytes)) Bytes
FROM dd_stats;


Is there any other way i Can estimate the space that would be reclaimed?

Url typo ?

Jan Hellings, January 30, 2019 - 5:38 am UTC

The link to the "moving to securefiles" article from Connor seems to have been moved. The article can be found at:
https://connor-mcdonald.com/2015/07/03/continuous-delivery-moving-to-securefile/

Connor McDonald
February 01, 2019 - 3:18 pm UTC

Thanks

BASICFILE "old school"

ojock, April 19, 2019 - 2:03 am UTC

We're on 12.1, we tested move from BASICFILE to SECUREFILE with RETENTION=NONE, for a table with high transactional activity on a CLOB.

Performance was worse on every level with SECUREFILE, for both DML and SELECT. Space reclaim was "buggy" too, causing us to run out of tablespace.

You could say BASICFILE was "old school"...or in this case more like...they don't make them like used to in the good ol' days!!
Connor McDonald
April 25, 2019 - 11:53 pm UTC

Definitely something you want to be taking up with Support.

Free up CLOB variables

Durga, June 18, 2019 - 11:19 am UTC

Hi Connor,

This is a sample code (i am using Oracle 11g)

declare
c clob;
begin
dbms_lob.createtemporary(c,true);

for i in 1 .. 4000 loop
c := c ||'123456789012,';
end loop;

dbms_output.put_line(dbms_lob.getlength(c));
setup_in_list_clob(c);

dbms_lob.freetemporary(c);
end;

In my actual code, i have many CLOB variables.
I have following questions regarding CLOBs -

1. Do i need to use dbms_lob.freetemporary(c) to reclaim space used by variable CLOB c or just c:= null would help to reclaim space?

2. Is it necessary to initialize each CLOB variable in my code using dbms_lob.createtemporary(c,true); ?

3. What is the purpose of having dbms_lob.createtemporary(c,true); ?

Please advice.

Sincerely,
Durga

Connor McDonald
July 02, 2019 - 4:45 am UTC

We'll free them at the end of the call, but you may want to free them during as well. For example

SQL> set serverout on
SQL> declare
  2    clb clob;
  3    ch varchar2(32767);
  4    k number;
  5  begin
  6    dbms_lob.createtemporary(clb,true,dbms_lob.call);
  7      for i in 1..3000 loop
  8        ch:=lpad('o',32767,'Y');
  9        dbms_lob.writeappend(clb,length(ch),ch);
 10      end loop;
 11      k:=dbms_lob.getlength(clb);
 12
 13      dbms_output.put_line('Before Free');
 14
 15      for i in ( select u.*
 16                from v$session s, v$sort_usage u
 17                where s.saddr = u.session_addr
 18      )
 19      loop
 20        dbms_output.put_line('bytes='||(i.blocks*8192));
 21      end loop;
 22
 23      for i in ( select * from v$temporary_lobs )
 24      loop
 25        dbms_output.put_line(i.CACHE_LOBS);
 26        dbms_output.put_line(i.NOCACHE_LOBS);
 27        dbms_output.put_line(i.ABSTRACT_LOBS);
 28      end loop;
 29
 30      dbms_lob.freetemporary(clb);
 31      dbms_output.put_line('After Free');
 32
 33      for i in ( select u.*
 34                from v$session s, v$sort_usage u
 35                where s.saddr = u.session_addr
 36      )
 37      loop
 38        dbms_output.put_line('bytes='||(i.blocks*8192));
 39      end loop;
 40
 41      for i in ( select * from v$temporary_lobs )
 42      loop
 43        dbms_output.put_line(i.CACHE_LOBS);
 44        dbms_output.put_line(i.NOCACHE_LOBS);
 45        dbms_output.put_line(i.ABSTRACT_LOBS);
 46      end loop;
 47
 48  end;
 49  /
Before Free
bytes=198180864
bytes=1048576
1
0
0
After Free
0
0
0

PL/SQL procedure successfully completed.


and you can't free something unless you create it first

SQL> set serverout on
SQL> declare
  2    clb clob;
  3    ch varchar2(32767);
  4    k number;
  5  begin
  6  --  dbms_lob.createtemporary(clb,true,dbms_lob.call);
  7      for i in 1..3000 loop
  8        ch:=lpad('o',32767,'Y');
  9        dbms_lob.writeappend(clb,length(ch),ch);
 10      end loop;
 11
 12      dbms_lob.freetemporary(clb);
 13  end;
 14  /
declare
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: invalid LOB locator specified: ORA-22275
ORA-06512: at "SYS.DBMS_LOB", line 1163
ORA-06512: at line 9


kal, November 17, 2019 - 9:50 am UTC

using your example with retention none

check the size after the creation
SQL> select bytes/1024/1024 from dba_segments where segment_name ='SYS_LOB0000092529C00003$$';

.0625

SQL> BEGIN
FOR i IN 1 ..20
LOOP
Load_BLOB_From_File_Image();
END LOOP;
END;
/


SQL> select bytes/1024/1024 from dba_segments where segment_name ='SYS_LOB0000092529C00003$$';

BYTES/1024/1024
---------------
368

SQL> DELETE FROM test_lob where MOD(ID,2)=0;

10 rows deleted.


SQL> select bytes/1024/1024 from dba_segments where segment_name ='SYS_LOB0000092529C00003$$';

BYTES/1024/1024
---------------
368


Once LOB space is allocated it does not reclaim unless table is dropped or truncated.
delete does not reclaim back half of delete rows.
After deleting rows with LOB, subsequent insert will reuse the space free by delete but it does not reclaim delete space without insert.

Connor McDonald
November 18, 2019 - 12:11 pm UTC

That is no different to almost anything in the Oracle database.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

CLOB/BLOB/etc

Complete documentation on Securefiles and Large Objects here