Oracle Shrink Tablespace

1. Login as sysdba

2. Query:

SELECT sum(ext.bytes)/(1024*1024) used, fil.name FROM dba_extents ext, v$datafile filWHERE ext.file_id=fil.file#GROUP BY fil.NAMEorder 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;