Oracle shrinking / reclaiming free tablespace space

I agree with EdStevens' comment, but here are some other ideas:

The AskTom website has a script that will list exactly how much you can shrink the existing data files.

If that doesn't yield much savings, you can find the biggest indexes on that tablespace and rebuild them; that is a temporary solution and the indexes would eventually get big again, but if budget doesn't allow more disk space now, try this query:

select segment_name, sum(bytes)/1024/1024 as mb 
from dba_segments 
where tablespace_name='TB1' and segment_type like 'INDEX%'
group by segment_name
order by mb;

Then if you find an index named ind_big_stuff is huge, then

alter index ind_big_stuff rebuild online;

Also, if your application is a commercial application, there may be several tables with zero rows. Or if you have partitioned tables, you may have some partitions with zero rows. You can drop space allocated to them with

exec dbms_space_admin.drop_empty_segments(schema_name=>'HR');

You can use the segment creation deferred clause on an alter table statement for things like partitioned tables, or tables you know will not have any rows.

I think there are five methods to deal with this oracle maintenance issue:

• Online reorg - Oracle online table reorganization package, (dbms_redefinition) is used to reorganize tables while they are accepting updates. It preserves the index and constraint definitions. (Only Enterprise Editon). No downtime.
• Alter table shrink space - Using the alter table shrink space compact command will re-pack the rows, move down the HWM, and releases unused extents. The shrink only compacts the data. It doesn't guarantee the blocks will be placed at the front of the datafile, so it's possible to have blocks at the end of the datafile, which means the datafile can't be resized smaller. It preserves the index and constraint definitions. No downtime.
• Data pump (expdp, impdp) – Offline method to transfer data and reorganize tables. It preserves the index and constraint definitions. Downtime required.
• Alter table move - The alter table move command moves rows down into un-used space and adjusts the HWM but does not adjust the segments extents, and the table size remains the same. The alter table move syntax also preserves the index and constraint definitions. Datafile resize required. Downtime required.
• Create table As Select (CTAS) - This copies the table rows into a clean area, lowering the high-water-mark, packing the rows densely (as dictated by PCTFREE) and releasing free space. Datafile resize required. Downtime required.

I searched for an answer to this and finally found it:

• (optionally) remove unused (dropped) tables from tablespace: PURGE TABLESPACE table_space_name;
• clean the tablespace: ALTER TABLESPACE table_space_name COALESCE;
• finally, resize the datafiles: ALTER DATABASE DATAFILE '/u02/oracle/rbdb1/file1.dbf' RESIZE 100M;

PS: Not sure about downtime, someone please check that.

PPS: For TEMP tablespace(s) use:

• ALTER TABLESPACE TEMP SHRINK SPACE ; (optionally add KEEP 512M or similar)
• resize the data/tempfiles: ALTER DATABASE TEMPFILE '/u02/oracle/rbdb1/temp01.dbf' RESIZE 512M;