Resize a datafile / set autoextend

Command

-- Grow/shrink to a fixed size:
ALTER DATABASE DATAFILE '/u01/oradata/ORCL/customer_data01.dbf' RESIZE 5G;

-- Enable autoextend:
ALTER DATABASE DATAFILE '/u01/oradata/ORCL/customer_data01.dbf'
  AUTOEXTEND ON NEXT 256M MAXSIZE 30G;

-- Disable autoextend:
ALTER DATABASE DATAFILE '/u01/oradata/ORCL/customer_data01.dbf' AUTOEXTEND OFF;

What it does

Sets a datafile to a fixed size or configures its automatic growth behavior.

When to use it

To reclaim space (shrink), preallocate space, or stop uncontrolled growth.

Caution

Shrinking with RESIZE cannot go below the used blocks at the end of the file (ORA-03297). Move the segments first.