Skip to Main Content
  • Questions

  • shrink space behavior of lob tablespace

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Cheuk.

Asked: August 11, 2016 - 11:03 pm UTC

Last updated: January 23, 2025 - 6:07 am UTC

Version: 11.2.0.4

Viewed 50K+ times! This question is

You Asked

Hi

I'm trying to understand the shrink space behaviors when dealing with multiple tables with multiple lob columns sharing a single tablespace. We've 3 tables (e.g. T1, T2, T3) with each table having a lob column. And the lob columns are stored in a single tablespace called Lobspace (comprised of 3 datafiles). Recently we decided to delete all the data from the tables and thought that it'd good to free up some of our data storage. Since we're on Oracle11, I thought we could use online shrink space cascade to reclaim the free space and follow by datafile resize. Here's what I did:

1. delete from T1;
delete from T2;
delete from T3;

2. alter table T1 enable row movement;
alter table T2 enable row movement;
alter table T3 enable row movement;

alter table T1 shrink space cascade;
alter table T2 shrink space cascade;
alter table T3 shrink space cascade;

3. After a long wait, I checked dba_extents and dba_free_space:

SELECT de.file_id, ddf.file_name, ddf.bytes/1024/1024 "File size (MB)", block_id first_block, block_id+de.blocks-1 last_block, segment_name
FROM dba_extents de, dba_data_files ddf
WHERE de.tablespace_name = 'LOBSPACE' AND de.file_id=ddf.file_id
union all
SELECT dfs.file_id, ddf.file_name, ddf.bytes/1024/1024 "File size (MB)", block_id, block_id+dfs.blocks-1, 'free'
FROM dba_free_space dfs, dba_data_files ddf
WHERE dfs.tablespace_name = 'LOBSPACE' AND dfs.file_id=ddf.file_id
ORDER BY file_id, first_block
/

I found most of the segments were marked free. In my three datafiles, two of them were completed free while one of them still got three segments corresponding to the three lob columns.

4. Next I checked the HWM with the query provided by Tom:

https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:766625833673

To my surprise, I saw no change in the HWM from the original values before the shrink.


Now here're my questions:

1. Why HWM isn't adjusted after I ran the shrink space cascade command even though the tablespace was freed. According to Oracle doc, shrinking of lob is supported in Oracle 10gR2 and up.

2. I found that one way to adjust the HWM was to move the lob column within the same tablespace, e.g.

alter table T1 move lob(lob_column) stored as (tablespace LOBSPACE);

But it'd require a table lock during the move, which is something I want to avoid.

3. After the lob move, I succeeded to resize two of the datafiles that were empty. But the last datafile still contained some segments like SYS_LOBxxxxxx. As I said earlier, there're 3 segments corresponding to the 3 lob columns from the 3 tables. Each segment was 64k. Since all the tables were empty, what're these remaining segments? Is there a way to get rid of them? Also, they're not "packed" in the datafile. Thus the last datafile couldn't be resized properly.

thanks

and Connor said...

WHen you do shrink space on a table, the LOB's are untouched. You can explicitly run shrink space on the LOB itself.

Here's a nice blog post about it

http://www.idevelopment.info/data/Oracle/DBA_tips/LOBs/LOBS_85.shtml


Rating

  (4 ratings)

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

Comments

Update HWM

Cheuk Cheng, August 15, 2016 - 5:24 pm UTC

Hi Connor,

Thanks for the pointer. According to the article, the alter table <table_name> shrink space statement was introduced in Oracle 10g R1 and was extended to handle LOB segment in Oracle 10g R2. Since I'm on 11.4, shouldn't shrink space cascade work for the lob segment space? When I tried to shrink the lob segment space with:

ALTER TABLE <table_name> MODIFY LOB (<lob_column>) (SHRINK SPACE);

I see the lob segment space is shrunk BUT the HWM isn't updated. The HWM value remains the same. Any ideas?
Chris Saxon
August 16, 2016 - 1:52 am UTC

Are your LOBs securfiles ? From the docs:

"Shrink operations can be performed only on segments in locally managed tablespaces with automatic segment space management (ASSM). Within an ASSM tablespace, all segment types are eligible for online segment shrink except these:

IOT mapping tables
Tables with rowid based materialized views
Tables with function-based indexes
SECUREFILE LOBs
Compressed tables"

Here's a demo

SQL> create tablespace ts1 datafile 'C:\ORACLE\ORADATA\NP12\TS1.DBF' size 100m autoextend on extent management local uniform size 8m;

Tablespace created.

SQL> create tablespace ts2 datafile 'C:\ORACLE\ORADATA\NP12\TS2.DBF' size 100m autoextend on extent management local uniform size 8m;

Tablespace created.

SQL> create tablespace ts3 datafile 'C:\ORACLE\ORADATA\NP12\TS3.DBF' size 100m autoextend on extent management local uniform size 8m;

Tablespace created.

SQL> create table t ( pk int, x char(2000), y clob, z clob ) tablespace ts1
  2  lob ( y)  store as basicfile ( disable storage in row tablespace ts2 ),
  3  lob ( z) store as securefile ( disable storage in row tablespace ts3 );

Table created.

SQL> declare
  2    c clob := rpad('x',1000,'x');
  3  begin
  4    for i in 1 .. 10
  5    loop
  6       c := c || c;
  7    end loop;
  8
  9    insert into t
 10    select rownum, rownum, c, c
 11    from   dual
 12    connect by level <= 500;
 13
 14  end;
 15  /

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

SQL> @tspace TS

TABLESPACE_NAME          FILE_NAME                                              BYTES      AUTO
------------------------ ------------------------------------------------------ ---------- --------
TS1                      C:\ORACLE\ORADATA\NP12\TS1.DBF                               100m   32768m
TS2                      C:\ORACLE\ORADATA\NP12\TS2.DBF                               508m   32768m
TS3                      C:\ORACLE\ORADATA\NP12\TS3.DBF                               572m   32768m

SQL> select tablespace_name, segment_name, extents, bytes
  2  from dba_segments
  3  where tablespace_name like 'TS%'
  4  order by 1, 2;

TABLESPACE_NAME          SEGMENT_NAME                      EXTENTS      BYTES
------------------------ ------------------------------ ---------- ----------
TS1                      T                                       1    8388608
TS2                      SYS_IL0000160504C00003$$                1    8388608
TS2                      SYS_LOB0000160504C00003$$              62  520093696
TS3                      SYS_IL0000160504C00004$$                1    8388608
TS3                      SYS_LOB0000160504C00004$$              70  587202560

5 rows selected.

SQL> delete from t
  2  where mod(pk,5) != 0;

400 rows deleted.

SQL> commit;

Commit complete.

SQL> select tablespace_name, segment_name, extents, bytes
  2  from dba_segments
  3  where tablespace_name like 'TS%'
  4  order by 1, 2;

TABLESPACE_NAME          SEGMENT_NAME                      EXTENTS      BYTES
------------------------ ------------------------------ ---------- ----------
TS1                      T                                       1    8388608
TS2                      SYS_IL0000160504C00003$$                1    8388608
TS2                      SYS_LOB0000160504C00003$$              62  520093696
TS3                      SYS_IL0000160504C00004$$                1    8388608
TS3                      SYS_LOB0000160504C00004$$              70  587202560

5 rows selected.

SQL> alter table t enable row movement;

Table altered.

SQL> alter table t shrink space cascade;

Table altered.

SQL> select tablespace_name, segment_name, extents, bytes
  2  from dba_segments
  3  where tablespace_name like 'TS%'
  4  order by 1, 2;

TABLESPACE_NAME          SEGMENT_NAME                      EXTENTS      BYTES
------------------------ ------------------------------ ---------- ----------
TS1                      T                                       1    8388608
TS2                      SYS_IL0000160504C00003$$                1    8388608
TS2                      SYS_LOB0000160504C00003$$              13  109051904
TS3                      SYS_IL0000160504C00004$$                1    8388608
TS3                      SYS_LOB0000160504C00004$$              70  587202560

5 rows selected.


Alex, August 15, 2016 - 5:30 pm UTC

According to the docs it looks like the cascade option should shrink LOBs:

https://docs.oracle.com/cd/B28359_01/server.111/b28310/schema003.htm#CBBBIADA

"Shrink a table and all of its dependent segments (including BASICFILE LOB segments):

ALTER TABLE employees SHRINK SPACE CASCADE;"
Chris Saxon
August 16, 2016 - 1:13 am UTC

Good point.

Questions on HWM update

Cheuk Cheng, August 17, 2016 - 1:34 am UTC

Thanks again Connor.

My setup is different from your example. So I modified your script show the problem:

create tablespace tsmain datafile 'C:\ORACLE\ORADATA\NP12\TS.DBF' size 100m autoextend on;
create tablespace ts datafile 'C:\ORACLE\ORADATA\NP12\TS1.DBF' size 100m autoextend on;
alter tablespace ts add datafile 'C:\ORACLE\ORADATA\NP12\TS2.DBF' size 100m autoextend on;
alter tablespace ts add datafile 'C:\ORACLE\ORADATA\NP12\TS3.DBF' size 100m autoextend on;

create table t1 ( pk int, x clob) tablespace tsmain
lob ( x) store as basicfile ( disable storage in row tablespace ts );

create table t2 ( pk int, y clob) tablespace tsmain
lob ( y) store as basicfile ( disable storage in row tablespace ts );

create table t3 ( pk int, z clob) tablespace tsmain
lob ( z) store as basicfile ( disable storage in row tablespace ts );


declare
c clob := rpad('x',1000,'x');
begin
for i in 1 .. 10
loop
c := c || c;
end loop;

insert into t1
select rownum, c
from dual
connect by level <= 500;

insert into t2
select rownum, c
from dual
connect by level <= 500;

insert into t3
select rownum, c
from dual
connect by level <= 500;

end;
/

commit;

select tablespace_name, segment_name, extents, bytes
from dba_segments
where tablespace_name like 'TS%'
order by 1, 2;

TABLESPACE_NAME SEGMENT_NAME EXTENTS BYTES
------------------------ ------------------------------ ----------- ----------
TS SYS_IL0000016756C00002$$ 15 983040
TS SYS_IL0000016759C00002$$ 15 983040
TS SYS_IL0000016762C00002$$ 15 983040
TS SYS_LOB0000016756C00002$$ 195 1039138816
TS SYS_LOB0000016759C00002$$ 195 1040187392
TS SYS_LOB0000016762C00002$$ 195 1039138816
TSMAIN T1 1 65536
TSMAIN T2 1 65536
TSMAIN T3 1 65536


SELECT ceil( blocks*(a.BlockSize)/1024/1024) "Current Size",
hwm,
ceil( (nvl(hwm,1)*(a.BlockSize))/1024/1024 ) "Smallest Poss.",
ceil( blocks*(a.BlockSize)/1024/1024) - ceil( (nvl(hwm,1)*(a.BlockSize))/1024/1024 ) "Savings",
'alter database datafile '''|| file_name || ''' resize ' || ceil((nvl(hwm,1)*(a.BlockSize))/1024/1024) || 'm;' "Command"
FROM (SELECT a.*, p.value BlockSize
FROM dba_data_files a
JOIN v$parameter p ON p.Name='db_block_size') a
LEFT JOIN (SELECT file_id, max(block_id+blocks-1) hwm
FROM dba_extents GROUP BY file_id ) b ON a.file_id = b.file_id
WHERE ceil( blocks*(a.BlockSize)/1024/1024) - ceil( (nvl(hwm,1)*(a.BlockSize))/1024/1024 ) > 1
ORDER BY "Command" Desc;

Current size HWM Smallest Poss. Savings Command
--------------- ----------- ---------------- ----------- --------------
1045 128383 1003 42 alter database datafile 'C:\ORACLE\ORADATA\NP12\TS3.DBF' resize 110m;
1026 125951 984 42 alter database datafile 'C:\ORACLE\ORADATA\NP12\TS2.DBF' resize 70m;
1035 127103 993 42 alter database datafile 'C:\ORACLE\ORADATA\NP12\TS1.DBF' resize 100m;
64 151 2 62 alter database datafile 'C:\ORACLE\ORADATA\NP12\TS.DBF' resize 10m;

delete from t1;
delete from t2;
delete from t3;
commit;

alter table t1 enable row movement;
alter table t1 shrink space cascade;
alter table t2 enable row movement;
alter table t2 shrink space cascade;
alter table t3 enable row movement;
alter table t3 shrink space cascade;

select tablespace_name, segment_name, extents, bytes
from dba_segments
where tablespace_name like 'TS%'
order by 1, 2;

TABLESPACE_NAME SEGMENT_NAME EXTENTS BYTES
------------------------ ------------------------------ ----------- ----------
TS SYS_IL0000016756C00002$$ 18 3145728
TS SYS_IL0000016759C00002$$ 18 3145728
TS SYS_IL0000016762C00002$$ 18 3145728
TS SYS_LOB0000016756C00002$$ 1 65536
TS SYS_LOB0000016759C00002$$ 1 65536
TS SYS_LOB0000016762C00002$$ 1 65536
TSMAIN T1 1 65536
TSMAIN T2 1 65536
TSMAIN T3 1 65536


SELECT ceil( blocks*(a.BlockSize)/1024/1024) "Current Size",
hwm,
ceil( (nvl(hwm,1)*(a.BlockSize))/1024/1024 ) "Smallest Poss.",
ceil( blocks*(a.BlockSize)/1024/1024) - ceil( (nvl(hwm,1)*(a.BlockSize))/1024/1024 ) "Savings",
'alter database datafile '''|| file_name || ''' resize ' || ceil((nvl(hwm,1)*(a.BlockSize))/1024/1024) || 'm;' "Command"
FROM (SELECT a.*, p.value BlockSize
FROM dba_data_files a
JOIN v$parameter p ON p.Name='db_block_size') a
LEFT JOIN (SELECT file_id, max(block_id+blocks-1) hwm
FROM dba_extents GROUP BY file_id ) b ON a.file_id = b.file_id
WHERE ceil( blocks*(a.BlockSize)/1024/1024) - ceil( (nvl(hwm,1)*(a.BlockSize))/1024/1024 ) > 1
ORDER BY "Command" Desc;

Current size HWM Smallest Poss. Savings Command
--------------- ----------- ---------------- ----------- --------------
1045 128767 1006 39 alter database datafile 'C:\ORACLE\ORADATA\NP12\TS3.DBF' resize 110m;
1026 88191 689 337 alter database datafile 'C:\ORACLE\ORADATA\NP12\TS2.DBF' resize 70m;
1035 127487 996 39 alter database datafile 'C:\ORACLE\ORADATA\NP12\TS1.DBF' resize 100m;
64 151 2 62 alter database datafile 'C:\ORACLE\ORADATA\NP12\TS.DBF' resize 10m;

As you can see the free space increases after the shrink command but HWM doesn't come down much.

If I run the query:

select de.file_id, ddf.file_name, ddf.TABLESPACE_NAME, block_id first_block, block_id+de.blocks-1 last_block, de.segment_name, de.segment_type, dl.table_name, dl.column_name, ds.bytes
from dba_extents de, dba_data_files ddf, dba_lobs dl, DBA_SEGMENTS ds
where de.tablespace_name = 'TS' AND de.file_id=ddf.file_id AND de.segment_name = dl.segment_name AND ds.segment_name = dl.segment_name
union all
select dfs.file_id, file_name, ddf.TABLESPACE_NAME, block_id, block_id+dfs.blocks-1, 'free', '-', '-', '-', dfs.BYTES
from dba_free_space dfs, dba_data_files ddf
where dfs.tablespace_name = 'TS'and dfs.file_id=ddf.file_id
order by file_id, first_block
/

FILE_ID FILE_NAME TABLESPACE_NAME FIRST_BLOCK LAST_BLOCK SEGMENT_NAME TABLE_NAME COLUMN_NAME BYTES
------- --------- --------------- ----------- ----------- ------------ ---------- ----------- ------
12 C:\ORACLE\ORADATA\NP12\TS1.DBF TS 128 167 free - - - 327680
12 C:\ORACLE\ORADATA\NP12\TS1.DBF TS 256 84519 free - - - 690290688
12 C:\ORACLE\ORADATA\NP12\TS1.DBF TS 84568 127231 free - - - 349503488
12 C:\ORACLE\ORADATA\NP12\TS1.DBF TS 127488 133679 free - - - 50724864
13 C:\ORACLE\ORADATA\NP12\TS2.DBF TS 128 35583 free - - - 290455552
13 C:\ORACLE\ORADATA\NP12\TS2.DBF TS 35608 85543 free - - - 409075712
13 C:\ORACLE\ORADATA\NP12\TS2.DBF TS 85584 128255 free - - - 349569024
13 C:\ORACLE\ORADATA\NP12\TS2.DBF TS 128640 134575 free - - - 48627712
14 C:\ORACLE\ORADATA\NP12\TS3.DBF TS 128 135 SYS_LOB0000016756C00002$$ LOBSEGMENT T1 X 65536
14 C:\ORACLE\ORADATA\NP12\TS3.DBF TS 144 151 SYS_LOB0000016759C00002$$ LOBSEGMENT T2 Y 65536
14 C:\ORACLE\ORADATA\NP12\TS3.DBF TS 160 167 SYS_LOB0000016762C00002$$ LOBSEGMENT T3 Z 65536
14 C:\ORACLE\ORADATA\NP12\TS3.DBF TS 176 42895 free - - - 349962240
14 C:\ORACLE\ORADATA\NP12\TS3.DBF TS 43008 84647 free - - - 341114880
14 C:\ORACLE\ORADATA\NP12\TS3.DBF TS 84696 126335 free - - - 341114880

Both TS1 and TS2 are totally free while TS3 contains little bit of data. But yet the HWM still doesn't come down.
Connor McDonald
August 17, 2016 - 1:08 pm UTC

I would expect this to be the case.

For the *table*, there are benefits in bring the hwm down, because a full table scan must traverse everything up to the hwm.

For a lob segment, the lobs are accessed via the lob index. So there are benefits in freeing up the space (which as you can see, has in fact occurred), but the concept of lowering the "hwm" for a lob doesnt really gain your anything. It's not like you will ever "full scan" a lob segment.

The caveat on this, is that if you are trying to shift the hwm so that you can *resize* the datafile, then you would need to do 'alter table move'. Alternately, if you want to do this without an outage, look at dbms_redefinition.

Test results from the demo above...

Rajeshwaran Jeyabal, January 20, 2025 - 12:36 pm UTC 

SQL> select tablespace_name, segment_name, extents, bytes
  2  from dba_segments
  3  where tablespace_name like 'TS%'
  4  order by 1, 2;

TABLESPACE_NAME          SEGMENT_NAME                      EXTENTS      BYTES
------------------------ ------------------------------ ---------- ----------
TS1                      T                                       1    8388608
TS2                      SYS_IL0000160504C00003$$                1    8388608
TS2                      SYS_LOB0000160504C00003$$              62  520093696
TS3                      SYS_IL0000160504C00004$$                1    8388608
TS3                      SYS_LOB0000160504C00004$$              70  587202560

5 rows selected.

SQL> alter table t enable row movement;

Table altered.

SQL> alter table t shrink space cascade;

Table altered.

SQL> select tablespace_name, segment_name, extents, bytes
  2  from dba_segments
  3  where tablespace_name like 'TS%'
  4  order by 1, 2;

TABLESPACE_NAME          SEGMENT_NAME                      EXTENTS      BYTES
------------------------ ------------------------------ ---------- ----------
TS1                      T                                       1    8388608
TS2                      SYS_IL0000160504C00003$$                1    8388608
TS2                      SYS_LOB0000160504C00003$$              13  109051904
TS3                      SYS_IL0000160504C00004$$                1    8388608
TS3                      SYS_LOB0000160504C00004$$              70  587202560

5 rows selected.


Team - I was looking at the test case results shared above
any reason why TS3 space utilization was not reduced after "shrink space cascade" command ? where as in TS2 it got reduced
Connor McDonald
January 23, 2025 - 6:07 am UTC

Sorry - I can't reproduice

SQL> create tablespace ts1 datafile 'X:\ORADATA\DB21\PDB21A\TS1.DBF' size 100m autoextend on extent management local uniform size 1m;

Tablespace created.

SQL> create tablespace ts2 datafile 'X:\ORADATA\DB21\PDB21A\TS2.DBF' size 100m autoextend on extent management local uniform size 1m;

Tablespace created.

SQL> create tablespace ts3 datafile 'X:\ORADATA\DB21\PDB21A\TS3.DBF' size 100m autoextend on extent management local uniform size 1m;

Tablespace created.

SQL>
SQL> create table t ( pk int, x char(2000), y clob, z clob ) tablespace ts1
  2      lob ( y)  store as basicfile ( disable storage in row tablespace ts2 ),
  3      lob ( z) store as securefile ( disable storage in row tablespace ts3 );

Table created.

SQL>
SQL> declare
  2        c clob := rpad('x',1000,'x');
  3      begin
  4        for i in 1 .. 10
  5        loop
  6           c := c || c;
  7        end loop;
  8
  9        insert into t
 10       select rownum, rownum, c, c
 11       from   dual
 12       connect by level <= 500;
 13
 14     end;
 15     /

PL/SQL procedure successfully completed.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> select column_name, securefile
  2  from user_lobs
  3  where table_name = 'T';

COLUMN_NAME                    SEC
------------------------------ ---
Y                              NO
Z                              YES

SQL>
SQL> select tablespace_name, segment_name, extents, bytes
  2  from dba_segments
  3  where tablespace_name like 'TS%'
  4  order by 1, 2;

TABLESPACE_NAME          SEGMENT_NAME                      EXTENTS      BYTES
------------------------ ------------------------------ ---------- ----------
TS1                      T                                       2    2097152
TS2                      SYS_IL0000312240C00003$$                1    1048576
TS2                      SYS_LOB0000312240C00003$$             993 1041235968
TS3                      SYS_IL0000312240C00004$$                1    1048576
TS3                      SYS_LOB0000312240C00004$$            1108 1161822208

6 rows selected.

SQL>
SQL> delete from t
  2  where mod(pk,5) != 0;

400 rows deleted.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> alter table t enable row movement;

Table altered.

SQL> alter table t shrink space cascade;

Table altered.

SQL>
SQL> select tablespace_name, segment_name, extents, bytes
  2  from dba_segments
  3  where tablespace_name like 'TS%'
  4  order by 1, 2;

TABLESPACE_NAME          SEGMENT_NAME                      EXTENTS      BYTES
------------------------ ------------------------------ ---------- ----------
TS1                      T                                       1    1048576
TS2                      SYS_IL0000312240C00003$$                3    3145728
TS2                      SYS_LOB0000312240C00003$$             247  258998272
TS3                      SYS_IL0000312240C00004$$                1    1048576
TS3                      SYS_LOB0000312240C00004$$             316  331350016

6 rows selected.

SQL>
SQL>


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.