Skip to Main Content


Question and Answer

Connor McDonald

Thanks for the question, ben.

Asked: January 23, 2024 - 7:47 am UTC

Last updated: February 27, 2024 - 4:27 am UTC

Version: 12c

Viewed 1000+ times

You Asked

Hi TOM i have oracle cluster database EE with 2 nodes, and i have a big table with a big LOBs row, so after compressing the LOBs files i did move them to a new tablespace,so the principal table became just about 300MB but it still taking space of 1.2Tb, my concerns are about the space why i cant reclaim this space, i created a new tablespace and i did table move but it doesn't work it took so much time and i did shrink but it doesn't work too, i think there is a problem with high watermark? what i have to do please to gain this space and thanks.

The lobs were saved in the same tablespace with other data caled DATA having 1.2 Tb, after that i did move them to a new tablespace i created caled LOB_DATA the problem is the shrink space for table space DATA did nothing and the table move also doesn't work so how to reclaim the free extents in DATA 1.2Tb tablespace.

and Connor said...

The most probable cause here is a segment near the tablespace HWM.

Save this as a script and run it from SQL Plus

set verify off
undefine tablespace
select '&&tablespace' from dual;
column file_name format a64 word_wrapped
column smallest format 999,990 heading "Smallest|Size|Poss."
column currsize format 999,990 heading "Current|Size"
column savings  format 999,990 heading "Poss.|Savings"
break on report
compute sum of savings on report
compute sum of currsize on report

column value new_val blksize
select value from v$parameter where name = 'db_block_size'

drop table ext purge;
create table ext pctfree 0 as select file_id,block_id,blocks  from dba_extents;

select file_name,
       ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) smallest,
       ceil( blocks*&&blksize/1024/1024) currsize,
       ceil( blocks*&&blksize/1024/1024) -
       ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) savings
from dba_data_files a,
     ( select file_id, max(block_id+blocks-1) hwm
         from ext -- dba_extents
        group by file_id ) b
where a.file_id = b.file_id(+)
and a.tablespace_name = nvl(upper('&&tablespace'),a.tablespace_name)
order by 1

column cmd format a75 word_wrapped

select 'alter database datafile ''' || file_name || ''' resize ' ||
       ceil( (nvl(hwm,8)*&&blksize)/1024/1024 )  || 'm;' cmd
from dba_data_files a,
     ( select file_id, max(block_id+blocks-1) hwm
         from ext -- dba_extents
        group by file_id ) b
where a.file_id = b.file_id(+)
  and ceil( blocks*&&blksize/1024/1024) -
      ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) > 10
and a.tablespace_name = nvl(upper('&&tablespace'),a.tablespace_name)      

drop table ext purge;

undefine tablespace


For example, on my database I got the following

                                                                     Size  Current    Poss.
FILE_NAME                                                           Poss.     Size  Savings
---------------------------------------------------------------- -------- -------- --------
X:\ORADATA\DB21\PDB21A\USERS01.DBF                                  6,190   14,212    8,022
                                                                          -------- --------
sum                                                                         14,212    8,022

alter database datafile 'X:\ORADATA\DB21\PDB21A\USERS01.DBF' resize 6190m;

which shows me how much space I can get back, and what the command is to run it.

But in your case, it could be something like:

Start of File | ---------------- empty space -------------- empty space ------------------- [segment] -End of file

which means even though you have heaps of free space, you still have something up near the end of the file.

You could query DBA_EXTENTS ordering by BLOCK_ID descending to see what that might be, and look at moving that

More to Explore


Need more information on Multitenant? Check out the Multitenant docs for the Oracle Database