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.
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
Smallest
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
CMD
---------------------------------------------------------------------------
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