Shrink datafile after 'ORA-03297'

Problem

When try to resize datafile following error occur

SQL> ALTER DATABASE DATAFILE '/u01/PROD/data/system01.dbf' RESIZE 10G;
ERROR at line 1:
ORA-03297: file contains used data beyond requested RESIZE value

Solution

1. Check the tablespace sizes

SQL> set pages 999
set lines 400
SELECT df.tablespace_name tablespace_name,
 max(df.autoextensible) auto_ext,
 round(df.maxbytes / (1024 * 1024), 2) max_ts_size,
 round((df.bytes - sum(fs.bytes)) / (df.maxbytes) * 100, 2) max_ts_pct_used,
 round(df.bytes / (1024 * 1024), 2) curr_ts_size,
 round((df.bytes - sum(fs.bytes)) / (1024 * 1024), 2) used_ts_size,
 round((df.bytes-sum(fs.bytes)) * 100 / df.bytes, 2) ts_pct_used,
 round(sum(fs.bytes) / (1024 * 1024), 2) free_ts_size,
 nvl(round(sum(fs.bytes) * 100 / df.bytes), 2) ts_pct_free
FROM dba_free_space fs,
 (select tablespace_name,
 sum(bytes) bytes,
 sum(decode(maxbytes, 0, bytes, maxbytes)) maxbytes,
 max(autoextensible) autoextensible
 from dba_data_files
 group by tablespace_name) df
WHERE fs.tablespace_name (+) = df.tablespace_name
GROUP BY df.tablespace_name, df.bytes, df.maxbytes
UNION ALL
SELECT df.tablespace_name tablespace_name,
 max(df.autoextensible) auto_ext,
 round(df.maxbytes / (1024 * 1024), 2) max_ts_size,
 round((df.bytes - sum(fs.bytes)) / (df.maxbytes) * 100, 2) max_ts_pct_used,
 round(df.bytes / (1024 * 1024), 2) curr_ts_size,
 round((df.bytes - sum(fs.bytes)) / (1024 * 1024), 2) used_ts_size,
 round((df.bytes-sum(fs.bytes)) * 100 / df.bytes, 2) ts_pct_used,
 round(sum(fs.bytes) / (1024 * 1024), 2) free_ts_size,
 nvl(round(sum(fs.bytes) * 100 / df.bytes), 2) ts_pct_free
FROM (select tablespace_name, bytes_used bytes
 from V$temp_space_header
 group by tablespace_name, bytes_free, bytes_used) fs,
 (select tablespace_name,
 sum(bytes) bytes,
 sum(decode(maxbytes, 0, bytes, maxbytes)) maxbytes,
 max(autoextensible) autoextensible
 from dba_temp_files
 group by tablespace_name) df
WHERE fs.tablespace_name (+) = df.tablespace_name
GROUP BY df.tablespace_name, df.bytes, df.maxbytes
ORDER BY 4 DESC;

2. Find High Watermark of tablespace

SQL>select tablespace_name,
file_id,
file_name DATA_FILE_NAME,Allocated_MBYTES,
High_Water_Mark_MBYTES,
FREE_MBYTES,
trunc((FREE_MBYTES/Allocated_MBYTES)*100,2) "% Free",
trunc(Allocated_MBYTES-High_Water_Mark_MBYTES,2) Resizable
from
(
select ddf.tablespace_name tablespace_name,
ddf.file_id file_id,
ddf.file_name file_name,
ddf.bytes/1024/1024 Allocated_MBYTES,
trunc((ex.hwm*(dt.block_size))/1024/1024,2) High_Water_Mark_MBYTES,
FREE_MBYTES
from dba_data_files ddf
join dba_tablespaces dt on ddf.tablespace_name = dt.tablespace_name
left join (
    select file_id, sum(bytes/1024/1024) FREE_MBYTES
    from dba_free_space
    group by file_id
    ) free on ddf.file_id = free.file_id
join (
    select file_id, max(block_id+blocks) hwm
    from dba_extents
    group by file_id
    ) ex on ddf.file_id = ex.file_id
where 1=1
and ddf.tablespace_name='SYSTEM'
order by ddf.tablespace_name, ddf.file_id);

3. List the segments that have extents near the high watermark for given datafile

SQL>select file_name data_file_name,
segment_type,
owner||'.'||segment_name segment_name,
partition_name,
block_id,
blockId_Mbytes,
decode (segment_type,'TABLE','ALTER TABLE '||owner||'.'||segment_name||' MOVE TABLESPACE AA_DATA;','INDEX','ALTER INDEX '||owner||'.'||segment_name||' REBUILD ONLINE;')sc
from
(
select
de.owner owner,
de.segment_name segment_name,
de.segment_type segment_type,
de.block_id block_id,
DE.PARTITION_NAME partition_name,
ddf.file_name file_name,
trunc((de.block_id*(dt.block_size))/1024/1024,2) blockId_Mbytes
from dba_extents de
join dba_data_files ddf on  de.file_id=ddf.file_id
join dba_tablespaces dt on ddf.tablespace_name = dt.tablespace_name
where 1=1
and ddf.file_id = 15
order by de.block_id desc
)
where rownum <= 100;

4. Rebuild Segments

    i. Tables

SQL>ALTER TABLE AD_MGR.T_THREAD_DUMP MOVE TABLESPACE AA_DATA;

    ii. Lobsegments

SQL>select * from dba_lobs where owner='AD_MGR' and segment_name='SYS_LOB0000386007C00005$$';

ALTER TABLE your_schema.your_table MOVE LOB(column_name) STORE AS (TABLESPACE new_tablespace); 
ALTER TABLE AD_MGR.T_THREAD_DUMP MOVE LOB(THREAD_DUMP) STORE AS (TABLESPACE AA_DATA);

    iii. Index

SQL>ALTER INDEX AA_MGR.IDX$$_199120004 REBUILD ONLINE;