DEV Community

umzzil nng
umzzil nng

Posted on • Originally published at oraerror.com

Oracle ORA-01549 Error: Causes and Solutions Complete Guide

#oracle #database #dba #sql

ORA-01549: tablespace not empty, use INCLUDING CONTENTS option

ORA-01549 is thrown by Oracle when you attempt to execute a DROP TABLESPACE command on a tablespace that still contains segments, objects, or metadata. Oracle enforces this as a safety mechanism to prevent accidental data loss. To proceed, you must explicitly instruct Oracle to drop the contents along with the tablespace.

Top 3 Causes

1. Active Segments or Objects Still Exist in the Tablespace

The most common cause: tables, indexes, LOB segments, or partitions remain in the tablespace when the DROP command is issued.

-- Check for remaining segments before dropping
SELECT owner, segment_name, segment_type, bytes/1024/1024 AS size_mb
FROM dba_segments
WHERE tablespace_name = 'MY_TABLESPACE'
ORDER BY segment_type, owner;
Enter fullscreen mode Exit fullscreen mode

2. Temporary or Undo Segments Are Still Active

If the tablespace is a temporary or undo tablespace with active sort operations or uncommitted transactions, Oracle will block the drop.

-- Check active sort segments in a temp tablespace
SELECT * FROM v$sort_segment
WHERE tablespace_name = 'MY_TEMP_TBS';

-- Check active undo transactions
SELECT s.username, t.status, t.used_ublk
FROM v$transaction t
JOIN v$session s ON t.ses_addr = s.saddr;
Enter fullscreen mode Exit fullscreen mode

3. Users Have the Tablespace Set as Default or Temporary

If any database users have the target tablespace assigned as their default or temporary tablespace, Oracle may prevent the drop.

-- Check user assignments
SELECT username, default_tablespace, temporary_tablespace
FROM dba_users
WHERE default_tablespace = 'MY_TABLESPACE'
   OR temporary_tablespace = 'MY_TABLESPACE';
Enter fullscreen mode Exit fullscreen mode

Quick Fix Solutions

Option 1 – Force drop using INCLUDING CONTENTS:

-- Drop tablespace with all objects and OS datafiles
DROP TABLESPACE my_tablespace
  INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS;
Enter fullscreen mode Exit fullscreen mode

Option 2 – Manually remove objects first, then drop:

-- Drop all tables in the tablespace manually
DROP TABLE schema_name.table_name PURGE;

-- Verify tablespace is empty
SELECT COUNT(*) FROM dba_segments
WHERE tablespace_name = 'MY_TABLESPACE';

-- Then drop the tablespace cleanly
DROP TABLESPACE my_tablespace INCLUDING CONTENTS AND DATAFILES;
Enter fullscreen mode Exit fullscreen mode

Option 3 – Handle temp/undo tablespaces:

-- Switch default temp tablespace before dropping
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp_new;
DROP TABLESPACE old_temp INCLUDING CONTENTS AND DATAFILES;

-- Switch undo tablespace before dropping
ALTER SYSTEM SET undo_tablespace = 'UNDOTBS2';
DROP TABLESPACE undotbs1 INCLUDING CONTENTS AND DATAFILES;
Enter fullscreen mode Exit fullscreen mode

Prevention Tips

  1. Always run a pre-drop audit script before issuing any DROP TABLESPACE command in production. Verify segment counts, datafile associations, and user assignments. 
-- Pre-drop checklist
SELECT 'SEGMENTS'     AS item, COUNT(*) FROM dba_segments  WHERE tablespace_name = 'TARGET_TS' UNION ALL
SELECT 'DATAFILES',            COUNT(*) FROM dba_data_files WHERE tablespace_name = 'TARGET_TS' UNION ALL
SELECT 'DEFAULT_USERS',        COUNT(*) FROM dba_users      WHERE default_tablespace    = 'TARGET_TS' UNION ALL
SELECT 'TEMP_USERS',           COUNT(*) FROM dba_users      WHERE temporary_tablespace  = 'TARGET_TS';
Enter fullscreen mode Exit fullscreen mode
  1. Enforce a Change Management process — all DDL operations involving tablespace drops should require peer DBA review, a full RMAN backup, and documented rollback steps before execution in any production environment.

Related Oracle Errors

  • ORA-01548 – Active rollback segment found; cannot drop tablespace.
  • ORA-00959 – Tablespace does not exist.
  • ORA-01647 – Tablespace is read-only; cannot allocate space.

📖 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.

Top comments (0)