How to Resize Oracle Datafiles - Grow & Shrink

How to Resize Oracle Datafiles

You can resize Oracle datafiles online — no downtime required for either growing or shrinking. Growing is straightforward; shrinking requires knowing how much space is actually used at the high-water mark. This guide covers both directions safely.

Prerequisites

You need ALTER DATABASE or ALTER TABLESPACE system privileges. For shrinking, you need to determine the high-water mark — you cannot shrink below the highest used block.

-- Check current datafile sizes and autoextend settings
SELECT
  file_id,
  tablespace_name,
  file_name,
  ROUND(bytes/1024/1024, 0)    AS current_mb,
  autoextensible,
  ROUND(increment_by * 8192/1024/1024, 0) AS next_mb,
  ROUND(maxbytes/1024/1024/1024, 2)        AS max_gb,
  status
FROM dba_data_files
ORDER BY tablespace_name, file_id;

Step 1: Grow a Datafile

Growing is the simple, safe direction — always succeeds as long as disk has space.

-- Resize a datafile to an absolute size
ALTER DATABASE DATAFILE '/u01/oradata/ORCL/app_data01.dbf' RESIZE 2G;

-- For ASM-managed files (use file_id from dba_data_files)
ALTER DATABASE DATAFILE 5 RESIZE 5G;

-- Alternatively, using tablespace-level syntax
ALTER TABLESPACE app_data
  RESIZE DATAFILE '/u01/oradata/ORCL/app_data01.dbf' TO 2G;

Step 2: Configure Autoextend

Autoextend lets Oracle grow a datafile automatically when space runs out, within configured limits.

-- Enable autoextend on an existing datafile
ALTER DATABASE DATAFILE '/u01/oradata/ORCL/app_data01.dbf'
  AUTOEXTEND ON NEXT 256M MAXSIZE 20G;

-- Change NEXT increment and MAXSIZE on an already-autoextend file
ALTER DATABASE DATAFILE '/u01/oradata/ORCL/app_data01.dbf'
  AUTOEXTEND ON NEXT 512M MAXSIZE 50G;

-- Disable autoextend (cap file at current size)
ALTER DATABASE DATAFILE '/u01/oradata/ORCL/app_data01.dbf'
  AUTOEXTEND OFF;

-- Verify autoextend configuration
SELECT file_name,
       ROUND(bytes/1024/1024, 0) AS size_mb,
       autoextensible,
       ROUND(increment_by * 8192/1024/1024, 0) AS next_mb,
       DECODE(maxbytes, 0, 'NONE',
              ROUND(maxbytes/1024/1024/1024, 2) || ' GB') AS max_size
FROM dba_data_files
WHERE tablespace_name = 'APP_DATA';

Step 3: Determine the Safe Shrink Size

Before shrinking, find the high-water mark — the highest block Oracle has ever written to.

-- Find the minimum size a datafile can be shrunk to
-- (uses the block-level high-water mark)
SELECT
  file_id,
  file_name,
  ROUND(bytes/1024/1024, 0)                       AS current_mb,
  ROUND(nvl(hwm,1) * 8192/1024/1024, 0)           AS min_shrink_mb,
  ROUND((bytes - nvl(hwm,1)*8192)/1024/1024, 0)   AS reclaimable_mb
FROM dba_data_files d
LEFT JOIN (
  SELECT file_id, MAX(block_id + blocks) AS hwm
  FROM dba_extents
  GROUP BY file_id
) e USING (file_id)
WHERE tablespace_name = 'APP_DATA'
ORDER BY file_id;

Step 4: Shrink a Datafile

-- Shrink a datafile to a specific size
-- (must be >= min_shrink_mb from the query above)
ALTER DATABASE DATAFILE '/u01/oradata/ORCL/app_data02.dbf' RESIZE 500M;

-- If you get ORA-03297 (file contains used data beyond requested size),
-- the blocks still in use prevent shrinking that far.
-- Move segments to create space first (see Advanced section).

Step 5: Resize Tempfiles

Temporary tablespace tempfiles use a slightly different syntax.

-- Check tempfile sizes
SELECT file_name,
       ROUND(bytes/1024/1024/1024, 2) AS size_gb,
       autoextensible,
       ROUND(maxbytes/1024/1024/1024, 2) AS max_gb
FROM dba_temp_files;

-- Grow a tempfile
ALTER DATABASE TEMPFILE '/u01/oradata/ORCL/temp01.dbf' RESIZE 4G;

-- Shrink a tempfile (safe — temp segments are never permanently allocated)
ALTER DATABASE TEMPFILE '/u01/oradata/ORCL/temp01.dbf' RESIZE 1G;

-- Autoextend on a tempfile
ALTER DATABASE TEMPFILE '/u01/oradata/ORCL/temp01.dbf'
  AUTOEXTEND ON NEXT 512M MAXSIZE 10G;

Advanced Examples

Move Segments to Enable Deeper Shrink

When ORA-03297 blocks a shrink, reorganize the segment that occupies the high blocks.

-- Find segments near the end of a datafile
SELECT owner, segment_name, segment_type,
       ROUND(bytes/1024/1024, 0) AS size_mb,
       block_id,
       block_id + blocks - 1    AS last_block
FROM dba_extents
WHERE file_id = 5   -- datafile you want to shrink
ORDER BY last_block DESC
FETCH FIRST 10 ROWS ONLY;

-- Move a table to relocate its extents (compresses to start of tablespace)
ALTER TABLE app_schema.big_table MOVE TABLESPACE app_data;

-- Rebuild indexes after moving a table
ALTER INDEX app_schema.big_table_pk REBUILD;

-- Try shrink again after moving segments
ALTER DATABASE DATAFILE 5 RESIZE 500M;

Shrink All Datafiles to Their Minimum Safe Size

-- Generate resize statements for all oversized datafiles
SELECT 'ALTER DATABASE DATAFILE ''' || file_name || ''' RESIZE ' ||
       CEIL(nvl(hwm,1) * 8192 / 1048576 * 1.1) || 'M;' AS resize_sql,
       ROUND(bytes/1024/1024, 0)              AS current_mb,
       CEIL(nvl(hwm,1) * 8192 / 1048576 * 1.1) AS target_mb
FROM dba_data_files d
LEFT JOIN (
  SELECT file_id, MAX(block_id + blocks) AS hwm
  FROM dba_extents
  GROUP BY file_id
) e USING (file_id)
WHERE bytes > nvl(hwm,1) * 8192 * 1.2   -- at least 20% reclaimable
  AND tablespace_name NOT IN ('SYSTEM','SYSAUX','UNDO')
ORDER BY (bytes - nvl(hwm,1)*8192) DESC;

Resize Bigfile Tablespace Datafile

-- Bigfile tablespaces: resize the whole tablespace (same effect)
ALTER TABLESPACE dw_data RESIZE 200G;

-- Or reference the datafile directly
ALTER DATABASE DATAFILE '+DATA/ORCL/DATAFILE/dw_data.276.1158000123' RESIZE 200G;

Common Mistakes and Pitfalls

Shrinking without checking the high-water mark — ORA-03297 is the result. Always query dba_extents first to find the minimum safe size.

Shrinking SYSTEM or SYSAUX datafiles — These can technically be shrunk but it risks ORA-01654 during dictionary operations. Avoid shrinking them unless Oracle Support directs you.

Confusing RESIZE with MAXSIZE — RESIZE sets the current size. MAXSIZE in the AUTOEXTEND clause sets the ceiling. They are independent settings.

Resizing a file that is OFFLINE — Only online datafiles can be resized while the database is open. Bring the datafile online first, or resize in mount mode.

Not rebuilding indexes after moving tables — After ALTER TABLE ... MOVE, all non-partitioned indexes on that table become UNUSABLE. Always rebuild them.

Forgetting tempfiles in capacity planning — Tempfiles are excluded from dba_data_files. Query dba_temp_files separately.

Verification Queries

-- Confirm new size is in effect
SELECT file_id,
       file_name,
       ROUND(bytes/1024/1024/1024, 2) AS current_gb,
       autoextensible,
       ROUND(maxbytes/1024/1024/1024, 2) AS max_gb,
       status
FROM dba_data_files
WHERE file_name LIKE '%app_data%'
ORDER BY file_id;

-- Confirm free space changed as expected
SELECT tablespace_name,
       ROUND(SUM(bytes)/1024/1024, 0) AS free_mb,
       COUNT(*)                        AS free_extents
FROM dba_free_space
WHERE tablespace_name = 'APP_DATA'
GROUP BY tablespace_name;

-- Confirm OS file size matches Oracle's view (on filesystem, not ASM)
-- Run from OS: ls -lh /u01/oradata/ORCL/app_data01.dbf
-- The OS size should match the Oracle-reported size

Related Topics

• How to Add a Datafile - Add new datafiles instead of resizing
• How to Monitor Tablespace Usage - Track growth over time
• How to Create a Tablespace - Start from scratch
• Oracle Errors: ORA-01653 - Unable to extend table (out of space)