Oracle ORA-01540 Error: Causes and Solutions Complete Guide

#oracle #database #dba #sql

ORA-01540: tablespace is not offline — Causes, Fixes & Prevention

What Is ORA-01540?

ORA-01540 occurs when you attempt an operation that requires a tablespace to be in an OFFLINE state, but the tablespace is currently ONLINE. This is most commonly triggered during tablespace recovery, datafile rename, or certain maintenance operations that mandate an offline tablespace as a prerequisite. Understanding the exact sequence of steps required for these operations is the key to avoiding this error.

Top 3 Causes

1. Running RECOVER TABLESPACE on an Online Tablespace

The most frequent cause. The RECOVER TABLESPACE command strictly requires the target tablespace to be offline before execution.

-- Check current tablespace status first
SELECT tablespace_name, status
FROM dba_tablespaces
WHERE tablespace_name = 'YOUR_TABLESPACE';

-- Take the tablespace offline before recovery
ALTER TABLESPACE your_tablespace OFFLINE;

-- Now run recovery
RECOVER TABLESPACE your_tablespace;

-- Bring it back online after recovery
ALTER TABLESPACE your_tablespace ONLINE;

2. Automated Script Failing to Offline the Tablespace Before Proceeding

Scripts that lack proper error handling may skip the offline step silently, then attempt recovery or file operations on a still-online tablespace.

-- Safe PL/SQL pattern to verify offline status before proceeding
DECLARE
  v_status VARCHAR2(20);
BEGIN
  SELECT status INTO v_status
  FROM dba_tablespaces
  WHERE tablespace_name = 'YOUR_TABLESPACE';

  IF v_status != 'OFFLINE' THEN
    EXECUTE IMMEDIATE 'ALTER TABLESPACE your_tablespace OFFLINE';
    DBMS_OUTPUT.PUT_LINE('Tablespace taken offline successfully.');
  ELSE
    DBMS_OUTPUT.PUT_LINE('Tablespace already offline. Proceeding.');
  END IF;
END;
/

3. Renaming or Moving Datafiles Without Offlining the Tablespace

Datafile rename and relocation operations require the tablespace to be offline first. Skipping this step causes ORA-01540.

-- Step 1: Take tablespace offline
ALTER TABLESPACE your_tablespace OFFLINE;

-- Step 2: Copy/move the file at the OS level (outside SQL*Plus)
-- e.g., cp /old_path/file.dbf /new_path/file.dbf

-- Step 3: Update the control file with the new path
ALTER TABLESPACE your_tablespace
  RENAME DATAFILE '/old_path/file.dbf'
  TO '/new_path/file.dbf';

-- Step 4: Bring tablespace back online
ALTER TABLESPACE your_tablespace ONLINE;

-- Step 5: Verify
SELECT file_name, status
FROM dba_data_files
WHERE tablespace_name = 'YOUR_TABLESPACE';

Quick Fix Summary

If you hit ORA-01540, follow this checklist:

Confirm the tablespace is truly online using DBA_TABLESPACES.
Take the tablespace offline with ALTER TABLESPACE <name> OFFLINE;.
Re-run your original command (recovery, rename, etc.).
Bring it back online with ALTER TABLESPACE <name> ONLINE;.

-- One-stop status check for all tablespaces
SELECT tablespace_name, status
FROM dba_tablespaces
ORDER BY status, tablespace_name;

Note: SYSTEM, SYSAUX, UNDO, and TEMP tablespaces cannot be taken offline normally. For those, you must use STARTUP MOUNT and recover at the database level.

Prevention Tips

Always validate tablespace status before executing maintenance commands. Make it a standard step in every runbook and maintenance checklist.
Build precondition checks into automation scripts — verify the offline state was successfully achieved before moving to subsequent steps, and implement proper exception handling to halt execution on failure.

Related Oracle Errors

Error Code	Description
ORA-01539	Tablespace is not online (opposite scenario)
ORA-01542	Cannot allocate space in an offline tablespace
ORA-00376	File cannot be read at this time
ORA-01110	Data file reference, often stacked with ORA-01540

📖 Want a more detailed guide?
Check out the full in-depth version (Korean) on oraerror.com — includes detailed analysis, additional SQL examples, and prevention tips.