Oracle Shrink Tablespace

1. Login as sysdba

2. Query:

SELECT sum(ext.bytes)/(1024*1024) used, fil.name FROM dba_extents ext, v$datafile fil
WHERE ext.file_id=fil.file#
GROUP BY fil.NAME
order by fil.name;
           

3. Shrink specified tablespace

ALTER DATABASE DATAFILE '/home/datafile/users02.dbf' RESIZE 2G;
ALTER TABLESPACE SH_DATA_1 SHRINK SPACE KEEP 100M;