Reclaiming free space at tablespace / datafile level after re-organization of segment
Hi,
We have a 10.2.0.4 production database (Standard Edition) and in it has a table called PROCESS (with LOB column MESG)
that grew to appx 46 GB and after that a lot of data was purged from that table.
The table data plus the LOB segment is stored in table space APPS_DATA
select tablespace_name,sum(bytes)/1024/1024/1024,sum(maxbytes)/1024/1024/1024
from dba_data_files where tablespace_name='APPS_DATA' group by tablespace_name;
Tablespace_name sum(bytes)/1024/1024/1024 sum(maxbytes)/1024/1024/1024
APPS_DATA 65.17 79.95
The following queries confirmed that table PROCESS has a lot of free space (appx 40 GB) in APPS_DATA tablespace
SELECT table_name, column_name, segment_name, a.bytes/1024/1024 MB