Skip to Main Content
  • Questions
  • Reclaim the free space by truncate/Drop partition from partition table having LOB column

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Ishan.

Asked: March 07, 2017 - 2:19 pm UTC

Last updated: March 15, 2017 - 2:42 am UTC

Version: 12c (12.1.0.2)

Viewed 10K+ times! This question is

You Asked

Hi Team,

I have one partition table having almost 300GB data with LOB column which contains the 90-95% data of total table size. Partition table having 130 partition created and out of it currently 20 partition having the data. Partition table have 1 Global index and 1 local partition index. We are doing the Performance Test with application in which we need to remove old partition data to free the space to utilize for next runs.

1. I have tried truncate partition with drop storage to free the space.

"alter table <table_name> truncate partition P1 drop storage;"

and then I have rebuild the Global Index. I check the dba_free_space, it shows the free space but while checking the HWM in dba_extent, It is still set as high value and not showing free space in tablespace.

So I tried the shrink the partition with shrink the LOB column as follows

alter table <table_name> enable row movement;
ALTER TABLE <table_name> MODIFY PARTITION P1 SHRINK SPACE compact;
ALTER TABLE <table_name> MODIFY PARTITION P1 SHRINK SPACE;
ALTER TABLE <table_name> MODIFY LOB(<LOB_COLUMN>) (SHRINK SPACE CASCADE);
alter table <table_name> disable row movement;

and again rebuild the GLOBAL index and also rebuild local index for all partitions but space is not released.

So, I tried to shrink the entire table as follows

alter table <table_name> enable row movement;
ALTER TABLE <table_name> SHRINK SPACE compact;
ALTER TABLE <table_name> SHRINK SPACE;
ALTER TABLE <table_name> MODIFY LOB(<LOB_COLUMN>) (SHRINK SPACE CASCADE);
alter table <table_name> disable row movement;

and again rebuild the GLOBAL index and also rebuild local index for all partitions. This time the space was released.

2. I have tried alternative way with drop the partition

alter table <table_name> drop partition (P1);

and perform the same activities as mentioned above with update global and local indexes but no luck.

again I have to shrink the table and rebuild global and local index on all partition tables to release the space.


3. I have also tried with move the partition table to other tablespace and restore back to original tablespace after truncating the partition from partition table. But again no luck.


I have tested the scenario in my lab environment. Shrink is working fine as in my lab env having less data. but in 300GB size of table, It could take long time and for index rebuild also will take long time. plus shrink will lock the object as well.

Can you please help or suggest the best way to reclaim the space by droping/truncating the old partition.

and Connor said...

Sorry, I'm not sure what you mean. I did the following demo in an empty tablespace. You can see when I truncate a partition, or when I drop a partition the space is free'd up as expected.

SQL> create table t1 (
  2    c1 int,
  3    c2 char(1000),
  4    z1 clob,
  5    z2 clob
  6  )
  7  lob ( z1) store as basicfile z1lob,
  8  lob ( z2) store as basicfile z2lob
  9  partition by range ( c1 )
 10  (
 11    partition p1 values less than ( 5000 ),
 12    partition p2 values less than ( 10000 )
 13  )
 14  tablespace demo;

Table created.

SQL>
SQL> declare
  2    c clob;
  3  begin
  4    c := rpad('x',32700,'x');
  5
  6    insert into t1
  7    select
  8      rownum,
  9      rownum,
 10      'small clob',
 11      c
 12    from dual
 13    connect by level < 10000;
 14    commit;
 15  end;
 16  /

PL/SQL procedure successfully completed.

SQL>
SQL> create index t1_ix on t1 ( c1 );

Index created.

SQL>
SQL> col partition_name format a30
SQL> set lines 200
SQL> select segment_name, partition_name, bytes
  2  from user_segments
  3  where tablespace_name = 'DEMO'
  4  order by 1;

SEGMENT_NAME                   PARTITION_NAME                      BYTES
------------------------------ ------------------------------ ----------
SYS_IL0000101171C00003$$       SYS_IL_P786                       1048576
SYS_IL0000101171C00003$$       SYS_IL_P785                       1048576
SYS_IL0000101171C00004$$       SYS_IL_P790                       1048576
SYS_IL0000101171C00004$$       SYS_IL_P789                       1048576
T1                             P2                                7340032
T1                             P1                                7340032
Z1LOB                          SYS_LOB_P783                      1048576
Z1LOB                          SYS_LOB_P784                      1048576
Z2LOB                          SYS_LOB_P787                    409993216
Z2LOB                          SYS_LOB_P788                    409993216

10 rows selected.

SQL>
SQL> alter table t1 truncate partition p1;

Table truncated.

SQL>
SQL> select segment_name, partition_name, bytes
  2  from user_segments
  3  where tablespace_name = 'DEMO'
  4  order by 1;

SEGMENT_NAME                   PARTITION_NAME                      BYTES
------------------------------ ------------------------------ ----------
SYS_IL0000101171C00003$$       SYS_IL_P786                       1048576
SYS_IL0000101171C00003$$       SYS_IL_P785                       1048576
SYS_IL0000101171C00004$$       SYS_IL_P790                       1048576
SYS_IL0000101171C00004$$       SYS_IL_P789                       1048576
T1                             P2                                7340032
T1                             P1                                1048576 <== space released
Z1LOB                          SYS_LOB_P783                      1048576
Z1LOB                          SYS_LOB_P784                      1048576
Z2LOB                          SYS_LOB_P787                      2097152 <=== space released
Z2LOB                          SYS_LOB_P788                    409993216

10 rows selected.

SQL>
SQL> alter table t1 drop partition p2;

Table altered.

SQL>
SQL> select segment_name, partition_name, bytes
  2  from user_segments
  3  where tablespace_name = 'DEMO'
  4  order by 1;

SEGMENT_NAME                   PARTITION_NAME                      BYTES
------------------------------ ------------------------------ ----------
SYS_IL0000101171C00003$$       SYS_IL_P785                       1048576
SYS_IL0000101171C00004$$       SYS_IL_P789                       1048576
T1                             P1                                1048576
Z1LOB                          SYS_LOB_P783                      1048576
Z2LOB                          SYS_LOB_P787                      2097152

5 rows selected.  <== segments entirely removed


Rating

  (1 rating)

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

Comments

Ishan Joshi, March 14, 2017 - 9:55 am UTC

Thanks Connor for your response for my query.

Yes, I can see the free space in both dba/user_free_space and dba/user_segments.

But my question is with truncate/drop partition operations, High Water Mark (HWM) also needs to be reduced to free space as it is doing for normal table truncate operation.

In my case, tablespace size is still higher though the segments are free. with that we are not able to resize the datafile.

I am checking the HWM with following query

select a.tablespace_name,CEIL( blocks*(a.blocksize)/1024/1024) "Current Size in MB",
ceil( (nvl(hwm,1)*(a.BlockSize))/1024/1024 ) "Smallest Poss. in MB",
CEIL( blocks*(a.blocksize)/1024/1024) -
ceil( (nvl(hwm,1)*(a.BlockSize))/1024/1024 ) "Savings in MB",
'alter database datafile '''|| FILE_NAME || ''' resize ' ||
ceil((nvl(hwm,1)*(a.BlockSize))/1024/1024/100)*100 || 'm;' "Command",a.autoextensible
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 ) >= 0
order by "Savings in MB" desc;

options to reduce HWM, Shrink table and moving the table to other tablespace. But these options are cost more time to reclaim space in my case.

Connor McDonald
March 15, 2017 - 2:42 am UTC

Well you didnt *ask* that :-) You asked about free space, and as I've shown in the example, you *do* get the free space back for re-use by the same or other segments as the case may be.

If you want to *shift* a segment so that the hwm on a datafile is lowered, then you must physically move each segment.

But you also said: "to free the space to utilize for next runs". If you are doing to reuse the space anyway, I would not bother with trying reduce a datafile size, because it surely just grow again anyway

More to Explore

VLDB

If you are new to partitioning, check out Connor McDonald's introduction series here.

VLDB

Documentation set on VLDB and Partitioning.